Post reply

Store Procedure Optimization

  • July 6, 2012 at 11:33 pm

    #258565

    Hi Guys,

    I need your help. I have a store procedure and its taking to long to execute that package. Our user are complaining

    about to slow to retrieve that data. Please guide me which tips should i use for optimize this store procedure. I have 28 joins in this SP. Any help would be great.

    Thank You...

  • July 7, 2012 at 12:15 am

    #1509943

    For perforamnce issue you need to give the showplan,ddl and others details.Please see in my signature how to post the perf issue.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 7, 2012 at 1:53 pm

    #1510019

    rocky_498 (7/6/2012)

    Hi Guys,

    I need your help. I have a store procedure and its taking to long to execute that package. Our user are complaining

    about to slow to retrieve that data. Please guide me which tips should i use for optimize this store procedure. I have 28 joins in this SP. Any help would be great.

    Thank You...

    With 28 joins, there's a very strong probability that you have created a "Many-to-Many" join which is also known as an "Accidental Cross Join" which can take hours longer to execute that you would expect because of the vast number (millions and sometimes, billions) of internal rows T-SQL has to build to resolve such things. Chances are, there's either a DISTINCT or a GROUP BY in the query in a vain attempt to get rid of the duplicates it creates.

    Usually, the best way to fix this is to break the query into multiple smaller parts and store the interim results in Temp Tables. I've fixed many an hour long query to run in scant seconds this way.

    How do you identify where you need to split things up? Look at the Actual Execution plan for large arrows that have huge row counts associated with them to start with. There can be a lot more to it like looking at estimated number of rows vs actual rows but it's a great start.

    If you want specific help with this problem, please provide the information requested in the article found at the second link in my signature line below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 7, 2012 at 5:23 pm

    #1510038

    Jeff, I really appreciate your reply in detail. I will definite need your help here. Please could you provide me your email add so i can fwd SP to you.Once again thank you for your help....

  • July 8, 2012 at 3:16 pm

    #1510142

    rocky_498 (7/7/2012)

    Jeff, I really appreciate your reply in detail. I will definite need your help here. Please could you provide me your email add so i can fwd SP to you.Once again thank you for your help....

    Gosh, thank you for the extreme compliment but no. You need someone that has access to all of your data to really pull this off and my remote consulting days are pretty much over. My recommendation is for you good folks to sit down and start peeling the onion on this one. Not only will that bring the expertise in-house, but you'll also have great pride in learning how to solve these types of things.

    Give it a shot, Rocky. I know you guys can do this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 8, 2012 at 10:33 pm

    #1510162

    Jeff,

    Thanks for your reply and advice, if you can show me right path/ articles/tutorials that you can think is good learning for me that would be great, if you please provide me some useful tips that u used in your experience for optomize query/SP that would be great help.

    Thanks in advance.

  • July 10, 2012 at 9:37 am

    #1511043

    rocky_498 (7/8/2012)

    Jeff,

    Thanks for your reply and advice, if you can show me right path/ articles/tutorials that you can think is good learning for me that would be great, if you please provide me some useful tips that u used in your experience for optomize query/SP that would be great help.

    Thanks in advance.

    Given 28 joins I suspect there are a number of other issues at play here too. Forums are for SIMPLE, straight-forward assistance provided by FREE resources. For something this complex you REALLY need to engage a performance tuning professional. That will provide a second, even more important benefit - namely MENTORING you on HOW to do some basic performance analysis and refactoring yourself. I am sorry, but you simply cannot get what you seek from a few forum posts.

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

Viewing 7 posts - 1 through 6 (of 6 total)

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