COALESCE vs Dynamic SQL Approach

[Jackie Boy]

I read on following url: http://www.sqlteam.com/item.asp?ItemID=2077

that COALESCE should be much more effecient than Dynamic SQL querys,, but
why does my COALESCE query take more than 1000 times longer time to execute
than my dynamic one.

I Cant understand IT.....

[Tony Rogerson]

WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)

For the above the ISNULL is more efficient then COALESCE.

At the end of the day using dynamic SQL in this scenario is far better -
make sure you try and parameterise the query using sp_executesql...

SET @sql = 'SELECT * FROM sysobjects WHERE type=@type'

EXEC sp_executesql @sql, N'@type char(1)', @type = 'T'

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk

"Jackie Boy" <jack.ro...@sreg.com> wrote in message
news:uo1LobHNBHA.2400@tkmsftngp02...

[Jack Ronnovius]

Okey...
I run "estimated message plan" and found out why it took longer time

the COALESCE query ended in a clustered index scan..
while my dynamiq query ended in an index seek.

Conclusion,, when I use COALESCE the optimizer doesnt use my own configured index seek. Is it possible to force it to use this in this case?? And if it is possible how do I do??

Thanx by the way, for your answer..

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Itzik Ben-Gan]

First of all, I would follow Tony's recommendation to use ISNULL instead of
COALESCE where possible. Turns out that the optimizer tunes ISNULL better
than COALESCE.
Yes, you can force the optimizer to use a certain index by using an index
hint: FROM <table_name> (index = <index_name>), but, when you use an index
hint the query becomes static, and you lose all the optimizer's powerful
statistics based dynamic ability to generate a plan that is suitable for the
current query / data. Besides, in the specific scenario at hand which index
would you use? The whole idea of the query is to filter on a requested
column.

With dynamic execution, as non-portable, non standard, ugly as it may be,
you have better chances for a better performing query, as the result query
gets optimized, and in this query, all filters use literals and not
functions. This doesn't mean that dynamic execution doesn't have it's
disadvantages. For example, a user executing a stored procedure that uses
dynamic execution requires direct permissions to perform the activity which
is run dynamically, and not just execute permissions on the proc itself.

You need to consider all factors and decide what's more important to you...

--
BG

Hi-Tech College, Israel
http://sql.hi-tech.co.il

"Jack Ronnovius" <jack.ro...@sreg.com> wrote in message
news:ujP6A#HNBHA.3036@tkmsftngp02...

[Gert-Jan Strik]

If I remember correctly, COALESCE is ANSI SQL compliant, and ISNULL is
not.

If this is true, then the ISNULL solution is just as non-portable and
non standard as the dynamic sql solution.

Anyway, the reason why a query like WHERE Cus_Name =
COALESCE(@Cus_Name,Cus_Name) results in an index scan or table scan (as
opposed to an index seek) is, because COALESCE(@Cus_Name,Cus_Name) is
non-SARGable. In this case, when @Cus_Name is null, SQL-Server needs to
evaluate all rows.

Dynamic SQL can provide a solution. IMO a dynamic sql solution is
usually messy, and hard to maintain because of the complexity.

Another posible solution which is good for performance, is something
like:

If @Cus_Name IS NULL
Begin
SELECT ..
End
Else
Begin
SELECT .. WHERE Cus_Name = @Cus_Name
End

This kind of solution has several disadvantages:
1. the code is hard to maintain, because of all the copying and pasting
and only the small changes between the queries;
2. theoretically the number of branches grows exponentially with the
number of COALESCE-constructs. However, see example below;
3. you are doing the work that you should expect SQL-Server to do for
you. It is just too bad that SQL-Server does not handle this situation
very efficiently.

Suppose you have several COALESCE-constructs in your query. If you
choose to use this kind of solution, and are willing to do SQL-Server's
job, then you could probably get sufficient performance using something
like:

If @Cus_Name IS NOT NULL
SELECT ..
WHERE Cus_Name = @Cus_Name

AND Cus_City = COALESCE(@Cus_City,Cus_City)
AND Cus_Country = COALESCE(@Cus_Country,Cus_Country)

Else
Begin
If @Cus_City IS NOT NULL
SELECT ..
WHERE Cus_City = @Cus_City
AND Cus_Country = COALESCE(@Cus_Country,Cus_Country)
Else
Begin
If @Cus_Country IS NOT NULL
SELECT ..
WHERE Cus_Country = @Cus_Country
Else
SELECT ..
End
End

