Replacing isnull condition in where clause

Question

I have a where clause as follows:

where table1.col1=isnull(table2.col1,table1.col1)

This condition does not utilize the index in table2.col1 and performance is slow.

So I replaced it with following case statement: where table1.col1= case table2.col1 when null then table1.col1 else table2.col1 end

Performance increased dramatically, however the number of records returned is different when table2.col1 has null records in it.

Here is the real query I have been trying to optimize, written by someone else long before and utilizing temp tables.I have commented all possible options I have tried.

SELECT h.deal_date h_date,i.deal_date i_date,h.source_deal_header_id source_deal_header_id_h,h.per per_h,
        i.source_deal_header_id source_deal_header_id_i,i.per per_i,
        COALESCE(map.fas_book_id,i.fas_book_id,h.fas_book_id,-1) fas_book_id,
        COALESCE(map.eff_test_profile_id,i.eff_test_profile_id,h.eff_test_profile_id,-1)  eff_test_profile_id,
        h.no_indx,h.deal_id1 h_deal_id,i.deal_id1 i_deal_id,
        CASE WHEN h.link_effect_date>i.link_effect_date THEN h.link_effect_date ELSE i.link_effect_date END link_effect_date,
        h.CurveName,h.term_start,h.term_end
    INTO #perfect_match
     FROM #hedge h INNER JOIN #item i ON h.term_start=i.term_start
        AND h.term_end=i.term_end AND h.volume=i.volume AND h.buy_sell=i.buy_sell
        AND h.used=0 AND i.used=0 AND h.no_indx=i.no_indx AND h.no_terms=i.no_terms
        and h.initial_per_ava>=0.01 and i.initial_per_ava>=0.01
        inner join #no_dice_deal nd on i.source_deal_header_id=nd.source_deal_header_id
        inner join (select distinct * from #map_n_curve) map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
        --inner join #map_n_curve map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
            and h.book_map_id=isnull(map.h_book_map_id,h.book_map_id)   --isnull(map.h_book_map_id,-1)
            and i.book_map_id=isnull(map.i_book_map_id,i.book_map_id)   --isnull(map.i_book_map_id,-1)
            --and h.book_map_id=case when map.h_book_map_id IS null then h.book_map_id else map.h_book_map_id end  
            --and i.book_map_id=case when map.i_book_map_id is null then i.book_map_id else map.i_book_map_id end
            --and h.book_map_id=case  map.h_book_map_id when null then h.book_map_id else map.h_book_map_id end  
            --and i.book_map_id=case map.i_book_map_id  when null then i.book_map_id else map.i_book_map_id end
            --and (map.h_book_map_id is null or h.book_map_id=map.h_book_map_id)
            --and (map.i_book_map_id is null or i.book_map_id= map.i_book_map_id)
            --option (recompile)    

The condition : where table1.col1= case table2.col1 when null then table1.col1 else table2.col1 end gives better performance , all others are slow running.

Temp tables hedge and item has following structure.

CREATE TABLE #hedge(
                curve_id INT,
                source_deal_header_id INT,
                deal_date DATETIME,
                term_start DATETIME,
                term_end DATETIME,
                deal_volume FLOAT,
                buy_sell VARCHAR(1) COLLATE DATABASE_DEFAULT   NULL,
                per FLOAT DEFAULT 0,
                volume FLOAT,
                used BIT DEFAULT 0,
                fas_book_id INT,
                eff_test_profile_id INT,
                idx_vol FLOAT, --for perfect match
                no_indx INT,
                no_terms INT,
                initial_vol_ava  FLOAT,
                initial_per_ava  FLOAT,
                operation_status VARCHAR(1) COLLATE DATABASE_DEFAULT   NULL,
               --n:netting, m=matching
                deal_id1 VARCHAR(150) COLLATE DATABASE_DEFAULT ,
                link_effect_date DATETIME,
                CurveName VARCHAR(250) COLLATE DATABASE_DEFAULT   NULL,
                book_map_id int,fas_sub_id int,org_curve_id INT,uom_id int
            )

     CREATE TABLE #item(
                curve_id INT,
                source_deal_header_id INT,
                deal_date DATETIME,
                term_start DATETIME,
                term_end DATETIME,
                deal_volume FLOAT,
                buy_sell VARCHAR(1) COLLATE DATABASE_DEFAULT   NULL,
                per FLOAT DEFAULT 0,
                volume FLOAT,
                used BIT DEFAULT 0,
                idx_vol FLOAT,  --for perfect match
                no_indx INT,
                no_terms INT,
                initial_vol_ava  FLOAT,
                initial_per_ava  FLOAT,
                operation_status VARCHAR(1) COLLATE DATABASE_DEFAULT   NULL, 
                --n:netting, m=matching
                org_buy_sell VARCHAR(1) COLLATE DATABASE_DEFAULT  ,
                deal_id1 VARCHAR(150) COLLATE DATABASE_DEFAULT   NULL,
                link_effect_date DATETIME
                ,eff_test_profile_id INT
                ,fas_book_id INT
                ,org_curve_id INT,book_map_id int,fas_sub_id int,uom_id int
            )

