DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: MSSQL Large Table Question

  1. #1
    Join Date
    Dec 2003

    MSSQL Large Table Question

    I have a large table 6 Columns 2 Million rows and growing by 10% everyday estaimated.

    Its a change log table that monitors every change in the database.

    What is the best way to optimize this table for inserting.

    Remove all indexs except primary key?
    Particition the table Monthly to a new table?

    This is a multiple processor machine so
    Do I move this table to its own file group and move the file group to a new a different drive?

    Any suggestion would be helpful

  2. #2
    Join Date
    Jun 2004
    For a table that big, it would be a good idea to keep it on a separate drive. I dont think you can optimize it for insert b/c it doesn't need to go through the entire table to insert, it goes straight to last rec.(but I could be wrong).

    One way to optimize would be if you get a lot of similar error, create a table with all types of error you usually expect. And then in another table just put a timestamp, ErrorID. and if it is not from tblErrorTypes, then have another field called other within the table where you will have your timestamp to insert that error.

    This might take some extra time on programming and while processing but it all depends on how many errors you expect at any given time. This opimization is more like saving storage. Whichever way is cost-effective for you: storage or process time.
    new to programming but getting ther

  3. #3
    Join Date
    Nov 2004
    Huddinge, Sweden
    Inserts are greatly affected by the number of indexes affected, so removing indexes is a good thing. You may consider not even having a primary key, since that requires an index too, but this will depend on what the table is eventually queried for.

    One way to reduce the impact on the online processing is to have an intermediary table into which the logging is initially inserted, and then an offline process that moves this into the main table, but with few or no indexes it should have little actual effect.

    I don't think putting this table on a separate drive will be of much value, other than to deal with the volume.

    If you hit a brick wall, you didn't jump high enough!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center