September 8, 2007 at 8:55 pm
We have an inventory table that has a SKU, qty, and a date of the inventory. I have a query (listed below) that looks at two dates and returns the missing sku's between the dates, but it is understandably slow. I thought I could do this with a self join, but I have not figured it out. Here is the current query:
DECLARE @FirstDate as datetime
DECLARE @EndDate as datetime
SET @FirstDate = '9/5/2007'
SET @EndDate = '9/8/2007'
SELECT Style, invdate, SUM(Qty) AS Ttl
FROM Inv_AU
WHERE (invdate = @EndDate) AND (Style NOT IN
(SELECT DISTINCT Style
FROM Inv_AU AS inv_AU_1
WHERE (invdate = @FirstDate))) AND ([Wid/Col] IN
(SELECT DISTINCT width
FROM ShoeWidth
WHERE (type = 'Adult')))
GROUP BY Style, invdate
Can anyone help?
September 9, 2007 at 12:37 pm
declare @datelast datetime
declare @dateprevious datetime
set @dateprevious='20070101'
set @datelast='20070102'
/*find items that are in
inventorylast but not in inventoryprevious
*/
select inventorylast.SKU
,inventorylast.qty int
,inventorylast.invdate
from inventory inventorylast
where not exists
(select 1 from inventory inventoryprevious
where inventorylast.sku=inventoryprevious.sku
and inventoryprevious.invdate=@dateprevious
)
and inventorylast.invdate=@datelast
?
September 9, 2007 at 1:15 pm
I rewrote the query above as listed below. If I put dates directly in the query, it performs in less that 1 second. If I put the variables back in, the same query takes 24 seconds. What am I missing?
select a.style as style, sum(a.qty) as Ttl, a.invDate
from inv_AU as a left join inv_AU as b on a.style = b.style and (a.invdate = @FirstDate and b.invdate = @EndDate)
where b.style is null and a.invdate = @FirstDate and a.[wid/col] in (select distinct width from ShoeWidth where type='Adult')
group by a.style, a.invdate
order by ttl desc
September 9, 2007 at 3:19 pm
You can compare the execution plans with query analyzer. (Show (estimated) query plan.
I guess with literals the optimizer immediately figures out is it one date (index seek), with variables an range scan is more likely. You can search on the term "parameter sniffing"
*I would also remove the in statement, especially coupled with distinct.
...
where ... and exists (select width from Shoewidth where a.[wid/col]=width and type='Adult')
exists returns as soon as it matches, whilst in needs to scan the table for matching values (perhaps slightly exaggerated)
You might be missing an index (see query plan)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply