Just wondering if there is any opportunity to make this query run faster. I have many joins from multiple tables and I'm wondering if I can make this better.
I have a group by in there as well so am wondering if I am missing anything. It is from large DB's but it's taking hours.
select csi.ItemIDNumber, csi.UnOMe, csi.SellItemID, csi.ItemBc, ISNULL(pr.PrFlg,'PromoYes') Promo, br.BrandID Brand, cs.AddSte State, csi.ItemIDNumber , csi.UnOMe UOM, csi.SellItemID, csi.ItemBc, sum(f.TLSale) sales, sum(f.TotalLineCost) COGS, sum(f.ItemQS) Quantity, sum(f."1stM") FirstM, sum(f."1stM") B_Mar, sum(f."1stM") T_Mar, sum(f."1stM") T_Mar, csi.ItemCategory, cv.Name , f.ReportSalesID, it.ItemName, it.ItemCategory, it.ItemSubCategory, it.itemclass, it.GList, it.UMlit, it.ItemShortName, it.Clssficatn from dbo.ReportSales F with (nolock) left outer join dbo.DDate dd with (nolock) on f.DDateID = dd.DDateID left outer join dbo.DCSaleItem csi with (nolock) on f.DCSaleID = csi.DCSaleID left outer join dbo.DCurrVenr cv with (nolock) on f.DCurrVeID = cv.DCurrVeID left outer join dbo.DBrand br with (nolock) on f.DBrandID = br.DBrandID left outer join dbo.DCStore cs with (nolock) on f.DCStoreID = cs.DCStoreID left outer join dbo.ReportProF pr with (nolock) on f.DPromotionMID = pr.DimPrFlgID left outer join dbo.dimitem it with (nolock) on csi.ItemIDNumber = it.ItemIDNumber WHERE dd.FYQtr = '2015-1' group by csi.ItemIDNumber, cs.AddSte, csi.ItemCategory, cv.Name, csi.SellItemID, csi.ItemBc, br.BrandID, pr.PrFlg, csi.UnOMe, f.ReportSalesID, it.ItemName, it.ItemCategory, it.ItemSubCategory, it.ItemClass, it.GList, it.UMlit, it.ItemShortName, it.Clssficatn order by BrandID, cv.Name, csi.ItemIDNumber, PrFlg DESC, cs.AddSte, csi.ItemCategory
I've tried to revise the above query to try to break it down into two queries. The first gave me millions of rows and couldn't complete. This is because the group by didn't work right. I decided to write a subquery. I know it's not correct but if you can help me, point me in the right direction, it'll be helpful.
The item name should join after we have found the unique IDs and we have the sums of the sales.
select q1.ItemIDNumber, q1.TLSale, q2.ItemName FROM, ( select csi.ItemIDNumber, sum(f.TLSale) from dbo.ReportSales F with (nolock) left outer join dbo.DDate dd with (nolock) on f.DDateID = dd.DDateID left outer join dbo.DCSaleItem csi with (nolock) on f.DCSaleID = csi.DCSaleID group by csi.ItemIDNumber) q1 LEFT OUTER JOIN ( select * FROM dbo.ditem it ) As q2 ON q1.ItemIDNumber = q2.ItemIDNumber WHERE dep.dd.date >='2013-01-29 00:00:00.000' and dd.dep.Date <= '2013-01-30 00:00:00.000'
Next Edit, What you suggest works somewhat but have created the following that has a unique ID then attaches the item after this:
Well that's close, here is what I got working so
SELECT dep.itemidnumber, dep.tlsale, app.itemname, app.itemname, FROM (SELECTSELECT csi.itemidnumber, Sum(f.tlsale) FROM dbo.reportsales F WITH (nolock) LEFT OUTER JOIN dbo.ddate dd WITH (nolock) ON f.ddateid = dd.ddateid WHERE ( dat.date >='2013-01-29 00:00:00.000' AND dat.date <= '2013-01-30 00:00:00.000') GROUP BY csi.itemidnumber ) AS dep INNER JOIN ( SELECT * FROM dbo.dimitem it) AS app ON dep.itemidnumber = app.itemidnumber
The issue I have is I also wish to add a company name that is not identified from the ItemIDnumber which is from dep. I also wish to attach left outer join dbo.DCurrVenr cv with (nolock) on f.DCurrVeID = cv.DCurrVeID as well but don't know how to add this to the above which works but cant figure it out as I only want to group by itemID. So basically it will have unique item IDs, then attach item names to itemIDnumbers, then attach vendor names to vendor number in the report table. Any ideas?