Inserts are slow

  • 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.

  • 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.

  • 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

  • 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

  • I will try this. Thanks

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

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