Post reply

How to tune a job?

  • April 13, 2009 at 2:10 pm

    #202062

    I'm having performance issues with a job. It only runs once daily, at night. It's currently taking 2 hours to run.

    The job calls a proc.

    This proc in turn truncates some staging tables, then calls 4 other procs.

    Each of these procs creates a temp table (all run this step extremely fast).

    Then, each proc runs through each record in it's temp table (through a cursor), executing a fifth procedure. The fourth procedure takes the most time (the first three take about 10 minutes total).

    This fifth procedure handles 12 pages (770 lines) of business logic, cumulating in the populating of one of those afore-mentioned staging tables. This procedure itself runs quickly (once); the problem is that it is being run tens of thousands of times over the course of the job.

    This procedure creates two temp tables, reads from 18 tables (including the temps), and writes to 2.

    Additionally, it selects from 4 udfs that read from 4 total tables (3 new) and perform additional business logic. One of these udfs calls one of the other ones.

    And finally, half-way through this procedure is a gigantic loop that encompasses the remainder of the procedure. Inside this loop 7 of the 18 tables are read from, 1 is written to, and all four functions are called (one through the other).

    Overall, this procedure makes 22 calls to tables before the while loop, and 25 calls to tables inside the while loop.

    I've looked at all of the query's execution plans, and all appear to be optimized.

    I was able to identify some calls that the procedure would not need to make when being called from the fourth procedure. However, this only improved things by < 3 minutes.

    So, what else can I look at? This procedure appears to be just too complicated to try to make set-based. Am I stuck with it the way it is?

    Thanks for your advice...

    Wayne

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 13, 2009 at 8:06 pm

    #977002

    It's hard to say without seeing any code or execution plans; if you can at least post the plans in .sqlplan format then we can see if anything obvious jumps out.

    Ultimately you are likely going to have to rewrite everything into a set-based approach if you want optimal performance, but if other constraints make this impossible then there may be small changes that result in large benefits - cursors plus nested UDFs plus loops typically don't provide lightning-fast code.

  • April 14, 2009 at 12:30 pm

    #977528

    Cursors and looping are going to cause performance problems. If you cannot remove the cursors and looping you should look at how you are handling transactions. Are you doing it in 1 huge transaction or do you BEGIN and COMMIT transactions within your loops.

    You may also want to look at moving your huge looping procedure to an SSIS package that will handle looping better than T-SQL.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 3 (of 3 total)

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