asked
Merging two views into one
I have 2 views 1 runs in around 2 seconds The other in 2minutes 20seconds I HAVE A TIMEOUT PROBLEM WHEN MS ACCESS TRIES TO BUILD A REPORT OVER IT Essentially, they are the same The first selects only the latest week and sums Packs, Cartridges and Revenue The second selects the YTD and sums Packs, Cartridges and Revenue I have a third view that ties these two views together that takes around 2minutes 30seconds My question is, can these two views be combined into a single view rather than being 2 separate then joined And is over 2 minutes an acceptable period of time, could this be sped up, through better programming. Last Weeks Sales ================ SELECT dbo.CM_DATA.Year, dbo.tblStoreMaster.Planogram, SUM(dbo.CM_DATA.Volume) AS [Last week sales units (packs)], SUM(dbo.CM_DATA.Volume * dbo.tblProducts.Size) AS [Last week sales units (cartridges)], SUM(dbo.CM_DATA.Volume * dbo.GetPriceListByYW(dbo.CM_DATA.UID, dbo.tblCurrencies.Currency, dbo.CM_DATA.Year, dbo.CM_DATA.Week)) AS [Last week sales revenue] FROM dbo.[Current_User] INNER JOIN dbo.tblLive ON dbo.tblLive.Live = dbo.[Current_User].Live INNER JOIN dbo.CM_DATA INNER JOIN dbo.tblStoreMaster ON dbo.tblStoreMaster.StoreNo = dbo.CM_DATA.StoreNo AND dbo.tblStoreMaster.Chain = dbo.CM_DATA.Chain AND dbo.tblStoreMaster.CountryCode = dbo.CM_DATA.Country INNER JOIN dbo.tblProducts ON dbo.tblProducts.[COUNTRY CODE] = dbo.CM_DATA.Country AND dbo.tblProducts.SKU = dbo.CM_DATA.UID ON dbo.tblLive.UID = dbo.CM_DATA.UID AND dbo.tblLive.Chain = dbo.CM_DATA.Chain AND dbo.tblLive.CountryCode = dbo.CM_DATA.Country AND dbo.CM_DATA.Year >= dbo.[Current_User].Year - 1 AND dbo.CM_DATA.Week = dbo.[Current_User].Week INNER JOIN dbo.tblCurrencies ON dbo.tblCurrencies.CountryCode = dbo.[Current_User].CountryCode INNER JOIN dbo.tblPriceList ON dbo.tblPriceList.UID = dbo.CM_DATA.UID AND dbo.tblCurrencies.Currency = dbo.tblPriceList.Currency INNER JOIN dbo.vw_StoreCountByPlanogram ON dbo.tblStoreMaster.Planogram = dbo.vw_StoreCountByPlanogram.Planogram AND dbo.tblStoreMaster.CountryCode = dbo.vw_StoreCountByPlanogram.CountryCode AND dbo.tblStoreMaster.Chain = dbo.vw_StoreCountByPlanogram.Chain GROUP BY dbo.CM_DATA.Year, dbo.tblStoreMaster.Planogram YTD Sales ========= SELECT dbo.CM_DATA.Year, dbo.tblStoreMaster.Planogram, dbo.vw_StoreCountByPlanogram.Nbr_of_Stores AS [#Stores], SUM(dbo.CM_DATA.Volume) AS [Sales units YTD (packs)], SUM(dbo.CM_DATA.Volume / dbo.[Current_User].Week) AS [Average sales YTD (packs)], SUM(dbo.CM_DATA.Volume * dbo.tblProducts.Size) AS [Sales units YTD (cartridges)], SUM(dbo.CM_DATA.Volume * dbo.tblProducts.Size / dbo.[Current_User].Week) AS [Average sales YTD (cartridges)], SUM(dbo.CM_DATA.Volume * dbo.GetPriceListByYW(dbo.CM_DATA.UID, dbo.tblCurrencies.Currency, dbo.CM_DATA.Year, dbo.CM_DATA.Week)) AS [Sales revenue YTD], SUM(dbo.CM_DATA.Volume * dbo.GetPriceListByYW(dbo.CM_DATA.UID, dbo.tblCurrencies.Currency, dbo.CM_DATA.Year, dbo.CM_DATA.Week) / dbo.[Current_User].Week) AS [Average sales revenue YTD], dbo.tblPriceList.Currency FROM dbo.[Current_User] INNER JOIN dbo.tblLive ON dbo.tblLive.Live = dbo.[Current_User].Live INNER JOIN dbo.CM_DATA INNER JOIN dbo.tblStoreMaster ON dbo.tblStoreMaster.StoreNo = dbo.CM_DATA.StoreNo AND dbo.tblStoreMaster.Chain = dbo.CM_DATA.Chain AND dbo.tblStoreMaster.CountryCode = dbo.CM_DATA.Country INNER JOIN dbo.tblProducts ON dbo.tblProducts.[COUNTRY CODE] = dbo.CM_DATA.Country AND dbo.tblProducts.SKU = dbo.CM_DATA.UID ON dbo.tblLive.UID = dbo.CM_DATA.UID AND dbo.tblLive.Chain = dbo.CM_DATA.Chain AND dbo.tblLive.CountryCode = dbo.CM_DATA.Country AND dbo.CM_DATA.Year >= dbo.[Current_User].Year - 1 AND dbo.CM_DATA.Week <= dbo.[Current_User].Week INNER JOIN dbo.tblCurrencies ON dbo.tblCurrencies.CountryCode = dbo.[Current_User].CountryCode INNER JOIN dbo.tblPriceList ON dbo.tblPriceList.UID = dbo.CM_DATA.UID AND dbo.tblCurrencies.Currency = dbo.tblPriceList.Currency INNER JOIN dbo.vw_StoreCountByPlanogram ON dbo.tblStoreMaster.Planogram = dbo.vw_StoreCountByPlanogram.Planogram AND dbo.tblStoreMaster.CountryCode = dbo.vw_StoreCountByPlanogram.CountryCode AND dbo.tblStoreMaster.Chain = dbo.vw_StoreCountByPlanogram.Chain HOW I JOIN THEM =============== SELECT dbo.vw_SnapShotYTD.Planogram, dbo.vw_SnapShotYTD.[#Stores], dbo.vw_SnapShotLW.[Last week sales units (packs)], dbo.vw_SnapShotYTD.[Sales units YTD (packs)], dbo.vw_SnapShotYTD.[Average sales YTD (packs)], dbo.vw_SnapShotLW.[Last week sales units (cartridges)], dbo.vw_SnapShotYTD.[Sales units YTD (cartridges)], dbo.vw_SnapShotYTD.[Average sales YTD (cartridges)], dbo.vw_SnapShotLW.[Last week sales revenue], dbo.vw_SnapShotYTD.[Sales revenue YTD], dbo.vw_SnapShotYTD.[Average sales revenue YTD], dbo.vw_SnapShotYTD.Currency FROM dbo.vw_SnapShotLW INNER JOIN dbo.vw_SnapShotYTD ON dbo.vw_SnapShotLW.Year = dbo.vw_SnapShotYTD.Year AND dbo.vw_SnapShotLW.Planogram = dbo.vw_SnapShotYTD.Planogram
This site requires that you vote on the answers you've been given. For all helpful answers below, indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution to your problem, indicate this by clicking on the check mark next to that one answer.
Joining views and nesting views is a very common code smell that leads to performance problems. The issue is that the optimizer is effectively overwhelmed with choices leading to a very poor execution plan. If you need to combine two different data sets you're much better off writing a new query completely that then joins those queries.
Ok, just to be clear, are you suggesting I use 2 pass-through queries within Access and then join them. Can this be done in a single step or should i write the queries to temporary tables
Are the two views coming from the same SQL Server database? If so, you might want to rewrite them as one single view or stored procedure on the SQL Server database and call that from MS Access. "...And is over 2 minutes an acceptable period of time,..." Well put yourself in the shoes of the person who has to wait for the report to run. I've seen various benchmarks for execution time for running reports. You're really testing the patience of the poor sap who is waiting for the data after 30 seconds. It's a long time. Also consider that while the server is chugging away dealing with your report, those resources can't be put to use on other things. How often is it going to be run? We had a report that was taking 56 seconds to run on average... only it was being run about 2000 times a day! We went to work on it and it now takes about half a second. Makes a big difference. "...Could this be sped up, through better programming?..." Probably, but without more detail about the underlying tables (how many rows, what indexes, how "busy" the tables are, how they're related) it's hard to know where to start.
OK. I've laid out your code a little differently because I couldn't see what was going on in the blocks you've posted. I've put some observations at the bottom: -------- ------------------------------------------------------------------------------------- --------- Last Week's Sales SELECT dat.[Year] ,stm.Planogram ,SUM(dat.Volume) AS [Last week sales units (packs)] ,SUM(dat.Volume * prd.Size) AS [Last week sales units (cartridges)] ,SUM(dat.Volume * dbo.GetPriceListByYW(dat.[UID],ccy.Currency, dat.[Year], dat.[Week]) ) AS [Last week sales revenue] FROM dbo.[Current_User] usr JOIN dbo.tblLive liv ON liv.Live = usr.Live JOIN dbo.CM_DATA dat ON liv.[UID] = dat.[UID] AND liv.Chain = dat.Chain AND liv.CountryCode = dat.Country JOIN dbo.tblStoreMaster stm ON stm.StoreNo = dat.StoreNo AND stm.Chain = dat.Chain AND stm.CountryCode = dat.Country JOIN dbo.tblProducts prd ON prd.[COUNTRY CODE] = dat.Country AND prd.SKU = dat.[UID] AND dat.[Year] >= usr.[Year] - 1 -------- The next line looks to be the only difference between the two queries AND dat.[Week] = usr.[Week] JOIN dbo.tblCurrencies ccy ON ccy.CountryCode = usr.CountryCode JOIN dbo.tblPriceList pcl ON pcl.[UID] = dat.[UID] AND ccy.Currency = pcl.Currency JOIN dbo.vw_StoreCountByPlanogram scbp ON stm.Planogram = scbp.Planogram AND stm.CountryCode = scbp.CountryCode AND stm.Chain = scbp.Chain GROUP BY dat.[Year] ,stm.Planogram ; --------- ------------------------------------------------------------------------------------- --------- YTD Sales SELECT dat.[Year] ,stm.Planogram ,scbp.Nbr_of_Stores AS [#Stores] ,SUM(dat.Volume) AS [Sales units YTD (packs)] ,SUM(dat.Volume / usr.[Week]) AS [Average sales YTD (packs)] ,SUM(dat.Volume * prd.Size) AS [Sales units YTD (cartridges)] ,SUM(dat.Volume * prd.Size / usr.[Week]) AS [Average sales YTD (cartridges)] ,SUM(dat.Volume * dbo.GetPriceListByYW(dat.[UID], ccy.Currency, dat.[Year],dat.[Week])) AS [Sales revenue YTD] ,SUM(dat.Volume * dbo.GetPriceListByYW(dat.[UID], ccy.Currency, dat.[Year], dat.[Week]) / usr.[Week] ) AS [Average sales revenue YTD] ,pcl.Currency FROM dbo.[Current_User] usr JOIN dbo.tblLive liv ON liv.Live = usr.Live JOIN dbo.CM_DATA dat ON liv.[UID] = dat.[UID] AND liv.Chain = dat.Chain AND liv.CountryCode = dat.Country JOIN dbo.tblStoreMaster stm ON stm.StoreNo = dat.StoreNo AND stm.Chain = dat.Chain AND stm.CountryCode = dat.Country JOIN dbo.tblProducts prd ON prd.[COUNTRY CODE] = dat.Country AND prd.SKU = dat.[UID] AND dat.[Year] >= usr.[Year] - 1 AND dat.[Week] ]]]]]]]]
Apologies, I have only just seen this and it is still an ongoing issue for me. I am going to work through your suggestion and will let you know how I get on. many many thanks for your time and effort.
17 People are following this question.
