Post reply

How to keep one billion records in one table ?

  • November 10, 2008 at 1:29 am

    #103621

    Hi friends,

    I want to keep more than one billion records in one table. I am facing performance issues with this table navigation. What would be the best method to keep such data or what are precautions I should take it to perform in all environments ?

  • November 10, 2008 at 1:46 am

    #896148

    sanuj (11/10/2008)

    Hi friends,

    I want to keep more than one billion records in one table. I am facing performance issues with this table navigation. What would be the best method to keep such data or what are precautions I should take it to perform in all environments ?

    is it possible to have the master and transactioin database on the basis of primary key

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • November 10, 2008 at 1:57 am

    #896151

    You can partition the table.

    See http://weblogs.sqlteam.com/peterl/archive/2008/06/12/Horizontal-partitioning-Enterprise-style.aspx

    N 56°04'39.16"
    E 12°55'05.25"

  • November 10, 2008 at 1:58 am

    #896152

    use horizontal partition of the table, for more information see SQL BOL. It is good for one of the best performance.

  • November 10, 2008 at 3:16 am

    #896169

    Why does it have to be a single table?

    Have you considered table partitioning (SQL 2005 feature)

    How wide are the rows?

    What indexes do you have and what are the common queries?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 12, 2008 at 10:42 am

    #897656

    Hi sanuj!

    I currently have several billion+ row tables. Between partitioning and effective indexing, these tables perform pretty well. One thing to keep in mind if you decide to partition: you must be using SQL 2005 or 2008 Enterprise edition. Standard edition does not support partitioning.

    If you'd like more suggestions, please post your table schema and indexes.

    For more reading on partitioning, including an example on how to create a partitioned table, check out my blog articles here: http://sqlfool.com/tag/partitioning/[/url]

    Regards,

    Michelle Ufford
    SQLFool.com - Adventures in SQL Tuning

  • November 12, 2008 at 11:52 am

    #897709

    Everyone's right. Partition the tables and align indexes. Use different physical disk channels where possible.

  • November 28, 2008 at 1:44 am

    #904618

    Hi All,

    Thank you all for the help.... I am really happy 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply