Help with a Long running stored procedure

Question

Help with a Long running stored procedure

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Sorry folks... I lost track of this one...

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

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 2

Would replacing the UDF's with tables containing the necessary data improve performance? I am thinking of the UDF that returns a 1 if the transaction is a peak time transaction. It would be a lot easier to maintain the table than a UDF if the peak times change. I'm thinking a table with the country id and columns for start and end times for the peak period. That would allow for the IsPeak to be determined by a simple case statement.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Ross McMicken (10/12/2009)
Would replacing the UDF's with tables containing the necessary data improve performance? I am thinking of the UDF that returns a 1 if the transaction is a peak time transaction. It would be a lot easier to maintain the table than a UDF if the peak times change. I'm thinking a table with the country id and columns for start and end times for the peak period. That would allow for the IsPeak to be determined by a simple case statement.

Undoubtedly it would. This suggestion was made a while back, the OP has yet to respond specifically about that.

Clear Sky SQL
My Blog[/url]

ClusterJunkie Old Hand Points: 385 More actions · Answer 4

Certainly an option to consider - however prior to then am toying with the idea of stored the calcs as persisted in temp/permanent tables...indexed where possible.

problem I'm having is the greater than equal to etc...

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

The overhead of calling a function is quite large

http://www.sqlservercentral.com/Forums/Topic790541-145-2.aspx#bm790978

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

newbie2009 (10/9/2009)
Jeff, thats exactly it however the execution plan pretty much the same in the dev environment and it runs in 50mins which has been difficult to explain to be honest...
obviously I/O demands on live are extreme by conmparison, but by a factor of 4....?
Pls see row counts attached.

First, the 50 mins is a bit long... I'd expect a report like this to run in a lot less time.

The other thing is, you say the execution plans are identical and that may be... except for the row counts that show up on the arrows. Does the dev box have the same number of rows as the production box?

And, my apologies... had other fires with sticks in them and haven't been able to spend much time on this even after I found it again...

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

ClusterJunkie Old Hand Points: 385 More actions · Answer 7

Yes the row counts and execution plans are the same - dev is refreshed overnight, agreed on the report time - it has to get faster quickly.

Agree with most if not all above on query re-design, however am at odds to explain behaviour.