Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow insert into a table Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 7:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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.
Post #1540195
Posted Tuesday, February 11, 2014 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540202
Posted Tuesday, February 11, 2014 8:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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.



  Post Attachments 
ExecutionPlan.jpg (24 views, 120.95 KB)
Post #1540216
Posted Tuesday, February 11, 2014 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540222
Posted Monday, February 24, 2014 3:10 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:08 AM
Points: 1,194, Visits: 2,228
It's hard to tell without seeing the actual insert statement or the execution plan. Please post it if possible ..

--
SQLBuddy
Post #1544729
Posted Monday, February 24, 2014 5:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
Does the table have a trigger?
Post #1544741
Posted Tuesday, February 25, 2014 12:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:23 PM
Points: 29, Visits: 139
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.
Post #1545110
Posted Tuesday, February 25, 2014 5:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:00 AM
Points: 35,547, Visits: 32,137
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse