Performance tuning on filter data from large data set in sql server

Question

I want to fix some performance issue with the following query.

Tbl_A has 65 million rows
Tbl_B has 2 rows

I need to filter Tbl_A with the values of Tbl_b

 SELECT COUNT(*) 
 FROM Tbl_A R1 WITH (NOLOCK) 
      INNER JOIN Tbl_B PBD 
          ON PBD.Indicators = CASE R1.Indicator WHEN 'Y' THEN 'B'
                                                WHEN 'N' THEN 'D'       
                                                WHEN '1' THEN 'B'
                                                WHEN '0' THEN 'D'       
                                                ELSE R1.Indicator       
                             END

I have tried using in & exists instead of join, but I cant find any performance benefits. Please assist.

Probably not performance improvement but query improvement you can combine `WHEN` as `WHEN 'Y' OR '1' THEN 'B'` `WHEN 'N' OR '0' THEN 'D'` — Mahesh, Jun 12 '17 at 09:10
I suppose this is an n:m relation? I can find the same indicator multiple times in tbl_a and multiple times in tbl_b? — Thorsten Kettner, Jun 12 '17 at 09:44

score 3 · Accepted Answer · answered Jun 12 '17 at 09:53

There is not much you can do here. You should have indexes on Tbl_A.Indicator and Tbl_B.Indicators of course.

The only option for a performance increase I see would be a persisted computed column:

ALTER TABLE tbl_a ADD pbd_indicator AS (CASE indicator WHEN 'Y' THEN 'B'
                                                       WHEN 'N' THEN 'D'       
                                                       WHEN '1' THEN 'B'
                                                       WHEN '0' THEN 'D'       
                                                       ELSE Indicator       
                                        END) PERSISTED;

Add the related index:

CREATE INDEX idx_tbl_a_pbd ON tbl_a(pbd_indicator);

And change the query accordingly:

SELECT COUNT(*) 
FROM tbl_a r1
INNER JOIN tbl_b pbd ON pbd.indicators = r1.pbd_indicator;

score 0 · Answer 2 · answered Jun 12 '17 at 09:15

Try this, joining on case statement might take lot of logical reads, instead use case statement in inner query.

SELECT COUNT(*)
FROM (
    SELECT CASE Indicator
            WHEN 'Y'
                THEN 'B'
            WHEN 'N'
                THEN 'D'
            WHEN '1'
                THEN 'B'
            WHEN '0'
                THEN 'D'
            ELSE Indicator
            END AS Indicator
    FROM Tbl_b
    ) R1
INNER JOIN Tbl_a PBD
    ON PBD.Indicators = R1.indicator

Performance tuning on filter data from large data set in sql server

2 Answers2