Skip to Content
0

Performance problem on INNER JOIN with CASE

Oct 19, 2017 at 04:10 AM

64

avatar image

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 ?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Nov 08, 2017 at 11:34 AM
0

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)) 
Share
10 |10000 characters needed characters left characters exceeded