Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Robert L Davis — Wed, 30 Jun 2010 19:43:20 GMT

Reminds me of a discussion on Twitter recently that stemmed out of me not specifying a length for the varchar data type in an example of converting a date to a string.

They said they had been yelled at more than once for this practice because it was a performance hit. I challenged the person to prove it to me, and in the end, he said that the difference was so slight to be barely detectable.

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

mjswart — Wed, 30 Jun 2010 20:11:47 GMT

Regarding the typical abundance of low hanging fruit: Shhh!! you'll spoil it for the rest of us.

Along similar lines, it's about time we stopped worrying about user defined stored procedures with names that start with "sp".

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Mateus — Wed, 30 Jun 2010 20:29:53 GMT

That was a nice read, thanks!

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Brad Schulz — Wed, 30 Jun 2010 21:46:15 GMT

I guess the reason that ISNULL works for creating non-null columns with SELECT...INFO and COALESCE doesn't is because COALESCE is really just blindly translated into a CASE expression by the engine.

In other words...

SELECT COALESCE(Color,'')

is translated to...

SELECT CASE WHEN Color IS NOT NULL THEN Color ELSE '' END

And I guess it's harder to parse out that CASE expression to determine nullability than it is to interpret a simple ISNULL.

Just my guess...

--Brad

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

NULLgarity — Tue, 06 Jul 2010 16:56:04 GMT

Excellent post...

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Marc Brooks — Tue, 06 Jul 2010 18:20:25 GMT

The reason COALESCE doesn't work in the SELECT INTO is that at the "meta level", there's no guarantee that COALESCE's output value is non-null. e.g in this the COALESCE is NULL for the first row:

WITH Point(x,y) AS

(

SELECT NULL AS x, NULL AS y

UNION ALL

SELECT NULL, 1

UNION ALL

SELECT 2, NULL

)

SELECT ISNULL(x,0) AS NotNullX

, ISNULL(y,0) AS NotNullY

, COALESCE(x,y) AS IsNullXY

FROM Point;

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Adam Machanic — Tue, 06 Jul 2010 19:34:58 GMT

Marc: Your COALESCE returns a NULL for that row only because you're not using a constant for the final argument. If you were to use a constant, COALESCE would never return a NULL.

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Paul White — Mon, 19 Jul 2010 12:29:07 GMT

Excellent post today, Adam!

The behaviour seen in the Color example is fascinating. It doesn't surprise me that only ISNULL works to generate a NOT NULL column with SELECT...INTO since that's the same arrangement that works for computed columns, and that's a documented feature ("Computed Columns" in Books Online, right at the bottom of the entry).

What is *interesting* is that the "WHERE Color IS NOT NULL" part is insufficient to produce a NOT NULL column without the ISNULL.

The optimiser tracks column NULLability as it explores plan alternatives via 'properties', which can result in extra optimisation opportunities. The "WHERE Color IS NOT NULL" predicate sets a QO property bit to let later operators know that NULLs are not possible in that column from that point forward. (So far so good.)

You can see that in action since the query plan for the ISNULL version _does not contain_ an ISNULL expression anywhere! The ISNULL was in the Compute Scalar, but was optimised away by later application of a rule recognising that performing an ISNULL operation on an attribute that cannot be NULL is redundant and wasteful.

Nevertheless, even though the ISNULL was optimised away, the fact that it was there originally was enough to trigger the rule that defines the destination column as NOT NULL.

What I find surprising is that the SELECT...INTO doesn't use the QO property information when deciding if the column should be created as NULLable or not, without the ISNULL. Seems like a bug (of omission).

Something else just to illustrate the point. If we run:

DECLARE @C TABLE (Color NVARCHAR(15) NOT NULL); -- Note NOT NULL

INSERT @C (Color)

SELECT Color

FROM Production.Product;

...there is a RaiseIfNullInsert in the predicate property of the Table Insert iterator, to check for NULLs. If we add the WHERE Color IS NOT NULL clause, the RaiseIfNullInsert disappears - optimised away by the knowledge that Color cannot be NULL by then.

So, the property is there, the optimiser 'knows' the column can't contain NULLs and can optimise on that basis. Double weird that the optimiser doesn't reason that in the SELECT...INTO case.

Apologies for the long comment :)

Paul

re: Revisiting ISNULL, COALESCE, and the Perils of Micro-Optimization

Bob Frasca — Tue, 14 Sep 2010 19:04:17 GMT

Great post Adam. I particularly found your Color example interesting. It just goes to show that every tool has value when used appropriately.