For optimal performance, you should test the most selective column
first. So in this example, the customer name is assumed to be the most
selective column, then customer city, and customer country is assumed to
be least selective.

Hope this helps.
Gert-Jan

[Zachary Wells]

A solution that both bypasses COALESCE and (from what I'm told, I haven't
actually tested it) doesn't cause an index scan is this:

WHERE (Field1 = @FieldValue or @FieldValue is null)

Zach

"Gert-Jan Strik" <so...@toomuchspamalready.nl> wrote in message
news:3B952887...@toomuchspamalready.nl...

[Umachandar Jayachandran]

Not true! Since the variable value is not known at compile time, it will
still do an index scan & run as slow as the original query. The correct way
to optimize this is to write it like:

WHERE Field1 LIKE COALESCE( @SearchVal, '%' )

This will perform as fast or faster than the dynamic SQL. The one
advantage you get with dynamic SQL is when you have multiple search
conditions, you can suppress some columns easily & the plan would use only
indexes that are required. With static SQL, the plan is nearly fixed. I
usually prefer the static SQL because the code is much easier to read &
maintain. In majority of cases, you can tune the queries without using
dynamic SQL.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

[Zachary Wells]

I stand corrected. After typing that response (note where I said I hadn't
tested it) I decided to test it. And lo and behold, my way did an index scan
too! Goes to show you that you should test these things instead of taking
the information as fact! Anyway, I tested your method and it indeed does do
an index seek instead of an index scan. Thanks for the tip!!

Zach

"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:udiHRcZNBHA.1932@tkmsftngp02...

[Gert-Jan Strik]

Umachandar,

do you also know a trick that works if the column datatype is non-char
(for example int)?

Gert-Jan

[Umachandar Jayachandran]

Yes. You can do something like:

SET @MinVal = -POWER( 2., 31 ) -- or whatever minimum based on your data
SET @MaxVal = POWER( 2., 31 ) - 1
SET @IntVal = 123

WHERE intCol BETWEEN COALESCE( @IntVal, @MinVal ) And
COALESCE( @IntVal, @MaxVal )

You can do the same for datetime columns too! These actually work pretty
good. I have used this technique instead of dynamic SQL lot of times with
nice results. These work based on the boundary values for each datatype &
hence you need to hard-code that in the code. Alternatively, you can do MIN
or MAX queries & set the values.

[Gert-Jan Strik]

Thanks, good stuff!!

Gert-Jan

[Steve Kass]

Is the COALESCE solution really more efficient than the straightforward

WHERE (intCol = @IntVal) OR (@IntVal IS NULL) ??

Steve Kass
Drew University

[Umachandar Jayachandran]

Yes. Since you are using variables, SQL Server is only going to produce
a plan that takes care of both OR conditions. In most cases, this will be
either a clustered index scan or any non-clustered index that matches the
where clause.
Think of it, when you are saying "(intCol = @IntVal) OR (@IntVal IS
NULL)", there is no way to know what the value of the variable would be. So
you have two possible plans, one that use the index on "intcol" to locate a
row and another that can just scan the least expensive index. So it is going
to pick the second strategy.
There are however cases where the COALESCE approach would be slightly
inefficient when you are scanning all rows. The reason being that we may
scan the index that is slightly more expensive if you are reading all rows.

[Zachary Wells]

That solution does an Index SCAN where the other solution does an Index
SEEK.

Zach

"Steve Kass" <sk...@drew.edu> wrote in message
news:3B97DA7A...@drew.edu...

[Steve Kass]

Hm. I would have thought SQL Server would realize that the truth value of
@IntVal IS NULL would be the same for every row of the table. When one does
SELECT * INTO T FROM S WHERE 1=0 does it check the WHERE condition for every
row?

Oh well.

Steve

[Steve Kass]

I guess what I hadn't realized was that the plan for an sp had to be fixed.
If SQL Server could compile the sp to "If @IntVal is null, produce the result
this way, and if it is not, produce it this other way" I can see this would
get pretty complicated fast, so it makes more sense now. If the OR
query is run on the fly, it will be more efficient. But then you wouldn't
have this problem in the first place. I get it...

Steve