February 9, 2008 at 12:17 am
I need to formulate a query in which the column that I reference in the WHERE clause is dynamic. For Ex:
SELECT 1 FROM TableA WHERE colA = 'A'
SELECT 1 FROM TableA WHERE colB = 'B'
I am not allowed to use Dynamic SQL.
The solution i thought of is to use the CASE statement in the WHERE clause like shown below. But my concern is the performance of such type of Query.
(assuming @ColName will be passed a parameter)
DECLARE @ColName varchar(100)
DECLARE @ColValue varchar(100)
SET @ColName = 'ColA'
SET @ColValue = 'Test'
SELECT *
FROM TableA
WHERE 1 = (CASE @ColName
WHEN 'ColA' THEN
CASE WHEN ColA = @ColValue THEN 1
ELSE 0
END
WHEN 'ColB' THEN
CASE WHEN ColB = @ColValue THEN 1
ELSE 0
END
END
)
I guess if any index is defined on either ColA or ColB, then it won't be used in the above case. Besides, I would like to know if there any major performance drawbacks.
Appreciate feedback.
Thanks.
February 9, 2008 at 2:30 am
There are a couple of methods.
If you can use an IF / ELSE block then do something like
if @targetCol = 'A'
select * from myTable where colA = @value
else if @targetCol = 'B'
select * from myTable where colB = @value
Otherwise, you could do something like
select *
from myTable
where @targetCol = 'A'
and colA = @value
UNION ALL
select *
from myTable
where @targetCol = 'B'
and colB = @value
Finally you could try
select *
from myTable
where (@targetCol = 'A' and colA = @value)
OR (@targetCol = 'B' and colB = @value)
Queries 2 and 3 should be pretty similar as far as the query optimiser goes. Try each and see which gives you the best performance - you'll probably only notice if your table has >1000 rows.
February 9, 2008 at 2:24 pm
I like Option#3. It seems more straigtforward and should run faster as well.
Thanks for the feedback.
February 11, 2008 at 2:34 pm
The third option will have to use an index scan instead of an idex seek, which will be slower than the other options.
The "if ... " version can have problems with cached execution plans for "A" being used for "B" and can thus have problems.
The Union All version is usually the fastest.
Test them and see. If the table is small enough, or the server underloaded enough, it may not matter.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply