Sql-server – make this multiple join query faster

group bysql servert-sql

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?

Best Answer

You need to get some numbers that make sense and build from there

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

the join on DDate is doing nothing and the group by csi.ItemIDNumber makes that left into a regular join

Related Question