Performance problem on INNER JOIN with CASE

[former_member185511]

Hi Everyone, I have a big performance issue regarding to a query which need to read a filter table for the filters and read the source data table related to that filters. So my filters table looks like this;

FILTER_ID    CUSTOMER COUNTRY DEPARTMENT
       1                  UK      DEP1
       2       CUS1       US      DEP1
       3       CUS1  

and here is my source data table;

   ROW_NO  CUSTOMER COUNTRY DEPARTMENT
      1      CUS1     UK      DEP1
      2      CUS2     UK      DEP1
      3      CUS3     UK      DEP1
      4      CUS1     US      DEP1
      5      CUS1     SG      DEP3
      6      CUS1     UK      DEP3

For each filters on the filter table i need to get rows from source data table. But if the column on FILTER table is EMPTY, we need to read all members for that column existing in the source data table. Lets say for FILTER_ID 1, we need to read all customer for COUNTRY = UK and DEPARTMENT = DEP1 from the source table. This is how result table should look like;

    FILTER_ID   ROW_NO   CUSTOMER   COUNTRY   DEPARTMENT
       1           1       CUS1       UK        DEP1
       1           2       CUS2       UK        DEP1
       1           3       CUS3       UK        DEP1
       2           4       CUS1       US        DEP1
       3           1       CUS1       UK        DEP1
       3           4       CUS1       US        DEP1
       3           5       CUS1       SG        DEP3
       3           6       CUS1       UK        DEP3

I am using conditional join and it is working fine but problem is, it is very SLOW!

 select t1.FILTER_ID, t2.* from FILTER_TABLE as t1 
inner join SOURCE_DATA as t2 on 
CASE WHEN t1.CUSTOMER = '' THEN t2.CUSTOMER ELSE 
t1.CUSTOMER END = t2.CUSTOMER and
CASE WHEN t1.DEPARTMENT = '' THEN t2.DEPARTMENT ELSE 
t1.DEPARTMENT END = t2.DEPARTMENT and
CASE WHEN t1.COUNTRY = '' THEN t2.COUNTRY ELSE 
t1.COUNTRY END = t2.COUNTRY 

FOR 12K rows of filter and 75K of source data it took 5 min but for 32K rows of filter, it took 32 min to run. If i remove the case from inner join it runs only 2 sec, i know it is not correct result but just to compare the huge difference.

Is there a way to optimize this code ?

[Former Member]

Hi Bilen,

You could try rewriting your code to the following, which should give you the same results you are trying to achieve above.

select t1.FILTER_ID, 
   t2.* 
from FILTER_TABLE as t1 inner join SOURCE_DATA as t2 on 
((t1.CUSTOMER = '' AND 
     t2.CUSTOMER = t2.CUSTOMER) OR
 ((t1.CUSTOMER != '' AND 
     t1.CUSTOMER = t2.CUSTOMER)) AND
((t1.DEPARTMENT = '' AND
 t2.DEPARTMENT = t2.DEPARTMENT) OR
 (t1.DEPARTMENT != '' AND
 t1.DEPARTMENT = t2.DEPARTMENT)) AND 
((t1.COUNTRY = '' AND
 t2.COUNTRY = t2.COUNTRY) OR
 (t1.COUNTRY != '' AND
 t1.COUNTRY = t2.COUNTRY))