Help tune the query, reduce reusability

Question

Hello folks,

I need help tuning the following query. I feel that I'm reusing the code again and again. There are 3 tables that differentiate each query (division, sub_division, sub_division_dept) but rest of the tables in each query are almost the same. Also, I would like to avoid UNION ALL which is causing performance issue.
Please assist in providing an efficient way to query this kind of scenario.

Declare @param1 VARCHAR(255)= NULL

--set @@param1 = '212199, 53546'

IF LEN(@div_Track_Id)= 0
BEGIN
Select d.division_name AS 'FieldName', o.ordername, o.orderid, p.productid,p.productname,
 d.division_name, dm.division_map_id
 from order o, product p, division d, divison_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and d.division_name='123ABC'
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )

union all
Select d.sub_division_name AS 'FieldName', o.ordername, o.orderid, p.productid,p.productname,
 d.sub_division_name, dm.sub_division_map_id
 from order o, product p, sub_division d, sub_division_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and d.sub_division_name like '%.M855%'
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )

END

ELSE IF LEN(@sub_div_Track_Id)= 0

BEGIN
 Select d.sub_division_name AS 'FieldName', o.ordername, o.orderid, p.productid,p.productname,
 d.sub_division_name, dm.sub_division_map_id
 from order o, product p, sub_division d, sub_division_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and d.sub_division_name='XYZ666'
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )
union all
Select d.sub_division_dept_name AS 'FieldName',o.ordername, o.orderid, p.productid,p.productname,
 d.sub_division_dept_name, dm.sub_division_dept_map_id
 from order o, product p, sub_division_dept d, sub_division_dept_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and d.sub_division_dept_name like '%.P234%'
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )
END

ELSE

BEGIN
Select d.division_name AS 'FieldName', o.ordername, o.orderid, p.productid,p.productname,
 d.division_name, dm.division_map_id
 from order o, product p, division d, divison_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )

union all

Select d.sub_division_name AS 'FieldName',o.ordername, o.orderid, p.productid,p.productname,
 d.sub_division_name, dm.sub_division_map_id
 from order o, product p, sub_division d, sub_division_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )

 union all

Select d.sub_division_dept_name AS 'FieldName',o.ordername, o.orderid, p.productid,p.productname,
 d.sub_division_dept_name, dm.sub_division_dept_map_id
 from order o, product p, sub_division_dept d, sub_division_dept_map dm
 where o.orderguid = p.productguid
 and d.divisonguid=o.orderguid
 and dm.divisonmapguid = d.divisonguid
 and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )

END

Answer 1

Hi @Brian collins ,

Per my knowledge, it could be a little difficult to tune this query which could avoid multiple UNION ALLs or code repeat for your situation if adding join is not working.

You could try to replace the long join statement with temp tables as below and maybe you could have a simple way while checking:

DROP TABLE IF EXISTS #table1,#table2,#table3  
  
Declare @param1 VARCHAR(255)= NULL    
--set @@param1 = '212199, 53546'  
  
Select d.division_name AS 'FieldName', o.ordername, o.orderid, p.productid,p.productname,  
d.division_name, dm.division_map_id  
into #table1  
from order o, product p, division d, divison_map dm  
where o.orderguid = p.productguid  
and d.divisonguid=o.orderguid  
and dm.divisonmapguid = d.divisonguid  
and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )  
   
Select d.sub_division_name AS 'FieldName', o.ordername, o.orderid, p.productid,p.productname,  
d.sub_division_name, dm.sub_division_map_id  
into #table2  
from order o, product p, sub_division d, sub_division_map dm  
where o.orderguid = p.productguid  
and d.divisonguid=o.orderguid  
and dm.divisonmapguid = d.divisonguid  
and d.sub_division_name like '%.M855%'  
and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )  
    
Select d.sub_division_dept_name AS 'FieldName',o.ordername, o.orderid, p.productid,p.productname,  
d.sub_division_dept_name, dm.sub_division_dept_map_id  
into #table3  
from order o, product p, sub_division_dept d, sub_division_dept_map dm  
where o.orderguid = p.productguid  
and d.divisonguid=o.orderguid  
and  ( @param1 is null or o.order_track_id IN (select trim(value) from string_split(@param1, ',')) )  
       
IF LEN(@div_Track_Id)= 0  
BEGIN  
Select * from #table1  
where division_name='123ABC'   
union all  
Select * from #table2  
where sub_division_name like '%.M855%'  
END  
ELSE IF LEN(@sub_div_Track_Id)= 0  
BEGIN  
Select * from #table2  
where sub_division_name='XYZ666'  
union all  
select * from #table3  
where sub_division_dept_name like '%.P234%'  
END   
ELSE    
BEGIN  
select * from #table1    
union all     
select * from #table2     
union all    
select * from #table3    
END  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Not sure why three SELECT statements within ELSE BEGIN ... END are the same, I use one of them:

DECLARE @param1 VARCHAR(255) = NULL;
--SET @param1 = '212199, 53546';

IF LEN(@div_Track_Id) = 0
BEGIN
    SELECT o.ordername, o.orderid, p.productid, p.productname, d.division_name, dm.division_map_id
    FROM [order] o
    INNER JOIN product p ON o.orderguid = p.productguid
    INNER JOIN division d ON d.divisonguid = o.orderguid
    INNER JOIN divison_map dm ON dm.divisonmapguid = d.divisonguid
    WHERE (@param1 IS NULL OR o.order_track_id IN (SELECT trim(value) FROM STRING_SPLIT(@param1, ',')))
        AND (d.division_name = '123ABC' OR d.sub_division_name LIKE '%.M855%')
END
ELSE IF LEN(@sub_div_Track_Id) = 0
BEGIN
    SELECT o.ordername, o.orderid, p.productid, p.productname, d.division_name, dm.division_map_id
    FROM [order] o
    INNER JOIN product p ON o.orderguid = p.productguid
    INNER JOIN division d ON d.divisonguid = o.orderguid
    INNER JOIN divison_map dm ON dm.divisonmapguid = d.divisonguid
    WHERE (@param1 IS NULL OR o.order_track_id IN (SELECT trim(value) FROM STRING_SPLIT(@param1, ',')))
        AND (d.sub_division_name = 'XYZ666' OR d.sub_division_dept_name LIKE '%.P234%')
END
ELSE
BEGIN
    SELECT o.ordername, o.orderid, p.productid, p.productname, d.division_name, dm.division_map_id
    FROM [order] o
    INNER JOIN product p ON o.orderguid = p.productguid
    INNER JOIN division d ON d.divisonguid = o.orderguid
    INNER JOIN divison_map dm ON dm.divisonmapguid = d.divisonguid
    WHERE (@param1 IS NULL OR o.order_track_id IN (SELECT trim(value) FROM STRING_SPLIT(@param1, ',')))
END