Help tune the query, reduce reusability

Brian collins 141 Reputation points
2021-02-04T14:39:34.977+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,914 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,827 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-05T07:19:39.357+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2021-02-04T16:39:33.98+00:00

    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