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

Inserts are slow Expand / Collapse
Author
Message
Posted Sunday, April 28, 2013 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 27, 2013 7:04 PM
Points: 7, Visits: 26
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.

Post #1447331
Posted Sunday, April 28, 2013 7:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 19, 2014 3:52 PM
Points: 364, Visits: 385
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.
Post #1447348
Posted Monday, April 29, 2013 12:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 6,862, Visits: 14,160
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
Post #1447372
Posted Monday, April 29, 2013 10:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:15 PM
Points: 1,766, Visits: 4,965
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.
Post #1447649
Posted Monday, April 29, 2013 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 27, 2013 7:04 PM
Points: 7, Visits: 26
I will try this. Thanks
Post #1447651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse