Why does COALESCE result in slow exec plan

swinstead 2016-04-05 23:51:30

When this DELETE statement uses COALESCE, the execution plan is much slower than when ISNULL is used instead. Can somebody help me understand why?

Aaron Bertrand 2016-04-06 01:54:39
Can you try both queries with OPTION (RECOMPILE) and after updating statistics on all relevant indexes? I don't see the ISNULL version in the plan you attached but since there is a huge discrepancy between estimated and actual rows all the way through the plan, it may just be that one of the query variations was stuck with a bad plan based on either different parameters or different data at compilation time or both.
SQLkiwi 2016-04-06 03:18:12
I can't give an exact answer based on an anonymized plan for only the COALESCE variant, but in general ISNULL and COALESCE will produce different plan shapes because:

  1. ISNULL is a single intrinsic function, while COALESCE is expanded to a CASE (IIF) statement. This can result in different cost estimations, and consequently different plan shapes.
  2. The optimizer has more tricks to hand with ISNULL than with COALESCE, particularly in the area of reasoning about the nullability of the input expressions. With ISNULL, the optimizer can sometimes remove entire predicates or subtrees through that reasoning.
  3. Both the above can sometimes work against ISNULL. It is sometimes necessary to choose the one that happens to work best for a given query.