badly written SQL

  • Hello

    I have inherited an awful piece of code that badly needs optimising.  It currently is a stored procedure which runs two selects, inserting the rows into a temporary #table, updates the #table and then selects records from #table depending on the column called  Nullable <sigh>

    The Nullable column is populated with a CASE statement as follows 

             (CASE 

                WHEN (B.type 'IN'

                      AND (ISNULL(B.IN_Opening_Stock,00

                      --snipped to save your sanity

                      AND (ISNULL(B.sp_Int_Cum_Actual,00

                      AND (ISNULL(B.sp_Int_Projected_Tonnes,00THEN 'NULL'

                 WHEN   (B.type 'OP'

                          AND (ISNULL(B.OP_Month_Tar_Prod_Ton,00)

                           --snipped to save your sanity

                          AND (ISNULL(B.OP_Actual_Production,00

                          AND (ISNULL(B.OP_MC_Util_Percentage,00THEN 'NULL'

                 ELSE 'OK'

              ENDAS Nullable,

    What I have done is turn this into a View by throwing out the #table, unioned the two queries and moved the date criteria to the Report that executes against the view.  I am still retrieving 21951 but only 19344 are 'OK', as set by the code above.

    Can I say Where Nullable = 'OK' in my where clause?

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • It's a little annoying, but you either have to write the whole case statement out again in your where clause, or you have to use a derived table. e.g.

    select * from (

    select ...

             (CASE 

                WHEN (B.type 'IN'

                      AND (ISNULL(B.IN_Opening_Stock,00

                      --snipped to save your sanity

                      AND (ISNULL(B.sp_Int_Cum_Actual,00

                      AND (ISNULL(B.sp_Int_Projected_Tonnes,00THEN 'NULL'

                 WHEN   (B.type 'OP'

                          AND (ISNULL(B.OP_Month_Tar_Prod_Ton,00)

                           --snipped to save your sanity

                          AND (ISNULL(B.OP_Actual_Production,00

                          AND (ISNULL(B.OP_MC_Util_Percentage,00THEN 'NULL'

                 ELSE 'OK'

              ENDAS Nullable,

    ....

    ) a

    where Nullable = 'OK'

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • SELECT * FROM B

    WHERE

    NOT((B.type = 'IN' AND (ISNULL(B.IN_Opening_Stock,0) = 0 AND (ISNULL(B.sp_Int_Cum_Actual,0) = 0 AND (ISNULL(B.sp_Int_Projected_Tonnes,0) = 0) OR

        (B.type = 'OP' AND (ISNULL(B.OP_Month_Tar_Prod_Ton,0) = 0) AND (ISNULL(B.OP_Actual_Production,0) = 0 AND (ISNULL(B.OP_MC_Util_Percentage,0) = 0)


    Kindest Regards,

    Vasc

  • Cheers, I'll give these a go tomorrow (home time now)


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • First of all get rid of

    ISNULL(B.IN_Opening_Stock,0) = 0

    and all other such checks.

    Replace it with (B.IN_Opening_Stock IS NULL OR B.IN_Opening_Stock = 0)

    Looks more heavy but executes times faster.

    Second.

    You cannot optimise CASE statement. You must optimise SELECT or UPDATE statement. Sometimes you need to optimise your approach.

    _____________
    Code for TallyGenerator

  • lol.

    Thanks for the IsNull tip, I wasn't aware of that.  I would of thought thast IsNull did exactly what your code suggested 'under the hood' as it were.  If it doesn't, how does it work?

    Second

    I am trying to optimise the Select.  Although your suggestion optimises he CASE statement ('times faster')

    Sometimes you need to optimise your reading


    http://glossopian.co.uk/
    "I don't know what I don't know."

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

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