September 19, 2007 at 6:01 am
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
September 19, 2007 at 7:08 am
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]
September 19, 2007 at 7:38 am
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
September 19, 2007 at 8:00 am
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]
September 19, 2007 at 8:24 am
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
September 20, 2007 at 3:42 am
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
September 27, 2007 at 12:16 am
It's an SQL Server 2000 forum... no CTE's here...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply