badly written SQL

Question

badly written SQL

David Jackson

SSCertifiable

Points: 6414
More actions
July 25, 2006 at 11:16 am

#115935

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,0) = 0
                  --snipped to save your sanity
                  AND (ISNULL(B.sp_Int_Cum_Actual,0) = 0
                  AND (ISNULL(B.sp_Int_Projected_Tonnes,0) = 0) THEN 'NULL'
             WHEN   (B.type = 'OP'
                      AND (ISNULL(B.OP_Month_Tar_Prod_Ton,0) = 0)
                       --snipped to save your sanity
                      AND (ISNULL(B.OP_Actual_Production,0) = 0
                      AND (ISNULL(B.OP_MC_Util_Percentage,0) = 0) THEN 'NULL'
             ELSE 'OK'
          END) AS 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."

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

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

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,0) = 0

--snipped to save your sanity

AND (ISNULL(B.sp_Int_Cum_Actual,0) = 0

AND (ISNULL(B.sp_Int_Projected_Tonnes,0) = 0) THEN 'NULL'

WHEN (B.type = 'OP'

AND (ISNULL(B.OP_Month_Tar_Prod_Ton,0) = 0)

--snipped to save your sanity

AND (ISNULL(B.OP_Actual_Production,0) = 0

AND (ISNULL(B.OP_MC_Util_Percentage,0) = 0) THEN 'NULL'

ELSE 'OK'

END) AS Nullable,

....

) a

where Nullable = 'OK'

Ryan Randall

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

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

David Jackson SSCertifiable Points: 6414 More actions · Answer 3

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 4

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

David Jackson SSCertifiable Points: 6414 More actions · Answer 5

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."