help with stored procedure tuning.

Question

help with stored procedure tuning.

Lucky9

Default port

Points: 1470
More actions
June 13, 2013 at 1:22 pm

#294757

trying to fine tune a stored procedure
it has 10 union all between select scripts..performance is very slow...what is the alternate for union all..
and all the select statements have derived tables...so performance is very slow and sometimes we get the time out error also...
and in where clause we have left and max also...i think those are the 2 functions that are making the performance slow as well ...so what would be the alternate for left function..
i cannot paste any sql and explain plans...but if somebody can tell me the alternates for union all and left functions..that would be really great...

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not sure if there is much we can do if you can't post anything. Last I checked my crystal ball was broken and still in the shop, and the force isn't that strong.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Some divide and conquer strategy might help, meaning to use temp tables or staging tables instead of massive union alls.

Without more details is hard to give an advise. Maybe you could share the code changing the table and column names, same thing with the execution plan.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Union all usually one of the most efficient ways of concatenating result sets. The alternative to LEFT functions is usually to not use the function at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

Lucky9 (6/13/2013)
trying to fine tune a stored procedure
it has 10 union all between select scripts..performance is very slow...what is the alternate for union all..
and all the select statements have derived tables...so performance is very slow and sometimes we get the time out error also...
and in where clause we have left and max also...i think those are the 2 functions that are making the performance slow as well ...so what would be the alternate for left function..
i cannot paste any sql and explain plans...but if somebody can tell me the alternates for union all and left functions..that would be really great...

UNION ALL simply concatenates result sets so I doubt that is the cause of your poor performance. It will be impossible to provide a solution when you have not provided anything to work with, but I can suggest to tackle each select statement separately.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]