Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2012-11-09 : 11:54:58
|
| Below is the scenario...I have a table whcih has 3 billion records and 8 columns in it(One of them is datetime).This table is being loaded from 4 different tables. Since this table has 3 billion records, we are having hard time to read data from the table. What is the best way to design this table? Since one of the field is DATETIME, i am thinking that it is not a good idea to create a partition on DATETIME field. Any ideas are appreciated.Thanks. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-09 : 15:33:50
|
| If your queries are always by date or date range then a clustered index on the datetime column may solve your performance issues. Partitioning is an option but you first should identify all the common access points (queries) used on the table. Most likely a good clustering choice is all you need.If you post the DDL including keys and indexes plus an example of the most common queries then I'm sure you'll get some good suggestions.Be One with the OptimizerTG |
 |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2012-11-11 : 19:15:22
|
quote:
Originally posted by sql_server_dba
Below is the scenario...I have a table whcih has 3 billion records and 8 columns in it(One of them is datetime).This table is being loaded from 4 different tables. Since this table has 3 billion records, we are having hard time to read data from the table. What is the best way to design this table? Since one of the field is DATETIME, i am thinking that it is not a good idea to create a partition on DATETIME field. Any ideas are appreciated.Thanks.
Partitioning doesn't help performance for the most part. Having the correct indexes will. Post the CREATE TABLE statement along with any CREATE INDEX statements for the table so someone can make a recommedation.,--Jeff Moden |
|
|
|
|
|
|
Design 3 billion records table