SQL Server Performance Forum – Threads Archive

Very Slow Insert

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?

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

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). thanks.

Does the table have a clustered index?
How many non clustered indices?
what is the fillfactor of these indices ?

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

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.

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.

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
BEGIN
–call stored proc here
END
–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.

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

ok. i’ll try your suggestion. be right back. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] thanks!

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. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

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.

Have you tried the BCP option? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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.

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

how do i do that?

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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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

BOL is always a first hand information for such utilities. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

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

]]>