Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

CASE in WHERE clause Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 3:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 2,858, Visits: 5,131
RVO (3/13/2013)
My boss is a professional. He was a developer recently.
I saw some of his stored procedures - good code.
I think he has a point. A few years ago at one large bank
production support team had big problems debugging complex long stored procedures
with lots of dynamic SQL. Production Support Team Lead just begged us to avoid using too much dynamic SQL.
Some people argue that for dynamic SQL, SQL Server engine cannot build optimized execution plan.
Not sure who's right who's wrong.


I'm sure. Your boss and Production Support Team are wrong on the following:
"for dynamic SQL, SQL Server engine cannot build optimized execution plan"

Now, you can write crap code in any form, dynamic or non-dynamic.
For the issue you are solving, properly written dynamic SQL will outperform any other solution. If you try, you will find it yourself! For dynamic-SQL optimizer will find and cach the best possible plans for each of SQL variation which will be built. If you use IF or CASE WHEN based solution, optimizer will not be able to come up with effective plan for any of the cases. Why? Gail Shaw explained it very well in her blog. If you have a doubt - just check it yourself.

Please remember, that there were many people around who argued that the Earth is flat, as if it would be round, we would fall down from it. http://en.wikipedia.org/wiki/Flat_Earth_Society





_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1430849
Posted Thursday, March 14, 2013 6:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 4,364, Visits: 6,206
RVO (3/13/2013)
Thanks all for your response.

Gail's blog recommends using dynamic SQL
but our boss doesn't like it at all.
I have to learn how to handle it either with CASE
or IF. My problem with IF was when there are
a lot of conditions IF becomes messy so I try to find out
if I can write logic in WHERE clause more accurate, easy to understand.
I'm talking about something like this:

Pseudo Java:
IF @account = '' OR LEN(@account) = 0 OR @account IS NULL
whereStmt = "WHERE Id1 BETWEEN 'AA' AND 'KK' AND Id2 IS NULL"
ELSEIF @account <> '' AND LEN(@account) > 0 AND @account IS NOT NULL
whereStmt = "WHERE Id1 BETWEEN 'MM' AND 'ZZ' AND Id2 IS NOT NULL"
ELSEIF @account > 100
whereStmt = "WHERE ......." and so on



You will either use dynamic sql to do this work or you will have to code a bajillion permutations of actual tsql that is hit with a massive sequence of IF statements testing the parameters or you will suffer HORRIBLY BAD PERFORMANCE AND CONCURRENCY. There is absolutely no other option. From my quick review I will say that dynamic sql is by far the best solution here. Tell your boss that I have gotten FIVE ORDERS OF MAGNITUDE performance improvement from scenarios like this by using dynamic sql. And concurrency goes WAY up when you stop doing table scans and hash joins that will result from the IS NULL OR construct. Guard against SQL Injection and you are good to go.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1430935
Posted Thursday, March 14, 2013 7:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
Thanks a lot for your response.
I always feel a very friendly supportive environment here.

I will definetly compare performance of regular vs dynamic SQL procedures.
But later.

My goal now is to come up with a "pure TSQL" mechanism
that handles this dynamic WHERE clause.
I see it all over the place in the Java code I am converting.
So it's like a pattern that I need find out how to handle nicely.
I feel like if I give up and do it with dynamic SQL - I surrender
and don't improve my TSQL skills ..

Again, a pattern is (java code)

sqlStmt = "select * from IndustryMap";
sqlStmt += " WHERE 1=1 " + whereSQLStmt;
if(cusip != null && cusip.length()>0){
whereSQLStmt += " and id_cusip = '" + cusip + "' ";}

if(ticker != null && ticker.length()>0){
whereSQLStmt += " and ticker = '" + ticker + "' ";}

if(exchangeCode != null && exchangeCode.length()>0){
whereSQLStmt += " and exch_Code = '" + exchangeCode + "' ";

They dynamically build WHERE clause
if input parameter is not null.
There are hundreds of these cases in Java.
Post #1430977
Posted Thursday, March 14, 2013 8:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
I tried this
select * from IndustryMap
WHERE 1 =1
AND id_cusip = COALESCE(@cusip,id_cusip)
AND ticker = COALESCE(@ticker,ticker)
AND exch_code = COALESCE(@exchangeCode,exch_code)

and was ready to scream Hurray but realized that
when any of the columns have a null value
this won't work. Because "AND col = NULL" is not valid.
Keep searching....
Post #1431000
Posted Thursday, March 14, 2013 9:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 4,364, Visits: 6,206
RVO (3/14/2013)
I tried this
select * from IndustryMap
WHERE 1 =1
AND id_cusip = COALESCE(@cusip,id_cusip)
AND ticker = COALESCE(@ticker,ticker)
AND exch_code = COALESCE(@exchangeCode,exch_code)

and was ready to scream Hurray but realized that
when any of the columns have a null value
this won't work. Because "AND col = NULL" is not valid.
Keep searching....


That will get you HORRIBLE plans too for several reasons.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431069
Posted Thursday, March 14, 2013 9:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 4,364, Visits: 6,206
Note, this isn't just about the WHERE clause. You can also potentially eliminate complete hits (joins) on tables if you don't actually need to filter on them based on input parameters.

I am wondering - why are you looking to get rid of a java process that already creates the proper (assuming it does) dynamic SQL?


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1431070
Posted Thursday, March 14, 2013 10:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 2,858, Visits: 5,131
Keep searching....
I also like to invent bicycles in my free time. Stupid clients/employers, they don't like to pay me for spending time on such an exciting hobby of mine ...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1431097
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse