0

I have the following query which contain 2 sub query which retrieve a flag if a row exist by conditions. I am wondering if there is a way to optimize the time it runs. My understanding is that decreasing the number of rows at early stage (sub queries) is optimum. So I add a bunch of where condition in the sub query. Then, i will keep only those rows where both condition is record exists. Is there a faster way of doing this?

Note: the only difference between the sub queries is the division_cd. But I could not merge them by doing a AND as there are different rows.

SELECT cc_id_hashed
       , division_cd
       , dpt_code
       , sales_area_cd
       , YEAR([trade_datetime]) as year_nb
       , datepart(week, [trade_datetime]) as week_nb
       , SUM(item_unit_price_aftertax * sales_num) as sales
       , (
        CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM iy_store_sales as t2
                    WHERE division_cd = 01 
                        AND is_returned_trade = 0 
                        AND payment_type = 01000 
                        AND company_cd = 001 
                        AND YEAR(t1.[trade_datetime]) = YEAR(t2.[create_date])
                        AND datepart(week, t1.[trade_datetime]) = datepart(week, t2.[trade_datetime])
                    GROUP BY cc_id_hashed
                        ,YEAR([trade_datetime])
                        ,datepart(week, [trade_datetime])
                    )
                THEN 'Record Exists'
            ELSE 'Record doesn''t Exists'
            END
        ) AS [first_flag]
        , (
        CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM iy_store_sales as t2
                    WHERE division_cd = 03 
                        AND is_returned_trade = 0 
                        AND payment_type = 01000 
                        AND company_cd = 001 
                        AND YEAR(t1.[trade_datetime]) = YEAR(t2.[trade_datetime])
                        AND datepart(week, t1.[trade_datetime]) = datepart(week, t2.[trade_datetime])
                    GROUP BY cc_id_hashed
                        ,YEAR([trade_datetime])
                        ,datepart(week, [trade_datetime])
                    )
                THEN 'Record Exists'
            ELSE 'Record doesn''t Exists'
            END
        ) AS [second_flag]
  FROM iy_store_sales t1
  WHERE division_cd in (01,03) 
        AND is_returned_trade = 0 
        AND payment_type = 01000
        AND company_cd = 001 
  GROUP by cc_id_hashed, division_cd, dpt_code, sales_area_cd, YEAR([trade_datetime]), datepart(week, [trade_datetime])
  ORDER BY YEAR([trade_datetime]), datepart(week, [trade_datetime])
O. Jones
  • 103,626
  • 17
  • 118
  • 172
elchapo
  • 33
  • 5
  • Do you have an index on the iy_store_sales table? If so, can you show it? – David.Warwick Sep 27 '21 at 01:40
  • 2
    An index with the column (is_returned_trade, payment_type, company_cd, division_cd) would help considerably your query, as an initial step of the optimization. Does it exist? Also, please include the execution plan. – The Impaler Sep 27 '21 at 01:42
  • I would also add trade_datetime to the index, and INCLUDE all other fields that are in the SELECT. – David.Warwick Sep 27 '21 at 01:54
  • @David.Warwick unfortunately the database does not belong to my company. I cannot add an index to it. Is there a way just with query changes to increase the speed? – elchapo Sep 27 '21 at 03:22
  • @elchapo, I can't think of a way. I would try to convince the owner to add the index. It would decrease the load on their server as well. – David.Warwick Sep 27 '21 at 03:25
  • Also, you might try using cross apply instead of sub queries. Sometimes they are faster, but if you don't have proper indexes on the table, there isn't much you can do. https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join – David.Warwick Sep 27 '21 at 03:27
  • 1
    @David.Warwick thank you. At least I know that it is not worth searching better way. I will check cross apply. – elchapo Sep 27 '21 at 03:44
  • Good luck with your query. – David.Warwick Sep 27 '21 at 03:55
  • Your subqueries don't need to group (or "distinct'ing") anything since you only test for existence. The optimizer might see through that and ignore the grouping but better to write the code efficiently in the first place. It is also unusual to write a query that will select all (of certain types) rows for all dates. Your ORDER BY clause is logically incomplete. Without knowing the goal and your schema, it is difficult to make useful suggestions. – SMor Sep 27 '21 at 11:22
  • `payment_type = 01000 ` Learn to write constants using the correct format and without implicit conversion (or assumption). Prefacing a number with any zeros does not change the number; that only affects numbers stored as strings. – SMor Sep 27 '21 at 11:27

0 Answers0