temp table #map_n_curve is created as follows:

SELECT  i.curve_id i_curve_id,h.curve_id h_curve_id,i.book_map_id i_book_map_id,h.book_map_id h_book_map_id,i.fas_book_id,min(i.eff_test_profile_id) eff_test_profile_id
into #map_n_curve
from tableA......joins......

Temp table #no_dice_deal can be ignored in joins, as it has no impact in performance.

Only temp table #map_n_curve join has impact on performance.

Please suggest how to improve performance when using isnull condition in where clause.

Answer 1

One thing that I can say is that the rewrite that you have isn't quite doing what you expect. The SIMPLE case statement allows only an equality check. That equality check doesn't do anything special with NULLs, so it won't handle NULL values in the way that you want it to. You can see this with a simple example. The following query returns no data:

DECLARE @COL2 INT = NULL;

SELECT 1
WHERE 1 = CASE @COL2 WHEN NULL THEN 1 ELSE 0 END

The following query returns 1 row:

DECLARE @COL2 INT = NULL;

SELECT 1
WHERE 1 = CASE WHEN @COL2 IS NULL THEN 1 ELSE 0 END;

Other than that, you're really not giving us a lot to go on here. I'm going to assume that your query is of the following form:

SELECT *
FROM table1
INNER JOIN table2 ON table1.col2 = table2.col2
where table1.col1=isnull(table2.col1,table1.col1);

In which case, the following queries should all return the same results. Perhaps one of them will give you better performance:

Not using ISNULL or CASE:

SELECT *
FROM table1
INNER JOIN table2 ON table1.col2 = table2.col2
where table1.col1 IS NOT NULL AND (table2.col1 IS NULL OR table1.col1 = table2.col1);

Using a CASE statement:

SELECT *
FROM table1
INNER JOIN table2 ON table1.col2 = table2.col2
where table1.col1 = CASE WHEN table2.col1 IS NULL THEN table1.col1 ELSE table2.col1 END;

Using UNION ALL:

SELECT *
FROM table1
INNER JOIN table2 ON table1.col2 = table2.col2
where table1.col1 = table2.col1

UNION ALL

SELECT *
FROM table1
INNER JOIN table2 ON table1.col2 = table2.col2
WHERE table1.col1 IS NOT NULL AND table2.col1 IS NULL;

Answer 2

I made following changes for the query, which gave better performance.Thanks Joe and Kumar for your suggestions.

