In an effort to not have single switch case statements, some DBD's have leveraged a way to use ISNULL NULLIF. Consider this:
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:
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:
For the specific case, this is the performance increase:
Old version:
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'
);
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:
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.