Slow-running SP

Question

Post reply

Slow-running SP

Mark Harley

SSCrazy

Points: 2259
More actions
September 19, 2007 at 6:01 am

#177180

I have a stored procedure that consists of creating two temp tables, joining them and then running SUM's on the data. Everything goes well until the sum's are performed on the table that results from the join. There are 8 SUM statements in my select (two of which involve basic math), and they are making the running time for the SP shoot way up (up to several minutes). Is there a way to streamline this? I'll paste the final portion of the SP below, in case that will help.
SELECT
pp.ClassNumber,
pp.ClassName,
pp.SubclassNumber,
pp.SubclassName,
SUM(pp.LPQty) 'LPInStock',
SUM(pp.LPCost) 'LPAvgCost',
SUM(pp.LPQty * pp.LPCost) 'LPTotalCost',
SUM(tp.TPQty) 'TPInStock',
SUM(tp.TPCost) 'TPAvgCost',
SUM(tp.TPQty * tp.TPCost) 'TPTotalCost',
(SUM(tp.TPQty) - SUM(pp.LPQty)) 'PCInStock',
(SUM(tp.TPQty * tp.TPCost) - SUM(pp.LPQty * pp.LPCost)) 'PCTotalCost'
FROM
#PreviousPeriod pp
INNER JOIN #ThisPeriod tp ON pp.ClassNumber = tp.ClassNumber AND pp.SubclassNumber = tp.SubClassNumber
GROUP BY
pp.ClassNumber,
pp.ClassName,
pp.SubclassNumber,
pp.SubclassName
ORDER BY
pp.ClassNumber,
pp.SubclassNumber

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

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi Mark,

Have you tried putting indexes on your tables?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Mark Harley SSCrazy Points: 2259 More actions · Answer 2

Hi Chris,

I decided to use table variables instead of temp tables, and that gave me a small performance increase, but the whole thing is still pretty slow. It's not possible to add an index to a table variable, is it?

Thanks,

Mark

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Hi Mark

You can put a clustered Primary key on a table variable.

I would suggest testing with both a table variable with indexes and a temp table with indexes.

Let me know what happens here?

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Mark Harley SSCrazy Points: 2259 More actions · Answer 4

Hi Chris,

Adding an index to the table variable seemed to be faster than doing the same to the temp tables, but either way it did shave a fair amount of time off the execution.

Thanks for all the help!

Mark

Megha Yadav SSCommitted Points: 1735 More actions · Answer 5

Hi !!!

I had 2 suggestions. Please do let me know just in case any of these helps

1) Try using CTE's in place of table variables ... I believe they are efficient ...

2) Try and check if this is any better ...All i tried was reduce the time the same operation is executed .....

Select

SubQuery.* ,
SubQuery.TPInStock - SubQuery.LPInStock as 'PCInStock'
SubQuery.TPTotalCost - SubQuery.LPTotalCost as 'PCTotalCost'

from

(SELECT
pp.ClassNumber,
pp.ClassName,
pp.SubclassNumber,
pp.SubclassName,
SUM(pp.LPQty) 'LPInStock',
SUM(pp.LPCost) 'LPAvgCost',
SUM(pp.LPQty * pp.LPCost) 'LPTotalCost',
SUM(tp.TPQty) 'TPInStock',
SUM(tp.TPCost) 'TPAvgCost',
SUM(tp.TPQty * tp.TPCost) 'TPTotalCost'
FROM
#PreviousPeriod pp
INNER JOIN #ThisPeriod tp ON pp.ClassNumber = tp.ClassNumber AND pp.SubclassNumber = tp.SubClassNumber
GROUP BY
pp.ClassNumber,
pp.ClassName,
pp.SubclassNumber,
pp.SubclassName) SubQuery

Group by

SubQuery.ClassNumber,
SubQuery.ClassName,
SubQuery.SubclassNumber,
SubQuery.SubclassName,
SubQuery.TPInStock,
SubQuery.LPInStock,
SubQuery.TPTotalCost ,
SubQuery.LPTotalCost

Order by

ORDER BY
SubQuery.ClassNumber,
SubQuery.SubclassNumber

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

It's an SQL Server 2000 forum... no CTE's here...

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