June 5, 2009 at 1:42 pm
I have a small problem with the below query. The first table in the CTE is coming from an unindexed view from the application I use, which is where the entire history of shipments are stored. Unfortunately, I do not have an option on whether or not to use this view.
Actually I do not have a problem with the query as I have written it below. It takes 29 seconds for it to come back with the data that I need. If I remove the WHERE clause all together, it still takes 29 seconds. The problem I have is when I put this in Visual Studio to create a report, I'll change the last line to show
WHERE ItemNumber in (@item)
Once I do that and plug the same numbers I had before in to the multivalue parameter, it seems to go into an infinite scan.
So my questions are, Why is it doing this? Is there any way around this? I am thinking that maybe I could create an index temp table instead of the CTE.
So as I wrap up this post, the query finally finishes, so I guess it is not infinite. It took about 15-20 minutes using
WHERE ItemNumber in (@item)
Any thoughts?
with Ship(Item,M1,M2,M3, YTD) as
(select ItemNumber,
sum(case when datediff(mm, ShipmentDate, getdate())=3 then ShippedQuantity else 0 end) as [M3],
sum(case when datediff(mm, ShipmentDate, getdate())=2 then ShippedQuantity else 0 end) as [M2],
sum(case when datediff(mm, ShipmentDate, getdate())=1 then ShippedQuantity else 0 end) as [M1],
sum(case when datepart(mm, getdate())=1 then
(case when datediff(yy,ShipmentDate, getdate())=1 then ShippedQuantity else 0 end)
else
(case when datediff(yy,ShipmentDate, getdate())=0 then ShippedQuantity else 0 end)
end) as [YTD]
from FS_HistoryShipment
group by ItemNumber)
selecta.ItemNumber,
a.ItemDescription,
a.ItemUM,
b.OnHandQuantity,
b.AllocationQuantity,
b.ATPQuantity,
b.OnOrderQuantity,
M3, M2, M1,
(M1+M2+M3)/3 as [Avg],
YTD,
case when ((M1+M2+M3)/3)=0 then null else b.ATPQuantity/((M1+M2+M3)/3) end as MOH
from Ship inner join FS_Item a on Item=ItemNumber
inner join FS_ItemData b on a.ItemKey=b.ItemKey
where a.ItemNumber in ('10001','10002',........'10100')
June 5, 2009 at 2:59 pm
Well I do not understand why using the multivalue parameter caused the scans it did when hardcoding the same values works just fine, but creating a indexed temp table worked just fine. If anyone does know why it behaves the way it does and would like to explain it, I'm all ears.
Thanks
June 8, 2009 at 2:00 am
From this vantage point its very hard to give you a definitive answer, as the definition of the view,relationships, indexes and quantity of rows are all unknown.
This sort of issue is usually down to the way the optimizer uses statistics.
The long answer is http://technet.microsoft.com/en-gb/library/cc966419.aspx#LiveContent[STATS].
The short answer is that with a local variable at compile time the optimizer guesses how many rows will satisfy the criteria. This is about 20-30% , look at the estimated rows value on the query plan.
When using a hard coded value , using the statistics, it pretty much knows how many rows will be returned.
Make sure all your statistics are upto date, try adding OPTION(RECOMPILE) to the query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply