March 12, 2013 at 11:54 am
I am converting embedded SQL (in Java code) to a stored procedure.
They are building SQL WHERE clause dynamically in JAVA.
What is the best way to implement this java logic in SQL?
sqlStmt = "SELECT * FROM
Imports..tdsix_balance B
LEFT JOIN Imports..tdsix_account A ON B.ACID = A.ACID
LEFT JOIN (SELECT DISTINCT ACID, MAX(AsOf) AS ASOF FROM Imports..tdsix_account GROUP BY ACID) Z
ON A.ACID = Z.ACID"
" +
"where ";
if (account.equals("")){
sqlStmt = sqlStmt +
"(B.ACID like '5F%' or B.ACID like '5G%' or B.ACID like '5H%' or B.ACID like '5T%'" +
" or B.ACID like '5J%' or B.ACID like '01%' or B.ACID like '03%')" +
"and B.asof =? " +
"and (A.asof = Z.ASOF or A.asof is null) " +
"order by B.acid";
}
else {
sqlStmt = sqlStmt +
"B.ACID like '" + account + "%' " +
"and B.asof =? " +
"and (A.asof = Z.ASOF or A.asof is null) " ;
To me it looks like I have to use CASE in WHERE clause but I don't know how to write all those "LIKE ... OR LIKE ..."
Or maybe I should forget about CASE and just use IF:
IF @account IS NULL
BEGIN
SELECT * FROM
Imports..tdsix_balance B
LEFT JOIN Imports..tdsix_account A ON B.ACID = A.ACID
LEFT JOIN (SELECT DISTINCT ACID, MAX(AsOf) AS ASOF FROM Imports..tdsix_account GROUP BY ACID) Z
ON A.ACID = Z.ACID
WHERE
LEFT(B.ACID,2) IN ('5F','5G','5H','5T','5J','01','03')
AND(A.AsOf = Z.ASOF or A.AsOf IS NULL)
ORDER BY
B.ACID
END
ELSE
BEGIN
SELECT * FROM
Imports..tdsix_balance B
LEFT JOIN Imports..tdsix_account A ON B.ACID = A.ACID
LEFT JOIN (SELECT DISTINCT ACID, MAX(AsOf) AS ASOF FROM Imports..tdsix_account GROUP BY ACID) Z
ON A.ACID = Z.ACID
WHERE
B.ACID LIKE @account+'%'
AND (A.AsOf = Z.ASOF OR A.AsOf IS NULL)
ORDER BY
B.ACID
END
March 12, 2013 at 2:24 pm
Personally, if the where clause is that complex I like the idea of just using an IF statement like in your second example if for no other reason that it is more readable and 2 years from now it will be easier for you or someone else to figure out what it's doing.
On the other hand, I don't use CASE statements in the WHERE clause at all anymore. I use something like this:
AND ((@Status = 1 AND table.Status = 1)
OR
(@Status = 0))
AND ((@ProductColor = 'yellow' AND table.Color = 'yellow')
OR
(@ProductColor = 'red' AND table.Color = 'red')
OR
(@ProductColor IN ('blue','green','')))
March 12, 2013 at 2:57 pm
This is a type of catch-all query. Gail's blog has a great way of handling this type of thing.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 7:53 am
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
March 13, 2013 at 8:01 am
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 code
IF @account = '' OR LEN(@account) = 0 OR @account IS NULLWHERE Id1 BETWEEN 'AA' AND 'KK' AND Id2 IS NULL
ELSEIF @account <> '' AND LEN(@account) > 0 AND @account IS NOT NULL
WHERE Id1 BETWEEN 'MM' AND 'ZZ' AND Id2 IS NOT NULL
ELSEIF @account > 100
WHERE ....... and so on
IF statements control the flow of processing. You can't use them like that in a where clause.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 8:10 am
You will have to suffer with performance issues because of the inability to properly use execution plans with this type of thing unless you can use the dynamic sql approach that Gail suggests.
Something like this.
WHERE
(
(@account = '' OR LEN(@account) = 0 OR @account IS NULL)
AND
(
(Id1 BETWEEN 'AA' AND 'KK' AND Id2 IS NULL)
OR
(Id1 BETWEEN 'MM' AND 'ZZ' AND Id2 IS NOT NULL)
)
)
OR
(
@account > 100
)
The big issue here is that it difficult to decipher the logic. Not too bad when you write it but in 6 months when you have to debug it or the next person comes along it is painful.
Surprising your boss is ok with pass through sql and not dynamic parameterized sql. Many people don't like dynamic sql because they don't understand how to properly protect yourself using parameters.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 9:29 am
Gail's blog recommends using dynamic SQL
but our boss doesn't like it at all.
Is your boss SQL Server professional or just an idiot with a lot of ambitions?
For what you're trying to achieve, dynamic SQL is the best option!
So just tell your boss, if he wants good code, you better to follow the right direction, not the one you boss like or dislike. Sounds like kindergarten to me!
March 13, 2013 at 8:28 pm
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.
March 13, 2013 at 9:02 pm
In this case I would have both the Dynamic SQL stored proc and non dynamic stored proc.
Compare both the execution plans. Also checking statistics (SET STATISTICS IO ON) and clearing before executing each stored proc.
It seems your manager really wants assurance and was burnt in the past. I would add some a @debug paramter to the dynamic stored proc. If @debug=1 then print the statement and do not execute. This way it makes it easier for a dev to see what is going on. I use this all the time if I am doing dynamic SQL.
If you see huge gains by using dynamic sql and allow for easy debugging your boss might be happy. It is worth a try. I think you will be surprised at the results.
March 13, 2013 at 9:07 pm
Forgot to include cache
Also checking statistics (SET STATISTICS IO ON) and clearing cache [/b]before executing each stored proc.
--Do not run in PROD!!!
DBCC FREEPROCCACHE
DBBC DROPCLEANBUFFERS
March 14, 2013 at 3:58 am
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
😉
March 14, 2013 at 6:58 am
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 NULLwhereStmt = "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 on googles mail service
March 14, 2013 at 7:53 am
;-)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.
March 14, 2013 at 8:20 am
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.... 🙂
March 14, 2013 at 9:39 am
RVO (3/14/2013)
I tried this
select * from IndustryMapWHERE 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 on googles mail service
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply