Store Procedure Optimization

Question

Store Procedure Optimization

rocky_498

SSCertifiable

Points: 6532
More actions
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...

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

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

Gullimeel Hall of Fame Points: 3871 More actions · Answer 1

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]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

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)

rocky_498 SSCertifiable Points: 6532 More actions · Answer 5

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

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