asked
INNER JOIN 2 SELECT Queries - performance issue
I have 2 select queries each takes under 30 seconds to run Query 1 reports every country and retailer I have worked with 13 months ago Query 2 reports every country and retailer I have worked with last month ie May 2016 vs May 2017 with a couple of inner joins to make sure only certain criteria are met I have INNER JOINED them to get only those Countries/Retailers that are in BOTH groups but when I INNER JOIN them the whole SELECT statement takes 15 Minutes Is there a better way to write this? You will see I moved the WHERE IN clause to parameters which reduced the run time from 20 Minutes to 15 Minutes DECLARE @P01 as nvarchar(10) DECLARE @P02 as nvarchar(10) SET @P01 = (select dm.MonthInCalendar from V2_DateMaster dm where dm.date = cast(dateadd(MONTH, -1,GETDATE()) as DATE)) SET @P02 = (select dm.MonthInCalendar from V2_DateMaster dm where dm.date = cast(dateadd(MONTH, -13,GETDATE()) as DATE)) SELECT a.Country, a.Chain FROM ( SELECT DISTINCT cd.country, cd.chain /*Live POS 1 month ago - 31 sec*/ FROM CM_DATA cd LEFT JOIN V2_DateMaster dm ON dm.ISOWeekID = cd.YearWeek and dm.DayInWeek = 6 INNER JOIN [V2_Pilot Retailers] pi ON pi.CountryCode = cd.Country and pi.Chain = cd.Chain and pi.Pilot = 1 and LEFT(pi.Notes, 2) = 'FY' INNER JOIN V2_StoreMaster sm ON sm.CountryCode = cd.Country and sm.Chain = cd.Chain and sm.StoreNo = cd.StoreNo and cd.YearWeek between sm.YYYYWW and sm.YYYYWWEND and sm.Planogram not in ('Z', 'W') INNER JOIN V2_Live lv ON lv.CountryCode = cd.Country and lv.Chain = cd.Chain and lv.Planogram = sm.Grade and lv.UID = LEFT(cd.UID, 10) and cd.YearWeek between lv.YYYYWW and lv.YYYYWWEND --WHERE dm.MonthInCalendar IN (select dm.MonthInCalendar from V2_DateMaster dm where dm.date = cast(dateadd(MONTH, -1,GETDATE()) as DATE)) WHERE dm.MonthInCalendar = @P01 GROUP BY cd.Country, cd.Chain ) a INNER JOIN ( SELECT DISTINCT cd.country, cd.chain /*Live POS 13 months ago - 25 sec*/ FROM CM_DATA cd LEFT JOIN V2_DateMaster dm ON dm.ISOWeekID = cd.YearWeek and dm.DayInWeek = 6 INNER JOIN [V2_Pilot Retailers] pi ON pi.CountryCode = cd.Country and pi.Chain = cd.Chain and pi.Pilot = 1 and LEFT(pi.Notes, 2) = 'FY' INNER JOIN V2_StoreMaster sm ON sm.CountryCode = cd.Country and sm.Chain = cd.Chain and sm.StoreNo = cd.StoreNo and cd.YearWeek between sm.YYYYWW and sm.YYYYWWEND and sm.Planogram not in ('Z', 'W') INNER JOIN V2_Live lv ON lv.CountryCode = cd.Country and lv.Chain = cd.Chain and lv.Planogram = sm.Grade and lv.UID = LEFT(cd.UID, 10) and cd.YearWeek between lv.YYYYWW and lv.YYYYWWEND --WHERE dm.MonthInCalendar IN (select dm.MonthInCalendar from V2_DateMaster dm where dm.date = cast(dateadd(MONTH, -13,GETDATE()) as DATE)) WHERE dm.MonthInCalendar = @P02 ) b ON b.Country = a.Country and b.Chain = a.chain print @P01 print @P02
What's the performance like if you run the two SELECT queries into temporary tables, and then run the outer query against those?
If you can, it would help us to help you if you shared your execution plan and the table DDL (including index definitions)
Hi ThomasRushton running the execution plan highlighted a missing index.. so thanks for that tip
Perhaps this approach will work better SELECT a.Country, a.Chain ,count(*) FROM ( SELECT DISTINCT cd.country, cd.chain,dm.MonthInCalendar /*Live POS 1 month ago - 31 sec*/ FROM CM_DATA cd LEFT JOIN V2_DateMaster dm ON dm.ISOWeekID = cd.YearWeek and dm.DayInWeek = 6 INNER JOIN [V2_Pilot Retailers] pi ON pi.CountryCode = cd.Country and pi.Chain = cd.Chain and pi.Pilot = 1 and LEFT(pi.Notes, 2) = 'FY' INNER JOIN V2_StoreMaster sm ON sm.CountryCode = cd.Country and sm.Chain = cd.Chain and sm.StoreNo = cd.StoreNo and cd.YearWeek between sm.YYYYWW and sm.YYYYWWEND and sm.Planogram not in ('Z', 'W') INNER JOIN V2_Live lv ON lv.CountryCode = cd.Country and lv.Chain = cd.Chain and lv.Planogram = sm.Grade and lv.UID = LEFT(cd.UID, 10) and cd.YearWeek between lv.YYYYWW and lv.YYYYWWEND WHERE dm.MonthInCalendar IN( cast(dateadd(MONTH, -1,GETDATE()) as DATE) ,cast(dateadd(MONTH, -13,GETDATE()) as DATE)))/*instead of an extra two queries agains the time table*/ --GROUP BY cd.Country, cd.Chain /*seems unnecessary*/ ) a group by a.Country, a.Chain having count(*)>1 (sorry it looks a bit of a mess , I can get the code formatted on my work computer...) Zvi
Hi tzvikl This is my attempt at formatting your code but what are you counting? as the count function requires 1 argument and this is empty SELECT a.Country, a.Chain ,count() FROM ( SELECT DISTINCT cd.country, cd.chain, dm.MonthInCalendar /*Live POS 1 month ago - 31 sec*/ FROM CM_DATA cd LEFT JOIN V2_DateMaster dm ON dm.ISOWeekID = cd.YearWeek and dm.DayInWeek = 6 INNER JOIN [V2_Pilot Retailers] pi ON pi.CountryCode = cd.Country and pi.Chain = cd.Chain and pi.Pilot = 1 and LEFT(pi.Notes, 2) = 'FY' INNER JOIN V2_StoreMaster sm ON sm.CountryCode = cd.Country and sm.Chain = cd.Chain and sm.StoreNo = cd.StoreNo and cd.YearWeek between sm.YYYYWW and sm.YYYYWWEND and sm.Planogram not in ('Z', 'W') INNER JOIN V2_Live lv ON lv.CountryCode = cd.Country and lv.Chain = cd.Chain and lv.Planogram = sm.Grade and lv.UID = LEFT(cd.UID, 10) and cd.YearWeek between lv.YYYYWW and lv.YYYYWWEND WHERE dm.MonthInCalendar IN( cast(dateadd(MONTH, -1,GETDATE()) as DATE) ,cast(dateadd(MONTH, -13,GETDATE()) as DATE)) ) a /*instead of an extra two queries agains the time table*/ --GROUP BY cd.Country, cd.Chain /*seems unnecessary*/ ) a group by a.Country, a.Chain having count()>1
Oops, sorry, that's because i copied my script from my SSMS , it didn't copy the ' * '. I'm counting all the rows, essentially what i'm doing is retrieving the distinct combination of country , chain and month, so that if a country + chain had an entry for both May 17 and May 17 it will have 2 rows only, thus by selecting only the rows with a count > 1 you should get only those countries and chains which you were searching for. Hope this all works well for you Cheers Zvi
Hi Zvi Thanks for the clarification When I run your code I get everything from May17 plus additions from May16 (21 Rows) What I need is only those in both tables (14 Rows) When I use the function HAVING COUNT(*) > 1 I get nothing. I needed to amend the code slightly as MonthInYear yields 'May 2017', but your solution returns a Date This wasn't an issue as I had a Date field in V2_DateMaster I could use SELECT a.Country, a.Chain, COUNT(*) as count FROM ( SELECT DISTINCT cd.country, cd.chain /*Live POS 1 month ago - 31 sec*/ FROM CM_DATA cd LEFT JOIN V2_DateMaster dm ON dm.ISOWeekID = cd.YearWeek and dm.DayInWeek = 6 INNER JOIN [V2_Pilot Retailers] pi ON pi.CountryCode = cd.Country and pi.Chain = cd.Chain and pi.Pilot = 1 and LEFT(pi.Notes, 2) = 'FY' INNER JOIN V2_StoreMaster sm ON sm.CountryCode = cd.Country and sm.Chain = cd.Chain and sm.StoreNo = cd.StoreNo and cd.YearWeek between sm.YYYYWW and sm.YYYYWWEND and sm.Planogram not in ('Z', 'W') INNER JOIN V2_Live lv ON lv.CountryCode = cd.Country and lv.Chain = cd.Chain and lv.Planogram = sm.Grade and lv.UID = LEFT(cd.UID, 10) and cd.YearWeek between lv.YYYYWW and lv.YYYYWWEND WHERE dm.Date IN( cast(dateadd(MONTH, -1,GETDATE()) as DATE) ,cast(dateadd(MONTH, -13,GETDATE()) as DATE)) ) a group by a.Country, a.Chain having count(*)>1 If I swap the WHERE CLAUSE around so it reads -13 before -1 like this WHERE dm.Date IN( cast(dateadd(MONTH, -13,GETDATE()) as DATE) ,cast(dateadd(MONTH, -1,GETDATE()) as DATE)) I still get he 21 rows but the Count get now produces the result 1 for every row which is also incorrect. The process takes 1 second which is great but I need to get the formula to work correctly before shouting for joy
Hi Zvi Afraid not, I still get 21 rows and a count of 1 for all I can see what is being attempted. I think that there is an issue with the WHERE clause WHERE dm.Date IN( cast(dateadd(MONTH, -13,GETDATE()) as DATE) ,cast(dateadd(MONTH, -1,GETDATE()) as DATE)) as if both criteria are not being taken into consideration. Kind Regards Martin
19 People are following this question.
