Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2003
    Posts
    4

    Unanswered: Slow Insert Performance on 10 million row table

    Hi,

    We have 2 insert statements one on a rather wide table and another on a table of 4 GUIDs and a date which begin to perform unacceptably to the users at approx 10 million rows. The time to run one of the inserts creating about 100,000 is about 30 seconds-1 minute on an empty table which rises to 15+ minutes when the tables reach over 10 million rows.
    Both tables have GUID non-clustered Primary keys and 2/4 other indexes which are required for other processing (removing the indexes reduce the time to about 10 minutes).

    We have a Technical Director who thinks that something is wrong (without saying what) because he thinks that INSERT performance should always be about the same regardless of table size, although in testing both INSERTs performance slows to unacceptable where there are 10 million rows in the table where the inserts take approximately 20 minutes. (We don't know now it performs over 10 million rows as we were already well over the spec limit of 10 minutes).

    Is this what you would expect? Or is something definitely wrong.

    TIA,

    David.

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Slow Insert Performance on 10 million row table

    Well..hard to tell,
    Check the fillfactor of your indexes. If the fillfactor is to high and you make a lot of insertions, that could take a lot of time 'cause SQL must take time to split the index pages to make room for new rows which is quite expensive. Try to change GUID to cluster index with a fillfactor of 50 and see if it makes any difference.
    On the other hand you could use DBCC INDEXDEFRAG to defragment all your indexes (see BOL for more).Hope it helps...

    Originally posted by david.longfield
    Hi,

    We have 2 insert statements one on a rather wide table and another on a table of 4 GUIDs and a date which begin to perform unacceptably to the users at approx 10 million rows. The time to run one of the inserts creating about 100,000 is about 30 seconds-1 minute on an empty table which rises to 15+ minutes when the tables reach over 10 million rows.
    Both tables have GUID non-clustered Primary keys and 2/4 other indexes which are required for other processing (removing the indexes reduce the time to about 10 minutes).

    We have a Technical Director who thinks that something is wrong (without saying what) because he thinks that INSERT performance should always be about the same regardless of table size, although in testing both INSERTs performance slows to unacceptable where there are 10 million rows in the table where the inserts take approximately 20 minutes. (We don't know now it performs over 10 million rows as we were already well over the spec limit of 10 minutes).

    Is this what you would expect? Or is something definitely wrong.

    TIA,

    David.
    Steve

  3. #3
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    dbadeplhes beat me to it! But I concur. That's the only thing I've seen impact INSERT performance as a table grows.

  4. #4
    Join Date
    Apr 2003
    Posts
    4

    Hmmm

    Unfortunately we have it set to a Fill factor of 50%, and have rebuilt the indexes regularly and still it takes a long time...

  5. #5
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I have never really done this so it's only a shot in the dark here, but would it be an acceptable option to drop all indexes before you do all the inserts and then recreate them once the inserts are finished? I would think that this would boost performance cause the indexes wouldn't have to be tampered with during the inserts and you would need to rebuild them in the end no matter what...? Just my two cents I guess...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  6. #6
    Join Date
    Apr 2003
    Posts
    4

    Tried It

    We tried it without any indexes except the PK, and while it was faster it was still no where near acceptable performance, and as this needs to run while users are using the system is not an option

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    In addition to all above tips also get the help from PROFILER by submitting trace to Index Tuning Wizard for index recommendation (it may help).
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Angry

    Index Tuning Wizard has nothing to do with the INSERT performance, so don`t waste your time trying this.

    Originally posted by Satya
    In addition to all above tips also get the help from PROFILER by submitting trace to Index Tuning Wizard for index recommendation (it may help).
    Steve

  9. #9
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    What kind of hardware are you running? Adding a few extra Ghz of processor power and som GB's of RAM might also do the trick...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  10. #10
    Join Date
    Apr 2003
    Posts
    4

    Hardware

    It is already running on a new Quad machine with 4GB RAM and RAID 10. We cannot use Ent Ed due to cost, so are pretty much running on the fastest hardware available as Standard can only use 2GB.

    I believe that we have hit the limit for SQL Server (with this dreadful design) and want to start from scratch but certain higher level people that ratified the original design are sure it should run faster and maybe it should but at the moment we will hit 10 million rows within 4 weeks of running so as you can see it is only a matter of time before it dies, although the aforementioned people think that the performance should level off at some point without getting any worse.

    How likely is it that the performance will level off? I imagine that while it may not continue to degrade at such a rate it will still get progressively worse as the table size increases.

    Dave.

  11. #11
    Join Date
    Apr 2003
    Location
    SC, USA
    Posts
    51
    I think we're all grasping at straws now, but have you tried the following?:

    - Place the transaction log, data and indexes in filegroups on separate physical volumes.
    - Commit transaction every x rows instead of after every row or only once at the end. Play around with the value for x.
    - Instead of using transactions, try a bulk-insert?

    good luck!

  12. #12
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I'm at least waving the white flag here...can't think of anything else.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  13. #13
    Join Date
    May 2003
    Posts
    7
    Several of our databases make similar updates (well, define 'Wide') several times a min to equally large tables. Except for indexing issues, we have had no performance problems (sub second on a good day). Are you sure it's the db causing the delay and not what is perfoming the statement?

    If there are several surrounding statements, run them all independently. Check the Query Analyzer, but also take the SQL Profiler a spin.

    If it's a db issue I would guess it has to do with too many indexes (which you already covered), nasty constraints or bad server configuration. Having 4gig of memory and using 4gig of memory are different things.

  14. #14
    Join Date
    Jun 2003
    Posts
    31
    May be it's too late to help David, but I still like to throw in few of my observations on performance tuning subject:

    1) Make sure you have enough hardware (CPU, RAM, Faster disks, multiple disk controllers, etc...) and the best software version (Enterprise Edition, Advance Server - otherwise, it wouldn't do you very much when having 4GB of RAM and only able to use 2GB !).

    2) as Loach mentioned, put DATA, TLOG, INDEX and even TEMPDB
    in a separate disk and different disk controller.
    3) Make sure the "boot.ini" has the '/PAE' or '/3GB' switch, and
    the SQL's configuration is set allow to use the AWE.
    4) For a huge table, try to separate it into several partitions and
    create a VIEW to join all partitions, so it's transparented to users.
    5) DO NOT set "Auto update statistics", but schedule a job to update
    each large table - one at a time at slowest period.
    6) Periodically, after update the table's statistics, run the
    DBCC SHOWCONTIG (table) to find out the average size and Scan
    density. If the Density is lower than 90%, calculate how many rows
    (based on the average row size) can fit into a data page (8k) and
    change the FILLFACTOR to avoid waste space & fradgmentation.
    7) Regularly run DBCC INDEXDEFRAG to reduce fragmentation, which
    definitely degraded performance, because SQL have to jump all over
    place to get the key. This DBCC INDEXDEFRAG alone is not good
    enough, since the file on disk is still fragmented. O/S defrag is a must.
    8) Contrary to others' opinios, Index Tuning Wizard may help improve
    the performance if it detected there is no cluster index, nor duplicate
    index(es). Basically, more indexes will incur performance hit, because
    each Insert/Updt/Del will also update each and every index on the
    table. Remove the duplicated index(es) will improve performance as
    well as reduce disk space waste.
    9) Check for any Blocking during the INSERT/UPDT/DEL on the table.
    This may be the culprit for your problem.

    10) You can either BCP the rows of data, or create a "Staging" table,
    which it can quickly be insert into, then replicate or BCP to the real
    table at a later time when trasnaction is slow.

    There are many ways to imrpove performance ( I learned from being an Oracle DBA before convert into SQL Server) but my brain cramp right now :-) I hope this will help.

    SVT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •