February 17, 2011 at 4:23 pm
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
*********************************************************************
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
February 17, 2011 at 4:34 pm
We'd need to see the execution plans for the queries to really help you here. If you need some help doing that the link in my sig for index/tuning help will show you how.
I'm not even sure I can hazard a guess at what's gone wrong there offhand without knowing DDL and expected rowcounts.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 4:36 pm
I agree with Craig, but also - why not try doing it all in one SELECT...?
edit: sorry my bad code removed
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 17, 2011 at 4:47 pm
I think this is what I meant to write just now...
SELECT
sli.itemid ,
sli.title,
qtysold = SUM(qtys.qtysold),
change = SUM(qtys.qtysold - qtys.qtysoldprevious)
FROM
sale_line_item AS sli
JOIN
transactions AS trn
ON
trn.txid=sli.txid
CROSS APPLY
(
SELECT
qtysold = CASE WHEN trn.txstartDate >= '2011-02-08' THEN sli.sliretailquantity ELSE 0 END,
qtysoldprevious = CASE WHEN trn.txstartDate < '2011-02-08' THEN sli.sliretailquantity ELSE 0 END
) AS qtys
WHERE
trn.txstartdate >= '2011-02-02' AND trn.txstartDate < '2011-02-16'
GROUP BY
sli.itemid,sli.title
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 18, 2011 at 6:28 am
wow never even heard of a cross apply, let me read up on that and try that out. thanks for the replies. I will attach execution plan if cross apply still slow
February 18, 2011 at 6:30 am
In this case, I am using CROSS APPLY just as a way of creating placeholders for the calculations.
It just makes the SELECT cleaner - but should not have any other effect.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 1, 2011 at 10:31 am
The cross apply is a lot faster thanks. but i am actually using a lot more fields and my group by looks like
GROUP BY
i.id,i.title + ' ' + coalesce(i.subtitle,''),'http://images.xxxx.com/ProductImages/' + I.imagepath + '/' + i.itemphoto1 + '_' + convert(nvarchar(10),i.id) + '_d.jpg',
m.title,imcategory2.description,i.condition,inventory.qtyonhand
Is there a cleaner way of doing this without having to group by all those fields? Here is the full query
;with inventory(itemid,qtyonhand) as
(select itemid,sum(qtyonhand) - sum(qtyproblem) from iminv
group by itemid)
SELECT 'itemid' = i.id ,'title' = (i.title + ' ' + coalesce(i.subtitle,'')),
'itempic' = 'http://images.xxxx.com/ProductImages/' + I.imagepath + '/' + i.itemphoto1 + '_' + convert(nvarchar(10),i.id) + '_d.jpg',
'kittype' = 'no','manufacturer' = m.title,
'category' = imcategory2.description,
'condition' = i.condition,
'qtyonhand' = inventory.qtyonhand,
qtysold = SUM(qtys.qtysold),
change = SUM(qtys.qtysold - qtys.qtysoldprevious)
FROM
sale_line_item AS sli
JOIN transactions AS t ON t.txid=sli.txid
join imitem i on sli.itemid = i.id
left join inventory on i.id = inventory.itemid
left join xmanufacturer m on i.mfrid = m.id
left join imcategory on i.catid = imcategory.id
LEFT JOIN IMCategory IMCategory2 on IMCategory2.id = IMCategory.parentID
CROSS APPLY
(
SELECT
qtysold = CASE WHEN t.txstartDate >= '2/21/2011' and t.txstartdate < '2/28/2011' THEN sli.sliretailquantity ELSE 0 END,
qtysoldprevious = CASE WHEN t.txstartDate > '2/14/2011' and t.txstartdate < '2/21/2011' THEN sli.sliretailquantity ELSE 0 END
) AS qtys
WHERE
t.txstartdate >= '2/14/2011' AND t.txstartDate < '2/28/2011'
and t.txbackorder is null and t.txexchange is null and txsubtype is null and groupleadersliid is null
and kittype is null and isgeneric = 0 and i.id <> 9999
GROUP BY
i.id,i.title + ' ' + coalesce(i.subtitle,''),'http://images.xxxx.com/ProductImages/' + I.imagepath + '/' + i.itemphoto1 + '_' + convert(nvarchar(10),i.id) + '_d.jpg',
m.title,imcategory2.description,i.condition,inventory.qtyonhand
order by qtysold desc
March 2, 2011 at 11:32 am
Michael,
You actually don't have to use the constants from your select statement in the GROUP BY clause. I know it's easy to just copy/paste them into the GROUP BY, but they are not necessary. Only the actual columns used for the grouping are necessary. It can look cleaner if constants and functions are left out.
For instance, here is your GROUP BY without the constants and functions (I hope I don't have a typo here since I didn't test it):
GROUP BY
i.id,i.title, i.subtitle, I.imagepath, i.itemphoto1
, m.title, imcategory2.description, i.condition, inventory.qtyonhand
Todd Fifield
March 2, 2011 at 11:47 am
Michael T2 (2/17/2011)
is i run the the 2 cte queries alone they are fast, less then a second...
I went to the doctor, held my arm over my head in a certain fashion and said, "It hurts like hell when I do this."
Doctor said, "Well, stop doing that." 😛
Having two aggregate queries in CTE's and then trying to join on them means the same thing as if they were views. Separately, they run very fast... together, they must resolve simultaneously and they are more prone to joining at the row level instead of the aggregate level.
Divide'n'Conquer. Dump the results of each query into it's own Temp Table and then join the Temp Tables. I believe you'll find things run much faster that way.
As a side bar, remember that "Set Based" doesn't mean "All in one query." 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply