Post reply

Optimizing Performance of Stored Procedures in SQL Server 2000

  • July 31, 2007 at 4:01 pm

    #118059

    Hello All,

    I need some of the expert advice on a situation I'm facing.

    We have about 240K Customers in our database. We created a Customer Segmentation process to segment our Customers in their proper group and then do targetted marketing.

    I have created a Stored Procedure called SP_CustomerProfiles_Main that has the functionality and makes use of 2 other stored procedures (sp_getnextlevel_single and SP_GetNextLevel_Multiple). This process use 6 tables and 6 Views and generates a table (called Customer_Profiles table) that contains the segmented data.

    I'm using temporary tables throughout the process to speed up execution and have utilized the use of Cursors to operate on individual rows.

    The job is scheduled to run every weekend on a dedicated database server and was taking 16 to 20 hours to execute when we had about 196K customers. But now the performance has significantly dropped to 55 to 70+ hours to process about 240K customers.

    I have tried few minor things to optimize it but haven't seen any promising results.

    Questions:

    1) How can I optimize the performance? Please specify the best practices.

    2) Is there any difference in executing a job in a database that has numbers of job scheduled to run and has lot more activity VS an isolated database just created to execute this job?

    3) Is using Cursor the optimal approach, to operate on single row of data?

    4) Any idea what may have caused to contribute in the performance drop? The system configuration is exactly the same when the process was originally created.

    Any help in this matter will be greatly appreciated.

    Best Regards,

    JB

     

    Kindest Regards,

    JB

  • August 1, 2007 at 7:48 am

    #723862

    Hello,

    For question 1:

    I've learned a lot from http://www.sql-server-performance.com/articles/per/main.aspx for auditing performance, perfomance tuning. Use query analyzer to view queryplans, and sqlprofiler to see the duration of individual statements.

    Basic: check normalisation,check indexes (also on temp tables), request only necessary data. Mostly set-bases operations (do this for all things with that attribute) works faster than row by row (if this thing has attribute x, then do....). Drop temptables when you no longer need them. You might check if your stored procedures benefit from the WITH RECOMPILE flag. Avoiding functions in where-clauses.

    For question 2:

    Depends they are operating on the same data (locks) or share the same physical storage (io contention)

    For question 3:

    Be aware there are several kind of cursors. Readforward, non updatable cursors are usually the fastest.For SQL set-based solutions tends to be much faster than their cursor-counterparts.

    For question 4:

    Most likely the increase of records, taking each operation a little longer.

    The layout of involved tables,views... and the actual procedures would help in further diagnostics.

    Kind regards.

  • August 1, 2007 at 8:01 am

    #723869

    Hi Jo,

    Thanks for replying. Actually I posted this message in another section [SQL Server 7, 2000 --> T-SQL] and I have added some additional details there as well. Please take a look and post any possible answer there.

    BTW - Is there a resource that I can look in to to learn how to use SQL set-based solutions?

    Thanks for replying.

    JB

     

     

    Kindest Regards,

    JB

  • August 1, 2007 at 8:55 am

    #723898

    1) WROX Press - Transact SQL for Beginners

    2) Cursors are bad from a performance perspective, as has already been stated.  REALLY REALLY bad. 

    3) If you MUST use cursors, try to use FAST_FORWARD ones.

    4) Joining 6 tables and using 6 views will be inefficient.  Perhaps bad design is at play here too.

    5) Use of temporary tables could lead to a lot of recompiles, which is VERY expensive in SQL2K.

     

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 1, 2007 at 2:56 pm

    #724004

    Thanks for the input SQLGuru. I'll try this approach and let you know if I need any help.

    JB

     

    Kindest Regards,

    JB

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

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