Post reply

Slow-running SP

  • 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

  • September 19, 2007 at 7:08 am

    #734567

    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

  • September 19, 2007 at 7:38 am

    #734591

    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

    #734602

    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

  • September 19, 2007 at 8:24 am

    #734617

    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

    #734871

    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

    #736630

    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)
    Intro to Tally Tables and Functions

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

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