SQL Server Performance

Very Slow Insert

Discussion in 'T-SQL Performance Tuning for Developers' started by blueFrench, Oct 4, 2004.

  1. blueFrench New Member

    One of the table of my database has 250,000 rows on it. Inserting 500 records on it took more than 1 minute. Is it good? How could I cut down the time?
  2. joechang New Member

    depending on exactly how you are inserting rows, this should take any from 0.1sec (5000 rows/sec) to 0.005sec (100k rows/sec)
    how big is the table (MB) ?, how much memory? processor type, exavt SQL syntax, indexes on table etc
  3. blueFrench New Member

    can i get the size of the table? anyway the size of the database is 123MB quite little i guess.
    memory is 512, P3 850Mhz.

    before it's even slower so I tried to put it in a temp table and then save it into the original Target table.

    i am using a cursor here, but that is only because there is no way to do it. for each row, i have to compute something based on certain conditions, that cannot be covered by the CASE statement. so i have no option but to use a cursor.

    regarding the indexes, i already ran sql profiler with it and it suggest nothing, meaning i can't do anything more with the indeces, i hope i'm wrong with this one (or else i would be running out of solution).

  4. Chappy New Member

    Does the table have a clustered index?
    How many non clustered indices?
    what is the fillfactor of these indices ?
  5. blueFrench New Member

    only have one clustered index, am sorry but am not sure about the fill factor.. how would i know it?

  6. Adriaan New Member

    Check for any insert triggers on your target table. If so, also check if that in itself does inserts or updates on other tables, which in turn may have triggers going off ... etc. etc.
  7. blueFrench New Member

    there are no trigger on the target table and i'm only inserting on one table. is the cursor really a big factor on the speed of this insert? 500rows for more than a minute.
  8. blueFrench New Member

    ok, here's what I got from the Execution Plan:

    each time I test like this:
    --begin loop here
    IF EXISTS (SELECT [Id] FROM Table1 WHERE Field1 = @var1 AND Field2 = @var2)
    SET @bitCanContinue = 1

    IF @bitCanContinue = 1
    --call stored proc here
    --continue loop if possible

    the IF EXISTS statement always show a Query cost of 0.17% and a 96% on the Custered Index in target table.

    Is this bad?

    thank you.
  9. satya Moderator

    You might consider writing the data to a text file and load it afterwards with BCP (which is incomparably fast for data loading).

    Run Sql Profiler - choose the SQLProfilerTSQL_Duration template.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. blueFrench New Member

    ok. i'll try your suggestion. be right back. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />thanks!<br />
  11. derrickleggett New Member

    It would be better to make a variable combination table. You could then take the contents out of the stored proc called in that loop and put them into the main query. Use the combo table to join to and find appropriate values to insert. Try to make this set based. You should be able to do the whole thing in a second or so.


    When life gives you a lemon, fire the DBA.
  12. blueFrench New Member

    hi there derrickleggett!

    I'm sorry but I don't fully understand what you want me to do. Is what I'm doing right now is similar or close to what you're suggesting? Here's what exactly I'm doing...

    RefTable.RefCol - referenced table and it's primary key
    TargetTable.ID - is linked to RefTable.RefColumn

    - Get rows from the TargetTable where ID = RefTable.RefCol.
    - Inserts it into a temp table.
    - Executes a stored proc that inserts into the temp table based on options (IFs... and calculations...) rows from TargetTable
    - after processing...
    - Inserts all rows in temp table not existing in TargetTable.

    BEFORE... Here's what I', doing...
    - Insert directly into TargetTable all rows validated from the executed stored proc.

    Time to insert (before) is more than five minutes for only 500 rows into a table of 250,000+ rows). After doing the new method the time is just over a minute. Still bad I believe because the users are complaining.

    Thank you.
  13. satya Moderator

  14. blueFrench New Member

    Yes and it was fast, but it is a command prompt utility, what I want is someting that I can include where the insert is.

    Can you please see my other post, it's kind of related.

    Thank you.
  15. satya Moderator

    True the BCP is command line utility and you can include the BCP statements in a query to perform the insert action.
    (I will look the other post)

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. blueFrench New Member

    how do i do that?
  17. satya Moderator

    If you need to execute from query analyser you will need to try this using xp_cmdshell,

    declare @cmd varchar(1000)
    select @cmd = 'bcp databasename.dbo.tablename in c:data.txt -S Team -Unn -Pnn -c'
    exec master..xp_cmdshell @cmd
    Refer to the sQL server books online for more information on BCP utility.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  18. blueFrench New Member

    I've been reading BOL, anyway, thanks so much. you've been a great help! Almost totally forgot about xp_cmdshell. Thanks satya!
  19. satya Moderator

    BOL is always a first hand information for such utilities. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  20. blueFrench New Member

    hi! i've edited my other post, can u look at it. Thanks!

Share This Page