SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Design 3 billion records table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_server_dba
Posting Yak Master

144 Posts

Posted - 11/09/2012 :  11:54:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 11/09/2012 :  15:33:50  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/11/2012 :  19:15:22  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000