January 31, 2012 at 10:03 am
peter-757102 (1/31/2012)
I often wished for a ransomization (on/off) setting, that forces deliberately anti-ordered results and expression execution order to quickly identify hidden false assumptions in queries.
Yeah. I doubt we will ever see that though, given the implications for the internal design of the query executor. I have often wondered why TOP is allowed without a scoped ORDER BY though. Probably a backwards-compatibility issue; the dumb stuff usually is 🙂
January 31, 2012 at 10:24 am
I can use that, for sure, but it changes nothing really. Similar I/O counts and CPU time actually goes up. I'm attaching the plans for the two versions:
cross apply 1:
select *
From OR_NCCore.dbo.Order_Dim nco
INNER JOIN or_nccore.dbo.STATUS_DIM S
ON nco.STATUS_ID = S.STATUS_ID
cross apply (
select *, rn=row_number()
over (partition by order_id order by order_item_id asc)
from or_nccore.dbo.ORDER_ITEM_DIM nci
where nco.order_id=nci.order_id
) nci
where rn=1
stats:
(70108 row(s) affected)
Table 'ORDER_ITEM_DIM'. Scan count 70147, logical reads 303216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ORDER_DIM'. Scan count 1, logical reads 4128, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'STATUS_DIM'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 2000 ms, elapsed time = 15948 ms.[/code]
Cross apply 2:
select *
From OR_NCCore.dbo.Order_Dim nco
INNER JOIN or_nccore.dbo.STATUS_DIM S
ON nco.STATUS_ID = S.STATUS_ID
cross apply (
select top 1 order_item_id
from or_nccore.dbo.ORDER_ITEM_DIM nci
where nco.order_id=nci.order_id
order by ORDER_ITEM_ID asc
) nci
Stats:
(70108 row(s) affected)
Table 'ORDER_ITEM_DIM'. Scan count 70147, logical reads 223933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ORDER_DIM'. Scan count 1, logical reads 4128, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'STATUS_DIM'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1281 ms, elapsed time = 7777 ms.
January 31, 2012 at 11:01 am
b5535084 (1/31/2012)
I can use that, for sure, but it changes nothing really. Similar I/O counts and CPU time actually goes up. I'm attaching the plans for the two versions...
Er, from those results:
TOP: CPU time = 1281 ms, elapsed time = 7777 ms, logical reads 223933 (ORDER_ITEM_DIM)
ROW_NUMBER: CPU time = 2000 ms, elapsed time = 15948 ms, logical reads 303216 (ORDER_ITEM_DIM)
TOP is better by every measure. The TOP plan looks better too: there's a (pointless) RID lookup in the ROW_NUMBER plan.
January 31, 2012 at 11:37 am
TOP: CPU time = 1281 ms, elapsed time = 7777 ms, logical reads 223933 (ORDER_ITEM_DIM)
ROW_NUMBER: CPU time = 2000 ms, elapsed time = 15948 ms, logical reads 303216 (ORDER_ITEM_DIM)
TOP is better by every measure. The TOP plan looks better too: there's a (pointless) RID lookup in the ROW_NUMBER plan.
Better? Well OK, a little, but not by an order of magnitude or anything significant, really. Anyway, the original join beats both by miles in I/O. Which surprises me. OTOH It points out the values of digging down into the measures like this.
Good comment about the RID lookup. Something to watch out for...
January 31, 2012 at 11:47 am
b5535084 (1/31/2012)
TOP: CPU time = 1281 ms, elapsed time = 7777 ms, logical reads 223933 (ORDER_ITEM_DIM)
ROW_NUMBER: CPU time = 2000 ms, elapsed time = 15948 ms, logical reads 303216 (ORDER_ITEM_DIM)
TOP is better by every measure. The TOP plan looks better too: there's a (pointless) RID lookup in the ROW_NUMBER plan.
Better? Well OK, a little, but not by an order of magnitude or anything significant, really.
Well I'd say half the elapsed time was pretty significant 😉
Anyway, the original join beats both by miles in I/O. Which surprises me.
There is a reason, but anyway I think we've gone as far with this as is sensible on this thread. Thanks for commenting.
March 21, 2012 at 12:27 pm
Great article about APPLY. Thanks
October 21, 2019 at 3:09 am
Update (SQL2016-SQL2017) about CROSS APPLY and optimization:
CROSS APPLY aren't necessarily optimized with nested loop.
The optimizer now optimizes them like any joins if possible.
CROSS APPLY is the best tool created ever to include business logic, intermediary values in a SQL query.
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply