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?
