Do I use case or coalesce or something else?

Hi all!
I'm runnnig the following query:
declare @.Action int
set @.Action = 2
SELECT * FROM estates
WHERE
((@.Action!=1)OR(
est_ZipCode BETWEEN 12000 AND 12999 OR
est_ZipCode BETWEEN 14000 AND 14999
))AND
((@.Action!=2)OR(
est_ZipCode BETWEEN 16000 AND 16999
))AND
((@.Action!=3)OR(
est_ZipCode BETWEEN 11000 AND 11999 OR
est_ZipCode BETWEEN 13000 AND 13999 OR
est_ZipCode BETWEEN 15000 AND 15999
))AND
((@.Action!=4)OR(
est_ZipCode BETWEEN 17000 AND 19999
))
and it runs much, much slower then:
SELECT * FROM estates
WHERE est_ZipCode BETWEEN 16000 AND 16999
/*equivalent to action 2*/
Why is that? Is there a better way of solving this, perhaps with case
or coalesce?
Any help appreciated,
NiclasIn order to get the best plan you will either need to use dynamic sql or a
number of if else statements.
Your problem stems from the optimiser not knowing the value of @.Action so it
must optimiser the whole query.
Have you tried UNION ALL, not experimented with that; the dynamic sql would
be the neatest in my opinion but has some requirements for security on the
base tables rather than just exec permission on the stored proc.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137422736.659781.271560@.o13g2000cwo.googlegroups.com...
> Hi all!
> I'm runnnig the following query:
> declare @.Action int
> set @.Action = 2
> SELECT * FROM estates
> WHERE
> ((@.Action!=1)OR(
> est_ZipCode BETWEEN 12000 AND 12999 OR
> est_ZipCode BETWEEN 14000 AND 14999
> ))AND
> ((@.Action!=2)OR(
> est_ZipCode BETWEEN 16000 AND 16999
> ))AND
> ((@.Action!=3)OR(
> est_ZipCode BETWEEN 11000 AND 11999 OR
> est_ZipCode BETWEEN 13000 AND 13999 OR
> est_ZipCode BETWEEN 15000 AND 15999
> ))AND
> ((@.Action!=4)OR(
> est_ZipCode BETWEEN 17000 AND 19999
> ))
> and it runs much, much slower then:
> SELECT * FROM estates
> WHERE est_ZipCode BETWEEN 16000 AND 16999
> /*equivalent to action 2*/
> Why is that? Is there a better way of solving this, perhaps with case
> or coalesce?
> Any help appreciated,
> Niclas
>|||"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137422736.659781.271560@.o13g2000cwo.googlegroups.com...
> Hi all!
> I'm runnnig the following query:
> declare @.Action int
> set @.Action = 2
> SELECT * FROM estates
> WHERE
> ((@.Action!=1)OR(
> est_ZipCode BETWEEN 12000 AND 12999 OR
> est_ZipCode BETWEEN 14000 AND 14999
> ))AND
> ((@.Action!=2)OR(
> est_ZipCode BETWEEN 16000 AND 16999
> ))AND
> ((@.Action!=3)OR(
> est_ZipCode BETWEEN 11000 AND 11999 OR
> est_ZipCode BETWEEN 13000 AND 13999 OR
> est_ZipCode BETWEEN 15000 AND 15999
> ))AND
> ((@.Action!=4)OR(
> est_ZipCode BETWEEN 17000 AND 19999
> ))
> and it runs much, much slower then:
> SELECT * FROM estates
> WHERE est_ZipCode BETWEEN 16000 AND 16999
> /*equivalent to action 2*/
> Why is that? Is there a better way of solving this, perhaps with case
> or coalesce?
> Any help appreciated,
> Niclas
I agree with Tony in part.
I had problems with one query (that contained a few ANDs and ORs) that was
solved by spliting it in 2 (or more) and using Union (or Union All). Try
this first. If this doesn't work, try writing 4 separate queries handling
each value of @.Action and use a series of IFs.
I would not go the dynamic SQL way.|||Thanks for your responses,
since the query contains about 20 actions and some other conditions
also, I think dynamic SQL sounds like the best solution.
Thanks again,
Niclas
Is there no way to build a switch statement inside the where-clause?|||"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1137426449.243655.33600@.g49g2000cwa.googlegroups.com...
> Thanks for your responses,
> since the query contains about 20 actions and some other conditions
> also, I think dynamic SQL sounds like the best solution.
> Thanks again,
> Niclas
> Is there no way to build a switch statement inside the where-clause?
No control-of-flow inside a Select statement.
CASE is possible, of course.|||It's a common problem with UI that have a filter option, you need to change
your query according to the parameters the user has filtered on.
Doing it in one big SELECT with CASE or OR's won't give a very good query
plan, often very poor and general.
This is where we need to use either IF ELSE or dynamic SQL, the IF ELSE
route has the problem of duplicate code and if there are lots of filters
then you could end up with 20, 30 or more IF ELSE's which is a lot to
maintain.
There is help at hand with the old dynamic SQL security on base table
requirement in SQL Server 2005 now.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:eU7tHJsGGHA.1312@.TK2MSFTNGP09.phx.gbl...
> "tonicvodka" <tonicvodka@.hotmail.com> wrote in message
> news:1137426449.243655.33600@.g49g2000cwa.googlegroups.com...
> No control-of-flow inside a Select statement.
> CASE is possible, of course.
>|||The standard syntax is <> instead of the stolen C syntax != for
non-equal. Your code and the sample you gave do not quite match. I
think that you want somethign like this.
SELECT * -- production code should have column names
FROM Estates
WHERE CASE @.action
WHEN 1
THEN CASE WHEN est_zipcode BETWEEN 12000 AND 12999
THEN 'T'
WHEN est_zipcode BETWEEN 14000 AND 14999
THEN 'T' ELSE 'F' END
WHEN 2
THEN CASE WHEN est_zipcode BETWEEN 16000 AND 16999
THEN 'T' ELSE 'F' END
WHEN 3
THEN CASE WHEN est_zipcode BETWEEN 11000 AND 11999
THEN 'T'
WHEN est_zipcode BETWEEN 13000 AND 13999
THEN 'T'
WHEN est_zipcode BETWEEN 15000 AND 15999
THEN 'T' ELSE 'F' END
WHEN 4
THEN CASE WHEN est_zipcode BETWEEN 17000 AND 19999
THEN 'T' ELSE 'F' END
etc.
ELSE 'F' END = 'T';
The CASE expression checks the WHEN clauses in order, so put the most
likely case first. This applies to the intermost WHENs also. I have
used both forms of the CASE expression and nested them, but you might
want to look up the syntax so you feel comfortable with it. The 'T'
and 'F' stand for True and False. It is a common SQL porgramming trick
for complex logic. You might also want to look at Logic Gem or another
decision table tool that will help with this kind of problem.
This lets the optimizer do its job on a single query, without having to
kludge dynamic SQL, use UNIONs or resort to procedural code.|||>> In order to get the best plan you will either need to use dynamic SQL or
a number of if else statements. <<
You really are trapped in an OO and 3GL mindset and cannot think
declaratively!
I used to tell students that it takes a year of SQL programming to have
the"declarative revelation" so that you stop thinking in terms of
dynamic on-the-fly coding, IF-THEN_ELSE control flows, etc. and just
write a single query.
I am not sure how good SQL-2005 is with CASE expressions, but DB2 does
a fine job.|||> I am not sure how good SQL-2005 is with CASE expressions, but DB2 does
> a fine job.
No it does not, and you just don't get it. In the real world "one size
fits all" approach (just write a single query) may perform very poorly.
Just think: consider a simple query
select <some columns> from some_table where some_date between
@.date_from and @.date_to
If there is a ( non-clustered for SQL Server) index on some_date, which
plan should the optimizer choose? It could scan the table (tablespace
scan in DB2 parlance), or it could access the table through the index.
Neither plan is the best for all the cases. Recompiling the query makes
perfect sense if teh table is big.
just think: consider a search form with several search conditions for
the user to fill, like zip, last name, fisrt name, whatever. Dynamic
SQL is most likely to run much better that generic stufff like
where zip=@.zip or @.zip is null
and last_name=@.last_name or @.last_name is null
and the reason is simple: all real life major RDBMS are well capable of
choosing different plans for diffrent parameter values, DB2 included.
For big tables the performance price of choosing only one plan for all
the cases may be way too steep.
So yes, in simpler cases we have if statements, in more complex ones we
just go for dynamic SQL.|||On 16 Jan 2006 10:31:21 -0800, --CELKO-- wrote:

