Skip to main content

Parallax effect

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.