| Author |
Please help with simple proc-- slow CASE statement
|
|
| Dan English 2006-04-06, 1:23 pm |
| Hi, I am having trouble with this query... the CASE statement makes it
terribly slow, it is very fast without it. What is the best solution for
queries like this where it is predicated on different columns? Query has
been simplified for posting here.
Thanks,
Dan
--------------------------------------------------------------------------------------
declare @col CHAR(3) --normally this is passed in to the stored proc as a
param
set @col='CRE'
select *
from data
where
CASE @col
WHEN 'CRE' then created
WHEN 'MOD' then modified
WHEN 'XXX' then assigned
END
Between '2006-04-01' and '2006-04-02'
| |
|
| Why don't you do something like this?
if @col ='CRE'
begin
select *
from data
where created Between '2006-04-01' and '2006-04-02'
end
if @col ='MOD'
begin
select *
from data
where modified Between '2006-04-01' and '2006-04-02'
end
if @col ='XXX'
begin
select *
from data
where assigned Between '2006-04-01' and '2006-04-02'
end
Denis the SQL Menace
http://sqlservercode.blogspot.com/
| |
| Dan English 2006-04-06, 1:23 pm |
| "SQL" <denis.gobo@gmail.com> wrote in message
news:1144345877.726389.250870@v46g2000cwv.googlegroups.com...
> Why don't you do something like this?
Thanks, thats helps with a small query like this but the actual select
statement is pretty long, would be a lot of duplication.
| |
| kniemczak@gmail.com 2006-04-06, 8:23 pm |
| try:
declare @col CHAR(3) --normally this is passed in to the stored proc as
a
param
set @col='CRE'
select *
from data
where
(@col = 'CRE' AND created Between '2006-04-01' and '2006-04-02' ) OR
(@col = 'MOD' AND modified Between '2006-04-01' and '2006-04-02') OR
(@col = 'XXX' AND assigned Between '2006-04-01' and '2006-04-02' )
| |
| Dan English 2006-04-06, 8:23 pm |
| <kniemczak@gmail.com> wrote in message
news:1144348706.473651.16680@u72g2000cwu.googlegroups.com...
> try:
> (@col = 'CRE' AND created Between '2006-04-01' and '2006-04-02' ) OR
> (@col = 'MOD' AND modified Between '2006-04-01' and '2006-04-02') OR
> (@col = 'XXX' AND assigned Between '2006-04-01' and '2006-04-02' )
Thanks, but this is not any faster. Looks like I have to resort to dynamic
sql.
| |
| Dan English 2006-04-06, 8:23 pm |
| <kniemczak@gmail.com> wrote in message
news:1144348706.473651.16680@u72g2000cwu.googlegroups.com...
> (@col = 'CRE' AND created Between '2006-04-01' and '2006-04-02' ) OR
> (@col = 'MOD' AND modified Between '2006-04-01' and '2006-04-02') OR
> (@col = 'XXX' AND assigned Between '2006-04-01' and '2006-04-02' )
Apparently it still tries to access all three columns, even though @col only
matches one. In other words, it isn't doing a short-circuit boolean
evaulation. Anyone know why?
| |
| Jan Van der Eecken 2006-04-07, 7:24 am |
| Even if SQL were to shortcut this boolean expression, it would still have to
go all to the end if ((@col = 'XXX' AND assigned Between '2006-04-01' and
'2006-04-02' ) yielded TRUE. Where does @col come from by the way? Is it a
client that passes it on its own discretion, or does it come from some other
query?
"Dan English" <dan_english2@cox.net> wrote in message
news:ecakjrcWGHA.4768@TK2MSFTNGP05.phx.gbl...
> <kniemczak@gmail.com> wrote in message
> news:1144348706.473651.16680@u72g2000cwu.googlegroups.com...
>
> Apparently it still tries to access all three columns, even though @col
> only matches one. In other words, it isn't doing a short-circuit boolean
> evaulation. Anyone know why?
>
| |
| Dan English 2006-04-07, 1:23 pm |
|
"Jan Van der Eecken" <jvandereecken@omam.com> wrote in message
news:OZ5yHfjWGHA.3760@TK2MSFTNGP02.phx.gbl...
> Even if SQL were to shortcut this boolean expression, it would still have
> to go all to the end if ((@col = 'XXX' AND assigned Between '2006-04-01'
> and '2006-04-02' ) yielded TRUE. Where does @col come from by the way? Is
> it a client that passes it on its own discretion, or does it come from
> some other query?
Client passes it. Most of the time the first statment is true.
|
|
|
|