SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserts are slow


Inserts are slow

Author
Message
rcarrier
rcarrier
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 41
I have a stored procedure that requires reading transactions one by one and then creating a few inserts per transaction. The total number of inserts is usually about 15,000. To me this is a very small number.

The inserts are getting created in a loop as I process and check each row from the source system.

One source system transaction may need to have several inserts created in the destination table and its a complicated process to determine this.

What I'm struggling with are these inserts are taking for ever and the status under sp_who quickly goes from runnable to suspended.

I'm actually gathering all the inserts into a temp table and then doing real insert at the end with a select into.

The table does have about 100 columns and yes they are all needed for research reasons.

Any help or suggestions is appreciated.
Mansfield
Mansfield
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 394
The next step is to determine why the query is being suspended. From here on you'll need to do some wait stats analysis. Maybe this article will help you approach this in a systemic manner.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16225 Visits: 19546
rcarrier (4/28/2013)
...
One source system transaction may need to have several inserts created in the destination table and its a complicated process to determine this.
...


"Complicated" is a relative word. If you're generating new rows to insert "row by row", there's a more than even chance that the folks who lurk around here can convert it into a set-based equivalent, which would of course be much faster - and faster means less chance of interfering with, and being interfered by, other processes accessing the same resources. Post up some code if you're interested in offering them an opportunity to look into this.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12273 Visits: 10648
Run a sample procedure call in a SSMS Query window with the 'Include Actual Execution Plan' option enabled. This will reveal the relative cost of each query within the stored procedure, and it will reveal the relative cost of each operation within each query.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
rcarrier
rcarrier
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 41
I will try this. Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search