1

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.

  • 3
    I suggest you read this. – SqlWorldWide May 16 '17 at 13:01
  • What index do you think you can use on (select distinct * from #map_n_curve) ? – Mister Magoo May 17 '17 at 7:54
  • I can add distinct operation while creating temp table #map_n_curve and replace the join to #map_n_curve directly.I tried with this too,including creating index on h_book_map_id and i_book_map_id columns of #map_n_curve.However, performance did not improve. – Madhu May 17 '17 at 10:22
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;
  • Thanks Joe for your precious answers.I have tried with all 3 conditions you have mentioned, however performance did not improve, though query returned the same result. table2.col1 IS NULL condition is the main cause of performance degradation.I tried by adding index on table2.col1 , still no gain. – Madhu May 17 '17 at 4:11
  • @Madhu Looks like your comment is missing a link. Please edit more information into your question. The query, relevant table definitions and indexes, and actual execution plans posted on brentozar.com/pastetheplan will be helpful. – Joe Obbish May 17 '17 at 4:20
  • I have edited the question to include temp tables and main query , which is slow due to isnull condition.Execution plan shows hash match cost is 40% and hash match aggregate cost is 50%.Removing distinct in map_n_curve temp table removes hash match aggregate , still performance is same. – Madhu May 17 '17 at 6:30
  • @JoeObbish, isn't it a case of UNION all or LEFt join ? – KumarHarsh May 17 '17 at 8:15
  • Joe Obbish and Kumar Harsha, Thanks for your precious comments , I applied union all for 2 select queries (for table2.col1 null and not null cases),performance was better than isnull condition. – Madhu May 17 '17 at 12:01
0

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
0

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;
  • Thanks kumar for your suggestions,As I have mentioned, I need to optimize the code someone has written long before,and I dont have any business logic idea behind it.So I am trying to optimize it a safe way, without hampering the business logics inside it.I tried using left join as you suggested for #map_n_curve, but it outputs different no of records. – Madhu May 17 '17 at 7:00
  • @Madhu ,With due respect,you are not able to use LEFT join or UNION ALL properly.Also by doing so there is no change in biz. rule.Secondly using UNION ALL or LEFT JOIN is part of optmization. – KumarHarsh May 17 '17 at 8:15
  • #map_n_curve temp table is joined to column h_curve_id,i_curve_id, whereas filter applied in book_map_id columns.So I think left join will give different result because it will include null values for curve_id's. Our concern is to include nulls for book_map_id(in filter condition). – Madhu May 17 '17 at 10:09
  • Also, as the query has select into temp table concept, using union will require another insert statement to temp table or union of 2 temp tables after inserting data.I am not sure whether this process will reduce performance,as steps involved will be more – Madhu May 17 '17 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.