Slow insert into a table

Question

Slow insert into a table

sqlnaive

SSCoach

Points: 17437
More actions
February 11, 2014 at 7:41 am

#285342

I'm facing an issue here. I have one procedure which inserts data in a table with the help of some joins in few tables. Now there are around 1000 records to be inserted as a result of execution of that procedure. But the problem is that it's inserting one recor at a time. I have updated the statistics of all the dependent tables. There is no index on the table where data getting inserted. What can be the issue ? It's taking almost an hour to insert 1000 records.

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

one record at a time?!?!

can you show the code for the procedure? does it have a cursor? are there triggers ont he destination table? does the trigger have cursors?

lots of possible issues, but without details, we can't offer much more than general guidelines: no cursors, no loops, etc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Nope there are no cursors used. But I can see the execution plan for the table. And it shows almost 75% of the time taken for Insertion operation.

Attached is the print screen of execution plan. I had to hide the object's name.

Lowell SSC Guru Points: 323504 More actions · Answer 3

don't get caught up staring at the percentages; they have to add up to 100, so just because something is 75% doesn't mean it's bad, it just means that's where work was performed compared to other steps.

i put more importance on things like the estimated number of rows inserted being 19213 and the estimated subtree cost being 87.8014 than anything else.

you said it inserts one at a time, but this looks set based.

can you obfuscate the insert statement and show it to us? the actual execution plan would tell us everything, if you can post that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

It's hard to tell without seeing the actual insert statement or the execution plan. Please post it if possible ..

--

SQLBuddy

EricEyster SSCrazy Points: 2974 More actions · Answer 5

EricEyster

SSCrazy

Points: 2974

February 24, 2014 at 5:07 pm

#1691739

Does the table have a trigger?

SillyDragon Ten Centuries Points: 1396 More actions · Answer 6

sqlnaive (2/11/2014)
I'm facing an issue here. I have one procedure which inserts data in a table with the help of some joins in few tables. Now there are around 1000 records to be inserted as a result of execution of that procedure. But the problem is that it's inserting one recor at a time. I have updated the statistics of all the dependent tables. There is no index on the table where data getting inserted. What can be the issue ? It's taking almost an hour to insert 1000 records.

Have you tried to use other account to execute the SQL?

Have you tried to load all related data into other database and run again, take a look what happens?

it might be caused by database configuration.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

sqlnaive (2/11/2014)
I'm facing an issue here. I have one procedure which inserts data in a table with the help of some joins in few tables. Now there are around 1000 records to be inserted as a result of execution of that procedure. But the problem is that it's inserting one recor at a time. I have updated the statistics of all the dependent tables. There is no index on the table where data getting inserted. What can be the issue ? It's taking almost an hour to insert 1000 records.

Please see the second link under "Helpful Links" in my signature line below. That's the information that we'd need to help you solve this problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)