COALESCE vs ISNULL and nested ISNULLs

The plans attached (estimates only) show the difference of the same query with the exception that one uses COALESCE and the other is ISNULL. Much has been written about the difference before. COALESCE expands to a CASE expression and the datatype is determined by evaluating all expressions in the function. ISNULL doesn't behave that way. I get that part. I don't know how to use that knowledge and explain the difference in these plans.

Plan.pesession (34.0 kB)
avatar image By Dan Holmes 725 asked Jan 22, 2016 at 06:46 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Jan 22, 2016 at 06:55 PM

Looks like nested ISNULLs does a better job in this case, avoiding the hash matches that make the COALESCE version so expensive. But I think it may be a red herring that that is the only difference - it seems that in the good query the index seek on tblCalendar only estimates 337 rows and has more (and different) predicates than the clustered index seek used in the other query, which estimates 6,574 rows. I'd have to do a diff to spot any other differences in the query text, but is it possible these plans were constructed at different times (when different stats were in place) and/or with different values for the @Date parameter? I noticed the parameters tab doesn't include compiled or runtime values. Is the behavior the same with OPTION (RECOMPILE);?

avatar image Dan Holmes Jan 22, 2016 at 07:13 PM

The text is the same. I started with the COALESCE text and made the ISNULL text from it. My only changes were those changes.

The estimates on that table are what is messing with my head. Why would that change because of an ISNULL vs COALESCE?

avatar image Aaron Bertrand ♦ Jan 22, 2016 at 07:15 PM

Internal implementation? There are other behavior differences between them as well, for example I highlight some observed differences in this tip. Paul might have some more concrete ideas when he swings around next. In the meantime, I would say go with the one that performs better.

10|10000 characters needed characters left

1 answer: sort voted first

Although COALESCE and ISNULL expressions can sometimes express the same logical requirement (if written very carefully), there are many internal differences that mean cardinality estimation and plan shape choices will often be different.

The query in question is too complex, with too many unknown factors (view definitions, data types, column nullability and so on) to analyse in detail, but:

  • As noted in the question, the two have different behaviours with respect to data types. Where this introduces implicit type conversions, cardinality estimation can be affected.

  • COALESCE is expanded to a fixed form of CASE expression, which cannot be split into component parts or moved around the plan tree in the same way nested ISNULL expressions can. In short, ISNULL generally allows the optimizer more freedom. This extra freedom can result in better or worse plans, of course.

  • SQL Server can reason about nullability with ISNULL but not so much with COALESCE. This can enable simplifications if an expression known not to be nullable (internally, during compilation) has this function applied. It can also affect cardinality estimation by removing null steps from intermediate histograms.

  • Cardinality estimation for ISNULL may be performed using different logic than for CASE WHEN x IS NOT NULL THEN y ELSE .... More generally, there is no guarantee that logically equivalent predicates expressed using different syntax will produce the same estimation, or take the same path through optimization. In fact, most often they will not, especially in non-trivial cases. Small changes in CE can have large effects on final plan shape.

It is not sensible to say which will be better on average. Personally, I prefer to avoid both wherever possible. I also much prefer to write simpler queries with purely relational operators. In my experience, this leads to the best results from the optimizer, and the most predictable results.

The queries in the question are good examples of (estimated) low-cost queries where the optimizer will not spend much time (by design) trying to find anything better than a broadly reasonable solution. Most choices in this type of query are based on heuristics and guesses rather than detailed CE calculations anyway.

avatar image By SQLkiwi ♦ 6.6k answered Jan 23, 2016 at 11:48 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x166
x26

asked: Jan 22, 2016 at 06:46 PM

Seen: 328 times

Last Updated: Jan 23, 2016 at 11:48 AM