0

Just wondering if there is any opportunity to make this query any faster. I have many joins from multiple tables and am wondering if I can make this faster.

I have a group by in there as well so am wondering if I am missing anything. It is from large DBs 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 (select 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 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?

  • I think we'll at least need the execution plan posted - save as xml, upload to pastebin.com and provide a link- table and index definitions will be extremely helpful as well – Scott Hodgin Aug 25 '16 at 23:07
  • Hi Scott, I think I realised my issue, for some reason the group by isn't working. In a way I want all these columns but I want to group these by state but it doesn't seem to work. – penfold255 Aug 26 '16 at 0:25
  • I think it may wish to have a subquery where I initially reduce the overall size of the query by grouping csi.ItemIDNumber, state and SUM sales, then I join it afterwards to it.ItemName, it.ItemCategory, it.ItemSubCategory. Any idea how I could write this? – penfold255 Aug 26 '16 at 1:30
  • Rather than a subquery, put it in a temp table. – Erik Darling Aug 26 '16 at 2:12
  • 1
    Limiting the grouping to fewer tables and then use the results as a derived table was my first thought too. But so far you are the only one who can possibly know which tables should be grouped. – Andriy M Aug 26 '16 at 6:00
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.