Having a really odd issue with a report for a client.
Running 2008R2, database is smallish, only like 3-4GB. Largest table for OrderDetails is around 4 million rows.
Server is a bit underpowered, but not horrible, and in most cases performs fine.
One query on a velocity report is stumping me.
declare @StartDate date = '2016-10-01',
@EndDate date = '2017-11-01',
@ClientID int = 3282
select p.ProductName, u.UOM, x.TimesOrdered as [Times Ordered], x.TotalQuantity as [Total Qty], x.TotalPrice as [Total Price], x.AverageQuantity as [Avg Qty], x.AveragePrice as [Avg Price]
select od.ProductID, od.UOMID, count(1) as TimesOrdered, sum(od.Quantity) as TotalQuantity, sum(od.Quantity * od.Price) as TotalPrice, avg(od.Quantity) as AverageQuantity, avg(od.Price) as AveragePrice
from Orders o
join OrderDetails od on o.OrderID = od.OrderID
join ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID
where ClientID = @ClientID and o.Active = 1 and o.Status = 1 and od.Active = 1 and o.OrderDate between @StartDate and @EndDate
group by od.ProductID, od.UOMID
join Products p on x.ProductID = p.ProductID
join UnitsOfMeasure u on x.UOMID = u.UOMID
order by p.ProductName, u.UOM
The inner query, executed by itself, which doing most of the heavy lifting, returns in a fraction of a second.
Joined with the outer one, I usually end up killing after about 5 - 6 minutes of runtime.
If I put the inner query into a temp table, and run from there instead of the subquery, again in a fraction of a second.
If i LEAVE the subquery in, and remove the "UnitsOfMeasure" table, AGAIN a fraction of a second.
Units of measure is by a longshot the smallest table of the entire group, not sure where to even start looking on where the issue is.
There are a few indexes with some fragmentation, but not the UOM one.
Any thoughts are appreciated.