>The standard syntax is <> instead of the stolen C syntax != for
>non-equal. Your code and the sample you gave do not quite match. I
>think that you want somethign like this.
>SELECT * -- production code should have column names
> FROM Estates
> WHERE CASE @.action
> WHEN 1
> THEN CASE WHEN est_zipcode BETWEEN 12000 AND 12999
> THEN 'T'
> WHEN est_zipcode BETWEEN 14000 AND 14999
> THEN 'T' ELSE 'F' END
> WHEN 2
> THEN CASE WHEN est_zipcode BETWEEN 16000 AND 16999
> THEN 'T' ELSE 'F' END
> WHEN 3
> THEN CASE WHEN est_zipcode BETWEEN 11000 AND 11999
> THEN 'T'
> WHEN est_zipcode BETWEEN 13000 AND 13999
> THEN 'T'
> WHEN est_zipcode BETWEEN 15000 AND 15999
> THEN 'T' ELSE 'F' END
> WHEN 4
> THEN CASE WHEN est_zipcode BETWEEN 17000 AND 19999
> THEN 'T' ELSE 'F' END
> etc.
> ELSE 'F' END = 'T';
>The CASE expression checks the WHEN clauses in order, so put the most
>likely case first. This applies to the intermost WHENs also. I have
>used both forms of the CASE expression and nested them, but you might
>want to look up the syntax so you feel comfortable with it. The 'T'
>and 'F' stand for True and False. It is a common SQL porgramming trick
>for complex logic. You might also want to look at Logic Gem or another
>decision table tool that will help with this kind of problem.
>This lets the optimizer do its job on a single query, without having to
>kludge dynamic SQL, use UNIONs or resort to procedural code.
Hi Joe,
But the single query tha the optimizer gets to do its job on doesn't
have any comparison predicate of the form
"<column name> <comp op> <expression>"
or "<expression> <comp op> <column name>".
The only thing the optimizer can do is to sigh and settle for a complete
table scan.
Using 4 SELECT statements and an IF ELSE tree or (shudder) dynamic SQL
would enable the optimizer to check if an index on est_zipcode can be
sed. That would result in faster execution.
Hugo Kornelis, SQL Server MVPsql