This Query Takes forever do not know why

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

This Query Takes forever do not know why

Author

Topic

cafmike
Starting Member

2 Posts

Posted - 2011-02-17 : 18:18:00

Here is my query
*********************************************************************

;with qtysold(itemid,qtysold) as
(select itemid,sum(sliretailquantity) from sale_line_item
where txid in (select txid from transactions t where txstartdate >= '2/9/2011' and txstartDate < '2/16/2011')
group by itemid),

previousday (itemid,qtysoldprevious) as
(select itemid, sum(sliretailquantity) from sale_line_item
where txid in (select txid from transactions t where txstartDate >= '2/2/2011' and txstartDate < '2/8/2011')
group by itemid)

select 'itemid' = i.id, i.title
'qtysold' = qtysold.qtysold,
'change' = qtysold.qtysold - previousday.qtysoldprevious
from imitem i
inner join qtysold on i.id = qtysold.itemid
left join previousday on i.id = previousday.itemid
left join inventory on i.id = inventory.itemid

*********************************************************************

Ok i am basically using one cte table to get the number of products sold from my product detail table (sale_line_item) for a certain date range
using another cte table to get the number of products sold from my product detail table (sale_line_item) for a different date range

is i run the the 2 cte queries alone they are fast, less then a second
if i run the whole query and do not join that 2nd cte it is fast and the reads are like 35899
when i join that 2nd cte the reads jump to like 16317830. and the query takes like 60 seconds.
Any ideas on how i can redo or can someone explain why this is happening

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-17 : 22:06:56

Basically you have derived queries that are referencing derived queries joining to other derived queries which have subqueries,
This might work better, untested. Remember the CTE gets re-propagated in each reference. so even though it runs fine standalone, the number of reads increases wildly based on the subsequent select....almost recursively.


;with qtysold as (
select 
itemid  = s.itemid,  
qtysold = SUM(sliretainquantity), 
from  safe_line_item s 	on s.itemid = s.itemid
inner join transactions t on i.txid = t.txid 
Where t.txstartdate >= '20110209' and t.txstartdate <= '20110216'
)

,priorday as (
itemid  = s.itemid,  
previous = SUM(sliretainquantity), 
from  safe_line_item s 	on s.item = s.itemid
	 inner join transactions t on i.txid = t.txid 
Where t.txstartdate >= '20110202' and t.txstartdate <= '2011028'
)

SELECT   i.id,i.title,(qtysold-previous)
FROM imitem i inner join qtysold qs on i.id = qs.itemid
left join previousday pd
	on i.id = pd.itemid
left join inventory inv on i.id = inv.itemid

I would just do it all in one select and avoid the recursion in the CTE inside a CTE., but you can likely attack this several different ways.


SELECT   
i.id,
i.title,
qtysold = SUM(Case WHen txstartdate >= '2/9/2011' and txstartDate < '2/16/2011'
	then sliretailquantity else 0 end),
priorday = SUM(Case WHen txstartdate >= '2/2/2011' and txstartDate < '2/8/2011'
	then sliretailquantity else 0 end),
netchange = SUM(Case WHen txstartdate >= '2/9/2011' and txstartDate < '2/16/2011'
	then sliretailquantity else 0 end) -
			SUM(Case WHen txstartdate >= '2/2/2011' and txstartDate < '2/8/2011'
	then sliretailquantity else 0 end)

FROM imitem i inner join sale_line_item qs on i.id = qs.itemid
left join sale_line_item pd
	on i.id = pd.itemid
left join inventory inv on i.id = inv.itemid

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources