Why does COALESCE result in slow exec plan
When this DELETE statement uses COALESCE, the execution plan is much slower than when ISNULL is used instead. Can somebody help me understand why?
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:
- 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.
- 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.
- 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.