May 15, 2009 at 7:40 am
I am trying to find more efficient way of writing this query.
There are two input parameters eg. @Type (4 different Type + Null
) and @value.
Depending on the Type and value data retrieve is filtered.
Select * from tableA
where case when @Type = Lname then Lname = @Value
when @type = fName then Fname = @Value
and so on....
Currently I have a logic in place as if then else...
If @Type = 'Lname'
Begin
select * from tableA
where Lname = @Value
End
else If @Type = 'Fname'
Begin
select * from tableA
where Fname = @Value
end
else if @type is null
begin select * from TableA
end
TIA,
Natasha
May 15, 2009 at 7:51 am
the following should help:
Select *
from tableA
where case
when @Type = 'Lname' then Lname
when @Type = 'fName' then Fname
when @Type is null then .....
else .....
end = @Value
Notice how the @Type evaluates to a column name, then the entire case statement is then compared to @value
May 15, 2009 at 8:04 am
Natasha:
That's actually not a bad way to go. Your instinct might be to combine all of these into one big master query that uses the parameters to logically switch the WHERE conditions, but that is almost always a bad idea. Logical search conditions that get "switched" on and off are not handled well by the query optimizer because it has to come up with one plan that covers all of these possible conditions. Consequently, it tends to pick a plan that always runs slowy, but always works, like a table scan. So ultimately, using IF to switch the search conditions is actually better than using CASE or other WHERE expressions.
The only problem is that having all of these different queries together in one stored procedure might result in an execution plan that does not take all of them into account well, however, most of the time that I have checked one of these they seemed to use the right query plan.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2009 at 8:25 am
RBarryYoung (5/15/2009)
Natasha:Logical search conditions that get "switched" on and off are not handled well by the query optimizer because it has to come up with one plan that covers all of these possible conditions. Consequently, it tends to pick a plan that always runs slowy, but always works, like a table scan. So ultimately, using IF to switch the search conditions is actually better than using CASE or other WHERE expressions.
I'm totally agree with Barry.... using the IF is a better approach ....
May 15, 2009 at 8:28 am
Here's a script demonstrating the problem, using your current code example, Natasha, along with the code that Sam provided showing how to get CASE into the WHERE clause:
--====== First Create TableA and add indexes: ======
Create Table TableA (
ID int identity(1,1) primary key
, Lname varchar(255)
, Fname varchar(255)
)
Create index idxTableA_Fname on TableA(Fname)
Create index idxTableA_Lname on TableA(Lname)
go
--====== Now Load it with data: ======
Insert into TableA (Lname, Fname)
Select [name], [name]
from master.sys.syscolumns
go
--====== Create the IF based stored procedure ======
Create Proc spGet_TableA(@type as varchar(20), @value varchar(255))
AS
Begin
If @Type = 'Lname'
Begin
select * from tableA
where Lname = @Value
End
else If @Type = 'Fname'
Begin
select * from tableA
where Fname = @Value
end
else if @type is null
begin select * from TableA
end
End
go
--====== Create the CASE based stored procedure ======
Create Proc spGet_TableA_case(@type as varchar(20), @value varchar(255))
AS
Begin
Select *
from tableA
where case
when @Type = 'Lname' then Lname
when @Type = 'fName' then Fname
when @Type is null then Null
end = @Value
End
go
--====== Now Execute Both ======
--(actually, you should select these lines and user "Control-L"
-- to display the query plans)
EXEC spGet_TableA 'Lname', 'id'
go
EXEC spGet_TableA_case 'Lname', 'id'
go
If you display the query plans for the two EXEC's at the end, you will see that while you original query uses a different index, depending on the @Type parameter, the combined query has a plan that just always scans the table.
I have attached the query plans from my system for convenience.
(EDIT: the attachment had the wrong plan in it. I have corrected this.)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2009 at 8:34 am
Thank you to both of you. I was thinking the same that using if then else will be more efficient than case since case does not use indexes and majority of the time table scan is performed. With case code has clarity in reading as oppose to bunch of if then else statements but in programming world efficiency is a priority.
Once again thank you both for convincing me 🙂 about my original code
Natasha
May 15, 2009 at 11:53 am
You could also consider the option of dynamic querying
create table tableA (Lname varchar(10), Fname varchar(10))
insert into tableA (Lname,Fname) select 'a1','b1' union select 'a2','b2'
--select * from tableA
declare @Type varchar(10),
@value varchar(10),
@sqlstr nvarchar(100);
set @Type = 'Lname';
set @value = 'a2';
set @sqlstr = 'select * from tableA ';
If @Type = 'Lname'
Begin
set @sqlstr = @sqlstr + ' where Lname = ''' + @value + ''''
End
else If @Type = 'Fname'
Begin
set @sqlstr = @sqlstr + ' where Fname = ''' + @value + '''';
end
print @sqlstr
exec sp_executesql
@stmt= @sqlstr,
@params= N'@value varchar(255)',
@value= @value;
May 15, 2009 at 12:00 pm
gyessql (5/15/2009)
You could also consider the option of dynamic querying
I am a big proponent of dynamic SQL, but I would not recommend it, because it not necessary for these cases unless the IF..ELSE structure proves inadequate. Plus, dynamic SQL introduces other problems, especially having to protect against SQL Injection attacks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2010 at 4:41 am
We can use case statement in where clause as follows:
declare @x int
Select col1,col2
from table1
where col1 = case when @x = 1 then 1000
when @x = 2 then 5000
end
Regards,
Tatoba
March 27, 2010 at 8:01 am
One more alternative, that is optimized well:
SELECT *
FROM TableA
WHERE @Type = 'Lname'
AND Lname = @Value
UNION ALL
SELECT *
FROM TableA
WHERE @Type = 'Fname'
AND Fname = @Value
UNION ALL
SELECT *
FROM TableA
WHERE @Type IS NULL;
The constant checks on @Type become start-up filters in the plan, so the parts of the UNION that don't match at run time are not actually executed at all.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 27, 2010 at 9:59 am
Nice idea Paul. I've tried some things like this in the past, but I could never seem come up with a query pattern that would be retain a stable optimization across a large number of cases. Do you know if your approach works on both 2005 and 2008? (I had a lot more trouble with my attempts on 2005 than on 2008)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2010 at 8:32 pm
RBarryYoung (3/27/2010)
Nice idea Paul. I've tried some things like this in the past, but I could never seem come up with a query pattern that would be retain a stable optimization across a large number of cases. Do you know if your approach works on both 2005 and 2008? (I had a lot more trouble with my attempts on 2005 than on 2008)
Yes it works on both, but is definitely better with simpler plans. I used to use this pattern a fair bit with 2005, less so these days. As always, it all depends on the details. Both methods need careful consideration, particularly concerning parameter sniffing. If the UNIONed queries are relatively simple, they would often be accompanied by an OPTION (RECOMPILE).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 28, 2010 at 5:39 am
Thanks, Paul, good to know. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply