Abusing ISNULL with NULLIF

In an effort to not have single switch case statements, some DBD's have leveraged a way to use ISNULL NULLIF.  Consider this:

DECLARE

  @first CHAR(1) = 'A'

, @second CHAR(1) = 'C';

SELECT CASE WHEN @first = 'B' THEN @second ELSE @first END;

SELECT ISNULL(NULLIF(@first , 'B') , @second);

Here we want to show the variable @first unless it equals 'B'.  In that case, we want to see @second.  For something simple, this is fine.  It may be harder to read for many, but it works.

This is where we went off the reservation and caused a concern.  Consider this:

DECLARE

  @table TABLE(Asset_Key        BIGINT

             , Asset_Datasource VARCHAR(64)

             , Asset_Group      VARCHAR(64)

             , Asset_tsID       VARCHAR(32)

             , Asset_amNumber   VARCHAR(64)

             , Asset_mcID       VARCHAR(64));

SELECT

  *

FROM

  @table T1

WHERE NOT EXISTS

(SELECT

   Asset_Key

 FROM

   @table T2

 WHERE

   T1.asset_datasource = T2.asset_datasource

   AND

   T1.Asset_Group = T2.Asset_Group

   AND

   T1.Asset_tsID = CASE

                     WHEN T1.Asset_tsID = 'NotDefined' THEN T2.Asset_tsID

                     ELSE ISNULL(NULLIF(T2.Asset_tsID , 'NotDefined') , ISNULL(NULLIF(T2.Asset_amNumber , 'NotDefined') , T2.Asset_mcID))

                   END

   AND

   T1.Asset_amNumber = CASE

                         WHEN T1.Asset_amNumber = 'NotDefined' THEN T2.Asset_amNumber

                         ELSE ISNULL(NULLIF(T2.Asset_amNumber , 'NotDefined') , ISNULL(NULLIF(T2.Asset_tsID , 'NotDefined') , T2.Asset_mcID))

                       END

   AND

   T1.Asset_mcID = CASE

                     WHEN T1.Asset_mcID = 'NotDefined' THEN T2.Asset_mcID

                     ELSE ISNULL(NULLIF(T2.Asset_mcID , 'NotDefined') , ISNULL(NULLIF(T2.Asset_tsID , 'NotDefined') , T2.Asset_amNumber))

                   END

);

Not only is this incredibly hard to read, but it performs poorly.  The solution for this case is to use UNIONs.  I'm not showing each combination, but you get the idea:

SELECT

  *

FROM

  @table T1

WHERE NOT EXISTS

(

SELECT

   Asset_Key

FROM

   @table T2

WHERE

   T1.Asset_tsID = T2.Asset_tsID

UNION

SELECT

   Asset_Key

FROM

   @table T2

WHERE

   T1.Asset_tsID = T2.Asset_tsID

        AND T1.Asset_amNumber = T2.Asset_amNumber AND T2.Asset_amNumber != 'NotDefined' 

        AND T1.Asset_mcID = 'NotDefined' 

UNION

SELECT

   Asset_Key

FROM

   @table T2

WHERE

   T1.Asset_tsID = T2.Asset_tsID

        AND T1.Asset_amNumber = 'NotDefined' 

        AND T1.Asset_mcID = T2.Asset_mcID AND T2.Asset_mcID != 'NotDefined'

);

For the specific case, this is the performance increase:

Old version:

(14520 row(s) affected)

Table 'dwAssetIntegration_Extract'. Scan count 5, logical reads 1068, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'dwAssetDimension_Final'. Scan count 5, logical reads 1987011, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 2, logical reads 449500498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 3609015 ms,  elapsed time = 3604367 ms.

New version:

(14520 row(s) affected)

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'dwAssetDimension_Final'. Scan count 8, logical reads 123085, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'dwAssetIntegration_Extract'. Scan count 7, logical reads 7476, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 1891 ms,  elapsed time = 2766 ms.