Inserts are slow

Question

Inserts are slow

rcarrier

SSC-Addicted

Points: 495
More actions
April 28, 2013 at 10:12 am

#276910

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.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mansfield SSCrazy Points: 2396 More actions · Answer 1

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[/url] will help you approach this in a systemic manner.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

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.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

rcarrier SSC-Addicted Points: 495 More actions · Answer 4

rcarrier

SSC-Addicted

Points: 495

April 29, 2013 at 10:40 am

#1610910

I will try this. Thanks