SELECT h.deal_date h_date,i.deal_date i_date,h.source_deal_header_id source_deal_header_id_h,h.per per_h,i.source_deal_header_id source_deal_header_id_i,i.per per_i,COALESCE(map.fas_book_id,i.fas_book_id,h.fas_book_id,-1) fas_book_id,
     COALESCE(map.eff_test_profile_id,i.eff_test_profile_id,h.eff_test_profile_id,-1)  eff_test_profile_id,h.no_indx,h.deal_id1 h_deal_id,i.deal_id1 i_deal_id
     ,CASE WHEN h.link_effect_date>i.link_effect_date THEN h.link_effect_date ELSE i.link_effect_date END link_effect_date,h.CurveName,h.term_start,h.term_end
 INTO #perfect_match_b
  FROM #hedge h INNER JOIN #item i ON h.term_start=i.term_start
        AND h.term_end=i.term_end AND h.volume=i.volume AND h.buy_sell=i.buy_sell
        AND h.used=0 AND i.used=0 AND h.no_indx=i.no_indx AND h.no_terms=i.no_terms
        and h.initial_per_ava>=0.01 and i.initial_per_ava>=0.01
        inner join #no_dice_deal nd on i.source_deal_header_id=nd.source_deal_header_id
        inner join (select distinct * from #map_n_curve) map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
            and h.book_map_id=map.h_book_map_id
            and i.book_map_id=map.i_book_map_id;


SELECT h.deal_date h_date,i.deal_date i_date,h.source_deal_header_id source_deal_header_id_h,h.per per_h,i.source_deal_header_id source_deal_header_id_i,i.per per_i,COALESCE(map.fas_book_id,i.fas_book_id,h.fas_book_id,-1) fas_book_id,
     COALESCE(map.eff_test_profile_id,i.eff_test_profile_id,h.eff_test_profile_id,-1)  eff_test_profile_id,h.no_indx,h.deal_id1 h_deal_id,i.deal_id1 i_deal_id
     ,CASE WHEN h.link_effect_date>i.link_effect_date THEN h.link_effect_date ELSE i.link_effect_date END link_effect_date,h.CurveName,h.term_start,h.term_end
     INTO #perfect_match_a 
     FROM #hedge h INNER JOIN #item i ON h.term_start=i.term_start
        AND h.term_end=i.term_end AND h.volume=i.volume AND h.buy_sell=i.buy_sell
        AND h.used=0 AND i.used=0 AND h.no_indx=i.no_indx AND h.no_terms=i.no_terms
        and h.initial_per_ava>=0.01 and i.initial_per_ava>=0.01
        inner join #no_dice_deal nd on i.source_deal_header_id=nd.source_deal_header_id
        inner join (select distinct * from #map_n_curve) map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
           and map.h_book_map_id is null 
           and map.i_book_map_id is null;

SELECT t.* into #perfect_match 
FROM 
( SELECT * from #perfect_match_a UNION ALL SELECT * from #perfect_match_b) t

Answer 3

IS Null is not the only problem.

IMHO, I think UNION all will perform best. Also It will make the script shorter.If you not getting correct result then just debug it.

Just try getting correct result.

(select distinct * from #map_n_curve)

This is 100% wrong insead use "inner join #map_n_curve map"

inner join #no_dice_deal nd

when you are not using any column of this table in ResultSET then better use it using "AND EXISTS clause".

I hv modified your script thinking that it would perform more better.

SELECT * INTO #perfect_match

FROM
(
SELECT h.deal_date h_date,i.deal_date i_date,h.source_deal_header_id source_deal_header_id_h,h.per per_h,i.source_deal_header_id source_deal_header_id_i,i.per per_i,COALESCE(map.fas_book_id,i.fas_book_id,h.fas_book_id,-1) fas_book_id,
     COALESCE(map.eff_test_profile_id,i.eff_test_profile_id,h.eff_test_profile_id,-1)  eff_test_profile_id,h.no_indx,h.deal_id1 h_deal_id,i.deal_id1 i_deal_id
     ,CASE WHEN h.link_effect_date>i.link_effect_date THEN h.link_effect_date ELSE i.link_effect_date END link_effect_date,h.CurveName,h.term_start,h.term_end
 --INTO #perfect_match_b
  FROM #hedge h INNER JOIN #item i ON h.term_start=i.term_start
        AND h.term_end=i.term_end AND h.volume=i.volume AND h.buy_sell=i.buy_sell
        AND h.used=0 AND i.used=0 AND h.no_indx=i.no_indx AND h.no_terms=i.no_terms
        and h.initial_per_ava>=0.01 and i.initial_per_ava>=0.01

        inner join  #map_n_curvE map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
            and h.book_map_id=map.h_book_map_id
            and i.book_map_id=map.i_book_map_id

            WHERE EXISTS(SELECT 1 FROM  #no_dice_deal nd WHERE i.source_deal_header_id=nd.source_deal_header_id)

UNION ALL
SELECT h.deal_date h_date,i.deal_date i_date,h.source_deal_header_id source_deal_header_id_h,h.per per_h,i.source_deal_header_id source_deal_header_id_i,i.per per_i,COALESCE(map.fas_book_id,i.fas_book_id,h.fas_book_id,-1) fas_book_id,
     COALESCE(map.eff_test_profile_id,i.eff_test_profile_id,h.eff_test_profile_id,-1)  eff_test_profile_id,h.no_indx,h.deal_id1 h_deal_id,i.deal_id1 i_deal_id
     ,CASE WHEN h.link_effect_date>i.link_effect_date THEN h.link_effect_date ELSE i.link_effect_date END link_effect_date,h.CurveName,h.term_start,h.term_end
     --INTO #perfect_match_a 
     FROM #hedge h INNER JOIN #item i ON h.term_start=i.term_start
        AND h.term_end=i.term_end AND h.volume=i.volume AND h.buy_sell=i.buy_sell
        AND h.used=0 AND i.used=0 AND h.no_indx=i.no_indx AND h.no_terms=i.no_terms
        and h.initial_per_ava>=0.01 and i.initial_per_ava>=0.01
        --inner join #no_dice_deal nd on i.source_deal_header_id=nd.source_deal_header_id
        inner join  #map_n_curve map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
           and map.h_book_map_id is null 
           and map.i_book_map_id is null
    WHERE EXISTS(SELECT 1 FROM  #no_dice_deal nd WHERE i.source_deal_header_id=nd.source_deal_header_id)
)TBL;