locked
Stored Procedure Infrastructure RRS feed

  • Question

  • An issue that I have run into a few times in the past couple of years has to do with using a stored procedure to handle the update interface for a single table.  The stored procedure accepts all necessary parameters to either update or insert a single record.  The stored procedure is to be used whenever a new record is inserted into the table or whenever the table is updated.

    The problem that I have with this is that this kind of infrastructure has led to the use of cursors whenever a multi-record update needs to be performed.  I find this especially trying whenever this leads to a trigger that is forced to process a multi-record update with a cursor.  My comment to the developers is that this kind of infrastructure must not be applied for multi-record update because it is too bloody slow.

    Now this infrastructure is in production.  I am asked to speed it up and I again strongly suggest eliminating the cursors.  They don't want any of it -- gotta use the infrastructure.  I need comments and suggestions.


    Mr. Slow Rabbit
    • Moved by Kent Waldrop _ Tuesday, January 26, 2010 9:45 PM might be a better fit? (From:Transact-SQL)
    Tuesday, January 26, 2010 1:17 PM

Answers

  • I'm not sure i'm getting all of this, but i want to comment on what i thought you might have said.

    1) The SP isn't to UPDATE a TABLE. It's is to do an action on an object. Whether that means 1 TABLE or many is totally irrelevant. The developers do not need to know what is done in the SP, just what the results are, like any other object.

    2) If the object may need multiple edits simultaneously, use XML as the input and make that the default. Then, if the convenience is requested, add a wrapper function for the single record edit that turns it into XML for the user and automatically calls the "real" SP.

    If convenience is handed to the developers on a silver platter, they will likely want to use it.
    • Proposed as answer by Hugo Kornelis Thursday, January 28, 2010 11:18 PM
    • Marked as answer by Kent Waldrop Saturday, February 13, 2010 3:27 PM
    Thursday, January 28, 2010 4:39 PM
    Answerer

  • Now this infrastructure is in production.  I am asked to speed it up and I again strongly suggest eliminating the cursors.  They don't want any of it -- gotta use the infrastructure.  I need comments and suggestions.


    I was very successful with developers taking out cursors from code when a sproc which tested on mini-size tables in development subsecond took 20 minutes in production before cancelling.

    However, if you need convincing for 100 msec (set based operation) from 700 msec (cursors), that may be more difficult.

    How about if you sit down with each developer and discuss cursors vs set based ?  Call a meeting with projector attached to your laptop? (donuts also!)

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by SandeepM_ Thursday, January 28, 2010 8:49 AM
    • Marked as answer by Kent Waldrop Saturday, February 13, 2010 3:27 PM
    • Edited by Kalman Toth Tuesday, September 25, 2012 5:57 AM
    Tuesday, January 26, 2010 1:30 PM

All replies


  • Now this infrastructure is in production.  I am asked to speed it up and I again strongly suggest eliminating the cursors.  They don't want any of it -- gotta use the infrastructure.  I need comments and suggestions.


    I was very successful with developers taking out cursors from code when a sproc which tested on mini-size tables in development subsecond took 20 minutes in production before cancelling.

    However, if you need convincing for 100 msec (set based operation) from 700 msec (cursors), that may be more difficult.

    How about if you sit down with each developer and discuss cursors vs set based ?  Call a meeting with projector attached to your laptop? (donuts also!)

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by SandeepM_ Thursday, January 28, 2010 8:49 AM
    • Marked as answer by Kent Waldrop Saturday, February 13, 2010 3:27 PM
    • Edited by Kalman Toth Tuesday, September 25, 2012 5:57 AM
    Tuesday, January 26, 2010 1:30 PM
  • I have had best success when I have used measurements to support what I am saying.  In some cases this has been sufficient in same cases not. 

    In October and November caught a series of three performance improvement requests for summary queries that were taking 2-5 seconds.  For one of the requests I was quickly able to double the throughput but for the other two I was able to triple the throughput.  In both cases the the improvements were deemed insufficient to warrant deployment.

    Sometimes you get a pretty good hand in a tough game.

    • Proposed as answer by SandeepM_ Thursday, January 28, 2010 8:50 AM
    Tuesday, January 26, 2010 2:21 PM

  • In October and November caught a series of three performance improvement requests for summary queries that were taking 2-5 seconds.  For one of the requests I was quickly able to double the throughput but for the other two I was able to triple the throughput.  In both cases the the improvements were deemed insufficient to warrant deployment.

    Sometimes you get a pretty good hand in a tough game.


    Kent,

    It also depends on the frequency of execution for the sproc. If executed 10,000 times a day, and you optimize it from 3 sec to subsecond, then an easy "sell". If executed only 50 times a day, a pretty hard sell.

    It is also worth explaining to developers when you bring down a sproc from few seconds to subsecond, not only that sproc gets faster, but the load lessens on the system itself (if very frequently executed sproc), therefore general response time improves.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by SandeepM_ Thursday, January 28, 2010 8:50 AM
    • Edited by Kalman Toth Tuesday, September 25, 2012 5:58 AM
    Wednesday, January 27, 2010 1:15 AM
  • Adding to KalMan and Kent, I would rather than to say, Please don’t disturb the Production environment.


    Please look on the following steps to handle it successfully

     

    1. Take the database to preproduction.
    2. Select the SP which is taking more time using cursor.
    3. Now change the Cursor with Loop or Update Statement if nested loop present
    4. Then check the performance of your new Sp
    5. if performance is better than Prod SP then take new SP to Production
    6. Again run in production and measure the performance
    7. Please carefully monitor this if everything is well then start development in Preproduction server for rest of the SP’s

     

    I remember, I had a good experience on this, as one Fact table takes more time to update. Then I wrote one update statement to takeout inner loop from nested loop query after that it comes to normal execution time.

     

    So always better to analyze the issue and take appropriate action which can give you best performance.

     

    Thanks,

    Sandeep

    Thursday, January 28, 2010 8:49 AM
  • I'm not sure i'm getting all of this, but i want to comment on what i thought you might have said.

    1) The SP isn't to UPDATE a TABLE. It's is to do an action on an object. Whether that means 1 TABLE or many is totally irrelevant. The developers do not need to know what is done in the SP, just what the results are, like any other object.

    2) If the object may need multiple edits simultaneously, use XML as the input and make that the default. Then, if the convenience is requested, add a wrapper function for the single record edit that turns it into XML for the user and automatically calls the "real" SP.

    If convenience is handed to the developers on a silver platter, they will likely want to use it.
    • Proposed as answer by Hugo Kornelis Thursday, January 28, 2010 11:18 PM
    • Marked as answer by Kent Waldrop Saturday, February 13, 2010 3:27 PM
    Thursday, January 28, 2010 4:39 PM
    Answerer
  • Thank you for your thoughts, guys.
    Saturday, February 13, 2010 3:28 PM