InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:21:12 GMT20http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxGreat article about APPLY. ThanksWed, 21 Mar 2012 12:27:09 GMThai21centuryhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]b5535084 (1/31/2012)[/b][hr][quote]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. [/quote]Better? Well OK, a little, but not by an order of magnitude or anything significant, really.[/quote]Well I'd say half the elapsed time was pretty significant ;-)[quote]Anyway, the original join beats both by miles in I/O. Which surprises me.[/quote]There is a reason, but anyway I think we've gone as far with this as is sensible on this thread. Thanks for commenting.Tue, 31 Jan 2012 11:47:31 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote]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. [/quote]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...Tue, 31 Jan 2012 11:37:43 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]b5535084 (1/31/2012)[/b][hr]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...[/quote]Er, from those results:TOP: CPU time = [b]1281[/b] ms, elapsed time = [b]7777[/b] ms, logical reads 223933 (ORDER_ITEM_DIM)ROW_NUMBER: CPU time = [b]2000[/b] ms, elapsed time = [b]15948[/b] 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.Tue, 31 Jan 2012 11:01:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxI 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:[code="sql"]select * From OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_IDcross 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 ) nciwhere rn=1[/code]stats:[code="plain"][code="plain"](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[/code]]Cross apply 2:[code="sql"]select * From OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_IDcross 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[/code]Stats:[code="plain"](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.[/code]Tue, 31 Jan 2012 10:24:57 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]peter-757102 (1/31/2012)[/b][hr]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.[/quote]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 :-)Tue, 31 Jan 2012 10:03:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]peter-757102 (1/31/2012)[/b][hr]I did check some on the net myself (long live Google)....and this is what I get from it:[/quote]I guess another consideration might be what to use as the clustering key, how wide that would be (which would affect nc indexes too), and whether having to sort new data in order of the cluster would be expensive or not.Tue, 31 Jan 2012 10:02:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]b5535084 (1/31/2012)[/b][hr]OK -- I get that now. So I added:where rn=1 (so it would be included)and removed the top 1, so I now looks like:select nco.*, nci.ORDER_ITEM_ID o2From OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_IDcross 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 ) nciwhere rn=1The inner join still beats it by a wide margin on I/O, even after I added the index you suggested (thanks for that!) It's also a pity that I can't put the where clause inside the subquery for the cross apply, but such is life![/quote]I'm confused. Why can't you use:[code="sql"]SELECT nco.*, nci.ORDER_ITEM_ID AS o2FROM OR_NCCore.dbo.Order_Dim AS ncoJOIN OR_NCCore.dbo.Status_Dim AS s ON nco.STATUS_ID = S.STATUS_IDCROSS APPLY ( SELECT TOP (1) nci.ORDER_ITEM_ID FROM OR_NCCore.dbo.ORDER_ITEM_DIM AS nci WHERE nci.ORDER_ID = nco.ORDER_ID ORDER BY nci.ORDER_ITEM_ID ASC) AS nci;[/code]...? I would expect this to perform well with the index. I'm struggling to understand the need for the ROW_NUMBER. Perhaps an execution plan and statistics results for my query above?Tue, 31 Jan 2012 10:00:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxOK -- I get that now. So I added:where rn=1 (so it would be included)and removed the top 1, so I now looks like:select nco.*, nci.ORDER_ITEM_ID o2From OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_IDcross 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 ) nciwhere rn=1The inner join still beats it by a wide margin on I/O, even after I added the index you suggested (thanks for that!) It's also a pity that I can't put the where clause inside the subquery for the cross apply, but such is life!Tue, 31 Jan 2012 09:52:51 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]SQL Kiwi (1/31/2012)[/b][hr][quote][b]peter-757102 (1/31/2012)[/b][hr]Can you explain (or have a link that does a good job at it), why one would want to go that route over clustered storage in that scenario?[/quote]Not offhand, no. It's not something I have looked at for years - but I am aware that some DW methodologies advocate heaps. It may have something to do with requiring sorted order for no apparent benefit, but we're on the ragged edge of my recollection here.[/quote]I did check some on the net myself (long live Google)....and this is what I get from it:1. Nonclustered indexes on heaps have direct "pointers" to their respective record data in the heap, avoiding a secondary scan trough a clustered index. On large tables that are heavily read and where records are never changed, this could be a big speed factor.2. It is easier to append new data and use many different search patterns using multiple indexes (a clustered index is only optimal on one, the rest becomes expensive).It might add flexibility in segregating inactive and active data too i think. Keeping the active set small, makes things fast ofcourse.Some testing on my side is needed to figure out when to use and when not too and what problems I encounter.Currenly all cubes I worked with are relativly easy managable in size, around 110 GB for the biggest, but with very limited access pattens, so clustering worked fine there. In other scenarios with much smaller datasets I also use clustereing, supported with one index in some cases and that worked fine too. But as usual, as the dataset grows, issues and wrong choices become more aparent.Tue, 31 Jan 2012 09:48:05 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]b5535084 (1/31/2012)[/b][hr]Thanks for the insights! I have a question about your comment about the rn doing nothing though. My hope was that it would force the windowing so that the "top 1" would be in the window. Or, must I reference rn directly to make that happen?[/quote]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.Tue, 31 Jan 2012 09:35:30 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]peter-757102 (1/31/2012)[/b][hr]Can you explain (or have a link that does a good job at it), why one would want to go that route over clustered storage in that scenario?[/quote]Not offhand, no. It's not something I have looked at for years - but I am aware that some DW methodologies advocate heaps. It may have something to do with requiring sorted order for no apparent benefit, but we're on the ragged edge of my recollection here.Tue, 31 Jan 2012 09:32:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]b5535084 (1/31/2012)[/b][hr]Thanks for the insights! I have a question about your comment about the rn doing nothing though. My hope was that it would force the windowing so that the "top 1" would be in the window. Or, must I reference rn directly to make that happen?[/quote]The optimizer removes it entirely because the result of the ROW_NUMBER isn't used for anything. Even if you did reference it, the ORDER BY in the ROW_NUMBER only defines how rows are ordered for numbering. In principle, the TOP could still return rows in some other order - the point is there is no guarantee, so you should not rely on any apparently-reproducible observed behaviour.Tue, 31 Jan 2012 09:31:09 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxThanks for the insights! I have a question about your comment about the rn doing nothing though. My hope was that it would force the windowing so that the "top 1" would be in the window. Or, must I reference rn directly to make that happen?Tue, 31 Jan 2012 09:26:50 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]SQL Kiwi (1/31/2012)[/b][hr][quote][b]peter-757102 (1/31/2012)[/b][hr]I took a look at the plans and what confused me is the apparent lack of clustered indexes (i noticed table scans instead of clustered index scans)![/quote]The table names made me think these are fact and dimension tables in a data warehouse. It is not uncommon to use heaps in that scenario.[/quote]Can you explain (or have a link that does a good job at it), why one would want to go that route over clustered storage in that scenario?The one thing that comes to my mind is reduces storage for simple entities, but when you start to add indexes and query it like it is relational, that small 'advantage' evaporates real fast.Tue, 31 Jan 2012 08:47:53 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]peter-757102 (1/31/2012)[/b][hr]I took a look at the plans and what confused me is the apparent lack of clustered indexes (i noticed table scans instead of clustered index scans)![/quote]The table names made me think these are fact and dimension tables in a data warehouse. It is not uncommon to use heaps in that scenario.Tue, 31 Jan 2012 08:40:13 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxI took a look at the plans and what confused me is the apparent lack of clustered indexes (i noticed table scans instead of clustered index scans)!Could it be that these are heaps with nonclustered indexes applied to them?If so, that would create lots of perfomance issues and limit the optimizer.Each table, except for very few exceptions should have a primarky key, and it should be clustered.Tue, 31 Jan 2012 08:33:58 GMTpeter-757102http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxIf those two queries produce identical results, it is just luck. Your original queries had (rn = 1) ordered by order_item_id ascending for the JOIN option (which would give the row with the lowest order_item_id). The APPLY form used MAX(order_item_id) which will produce the highest order_item_id of course. Your new query just uses TOP (1) without an ORDER BY - so which row you get is down to chance. The ROW_NUMBER in the apply does nothing, by the way. To answer your implied question about the worktable reads - these are related to the on-the-fly index SQL Server is creating for you each time the query runs (the Eager Index Spool in the query plan). This is an indication that you are missing a real index that would be helpful to the APPLY query. Based on the information provided, this is how I would write the APPLY query, and includes the definition of the index you should create to get best performance from it:[code="sql"]SELECT nco.ORDER_ID, nci.ORDER_ITEM_ID FROM OR_NCCore.dbo.Order_Dim AS ncoJOIN OR_NCCore.dbo.Status_Dim AS s ON nco.STATUS_ID = S.STATUS_IDCROSS APPLY ( SELECT TOP (1) nci.ORDER_ITEM_ID FROM OR_NCCore.dbo.ORDER_ITEM_DIM AS nci WHERE nci.ORDER_ID = nco.ORDER_ID ORDER BY nci.ORDER_ITEM_ID ASC) AS nci;-- Optimal index for this query:CREATE INDEX xON OR_NCCore.dbo.ORDER_ITEM_DIM (ORDER_ID, ORDER_ITEM_ID);[/code]Tue, 31 Jan 2012 07:58:29 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxOK , I've attached the two execution plans. Recall that I do a similar query two ways: 1 with join and one with cross apply. The produce identical results. I'm trying to understand why the cross apply version is more expensive and if the latter can be rewritten to do better or if join is the best choice for this application. with join:select nco.ORDER_ID, nci.ORDER_ITEM_ID from OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_ID inner join ( select *, rn=row_number() over (partition by order_id order by order_item_id asc) from or_nccore.dbo.ORDER_ITEM_DIM ) nci on nco.order_id=nci.order_id and rn=1Stats:(70108 row(s) affected)Table 'Worktable'. [b]Scan count 0, logical reads 0[/b], 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 'ORDER_ITEM_DIM'. Scan count 1, logical reads 1417, 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: [b] CPU time = 391 ms[/b], elapsed time = 1075 mswith cross apply:select nco.ORDER_ID, nci.ORDER_ITEM_ID From OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_IDcross apply ( select top 1 *, 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 ) ncistats:(70108 row(s) affected)Table 'Worktable'. [b]Scan count 70147,[/b] [b]logical reads 448162[/b], physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ORDER_ITEM_DIM'. Scan count 1, logical reads 1417, 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: [b]CPU time = 828 ms,[/b] elapsed time = 1080 ms.Tue, 31 Jan 2012 07:33:34 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]b5535084 (1/30/2012)[/b][hr]Hmmm.... replacing Having with Top 1...order by causes it to return Nulls for the max() for some rows -- not what I want. Here are the execution plans (couldn't see how to attach a document -- did I miss it?)[/quote]Yes, when you are adding a reply, there is an 'Edit Attachments' button in the Post Options section. If it is convenient, please also give the CREATE TABLE and CREATE INDEX statements for the tables involved. The more information you can provide, the more comprehensive an answer I'll be able to give you.Mon, 30 Jan 2012 19:14:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxHmmm.... replacing Having with Top 1...order by causes it to return Nulls for the max() for some rows -- not what I want. Here are the execution plans (couldn't see how to attach a document -- did I miss it?):Join: |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([nco].[STATUS_ID])=([S].[STATUS_ID]), RESIDUAL:([OR_NCCORE].[dbo].[ORDER_DIM].[STATUS_ID] as [nco].[STATUS_ID]=[OR_NCCORE].[dbo].[STATUS_DIM].[STATUS_ID] as [S].[STATUS_ID])) |--Sort(ORDER BY:([nco].[STATUS_ID] ASC)) | |--Hash Match(Inner Join, HASH:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID])=([nco].[ORDER_ID]), RESIDUAL:([OR_NCCORE].[dbo].[ORDER_DIM].[ORDER_ID] as [nco].[ORDER_ID]=[OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID])) | |--Filter(WHERE:([Expr1010]=(1))) | | |--Sequence Project(DEFINE:([Expr1010]=row_number)) | | |--Segment | | |--Sort(ORDER BY:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID] ASC, [OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ITEM_ID] ASC)) | | |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM])) | |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_DIM] AS [nco])) |--Sort(ORDER BY:([S].[STATUS_ID] ASC)) |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[STATUS_DIM] AS [S]))Cross Apply: |--Nested Loops(Inner Join, OUTER REFERENCES:([nco].[ORDER_ID])) |--Hash Match(Inner Join, HASH:([S].[STATUS_ID])=([nco].[STATUS_ID]), RESIDUAL:([OR_NCCORE].[dbo].[STATUS_DIM].[STATUS_ID] as [S].[STATUS_ID]=[OR_NCCORE].[dbo].[ORDER_DIM].[STATUS_ID] as [nco].[STATUS_ID])) | |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[STATUS_DIM] AS [S])) | |--Table Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_DIM] AS [nco])) |--Filter(WHERE:([Expr1010] IS NOT NULL)) |--Stream Aggregate(DEFINE:([Expr1010]=MAX([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ITEM_ID]))) |--Index Spool(SEEK:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[ORDER_ID]=[OR_NCCORE].[dbo].[ORDER_DIM].[ORDER_ID] as [nco].[ORDER_ID])) |--Index Scan(OBJECT:([OR_NCCORE].[dbo].[ORDER_ITEM_DIM].[IX_ORDER_ITEM_ID_with_Includes]))Mon, 30 Jan 2012 09:19:31 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxTry TOP (1) ... ORDER BY inside the APPLY instead of the MAX...HAVING. Happy to explain the performance difference if you can attach the actual execution plans for both to your reply.Mon, 30 Jan 2012 08:57:02 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxI've been learning CROSS APPLY lately and found some interesting things. I have a query that can easily be written as a JOIN (Sorry, it seems my indentation is lost here):select *From OR_NCCore.dbo.Order_Dim nco INNER JOIN or_nccore.dbo.STATUS_DIM S ON nco.STATUS_ID = S.STATUS_ID inner join ( select *, rn=row_number() over (partition by order_id order by order_item_id asc) from or_nccore.dbo.ORDER_ITEM_DIM ) nci on nco.order_id=nci.order_id and nci.rn = 1I can also write it as a CROSS APPLY: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 MAX(order_item_id) max_id from or_nccore.dbo.ORDER_ITEM_DIM where nco.order_id=order_id having MAX(order_item_id) is not null ) nciThe first interesting thing is that I have to add the "having" clause in the CROSS APPLY or else it returns null values for non-matching order_ids (but then, that's how aggregates work).The other interesting thing is that there are substantial differences in the CPU time and IO:Join:(70087 row(s) affected)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 '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.Table 'ORDER_DIM'. Scan count 1, logical reads 4124, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ORDER_ITEM_DIM'. Scan count 1, logical reads 3002, 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 = 2766 ms, elapsed time = 23370 ms.Cross Apply:(70087 row(s) affected)Table 'Worktable'. Scan count 70126, logical reads 448634, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ORDER_ITEM_DIM'. Scan count 1, logical reads 1394, physical reads 1, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ORDER_DIM'. Scan count 1, logical reads 4124, 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 = 1625 ms, elapsed time = 9035 ms.So, the CROSS APPLY runs faster but does tons more I/O to Worktable. Why would this be?Mon, 30 Jan 2012 08:49:16 GMTb5535084http://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxVery good article Paul. the explanation is very good and easy understanding.Thanks!Mon, 16 Jan 2012 07:13:59 GMTrfr.ferrarihttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]LadyRuna (1/13/2012)[/b][hr]The only thing not mentioned, or emphasized in this is the performance of using APPLY.For tiny databases / small result sets, this is awesome. For large result sets, it's terrible. The problem is that the APPLY operator applies the function to each row individually. If you watch SQL profiler while you're running your query / stored proc that uses apply, you'll see a call to the function that's applied for each row. Row by row processing is far slower than pulling all of the data in one swell foop in a single query. I've seen reports that use the apply operator that have nearly taken down a server due to the row by row processing. As soon as we axed the apply operator and put in a normal join onto the table (or tables) that it referred to, the report suddenly became zippy despite pulling back a huge data set. Therefore, I suggest you use caution when using APPLY and user defined functions in your code because although non-SQL coders think they're cool reuseable code, in reality they can seriously hurt performance for larger databases.[/quote]This is true, but the blame lies with scalar and multi-statement T-SQL functions that access data, not APPLY. [b]In-line functions with APPLY are highly recommended in part 1 for this reason[/b]. My advice is to be very careful about using T-SQL scalar or multi-statement functions at all - especially if they access data. In general though, APPLY is an enormously powerful tool that every developer should learn to use effectively.Fri, 13 Jan 2012 20:04:59 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxThe only thing not mentioned, or emphasized in this is the performance of using APPL.For tiny databases / small result sets, this is awesome. For large result sets, it's terrible. The problem is that the APPLY operator applies the function to each row individually. If you watch SQL profiler while you're running your query / stored proc that uses apply, you'll see a call to the function that's applied for each row. Row by row processing is far slower than pulling all of the data in one swell foop in a single query. I've seen reports that use the apply operator that have nearly taken down a server due to the row by row processing. As soon as we axed the apply operator and put in a normal join onto the table (or tables) that it referred to, the report suddenly became zippy despite pulling back a huge data set. Therefore, I suggest you use caution when using APPLY and user defined functions in your code because although non-SQL coders think they're cool reuseable code, in reality they can seriously hurt performance for larger databases.Fri, 13 Jan 2012 09:13:29 GMTLadyRunahttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]TheSQLGuru (4/22/2010)[/b][hr]I too would like to see more information on exactly what is required for the optimizer to be able to translate APPLY to a JOIN so it can use something other than NESTED LOOP joins. I have cleaned up some performance messes where devs went hog-wild on APPLIES and got crushed with kajillion-row loops. But I don't use nor know enough about APPLY to be as helpful as a consultant as I should be. I definitely look forward to the third article you mention!![/quote]A kajillion is quite a lot, yes ;-)Thanks for you your comments there - some very interesting ideas!I had not originally planned to write a third part, but perhaps I underestimated the level of interest.Just finishing off another series today, but I will look at this next.[quote]BTW Paul, would you mind if I took this material and made a presentation out of it to give at user group meetings and SQL Saturdays? I would of course give you credit for the work!![/quote]Of course - I would be flattered if you could find such a use for it. If the original articles in Word format would be of use to you, please mail me. Thanks again!PaulThu, 22 Apr 2010 17:56:24 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxI too would like to see more information on exactly what is required for the optimizer to be able to translate APPLY to a JOIN so it can use something other than NESTED LOOP joins. I have cleaned up some performance messes where devs went hog-wild on APPLIES and got crushed with kajillion-row loops. But I don't use nor know enough about APPLY to be as helpful as a consultant as I should be. I definitely look forward to the third article you mention!!BTW Paul, would you mind if I took this material and made a presentation out of it to give at user group meetings and SQL Saturdays? I would of course give you credit for the work!!Thu, 22 Apr 2010 10:03:31 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]Paul White NZ (4/20/2010)[/b][hr]I am considering a Part III, to cover some specific cases where APPLY can produce solutions which are more efficient than any other method...we'll see.Paul[/quote]Wow, thats a great news... APPLY is something i have not applied at all in any of my code and now i think i will start applying it :-DThanks Paul for nice pieces of cake, waiting to eat the third piece soon :hehe:.Great job!!~Edit : Cleared some mess i made in the Quote!Wed, 21 Apr 2010 05:49:41 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]Oleg Netchaev (4/20/2010)[/b][hr]After reading Part I last week I knew that Part II is going to be good, but I did not expect it to be this good![/quote]Thank you very much! I hope you manage to break free from 2000-compatibility mode soon.My thanks also to Tom Thompson, Gianluca, JoseP, Barry, UMG, Lutz, Steve, Jason, ckuroda, Tom Garth, Trey, Wayne, dbowlin, Clement, and Peter for all the kind comments.I am considering a Part III, to cover some specific cases where APPLY can produce solutions which are more efficient than any other method...we'll see.PaulTue, 20 Apr 2010 21:20:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxAfter reading Part I last week I knew that Part II is going to be good, but I did not expect it to be this good! This was easy to understand, yet comprehensive and inviting further digging introduction to APPLY. Too bad that at work I have to work with SQL server 2005 at compat 80 databases, so utilizing it is not yet possible, but the day will come.Excellent work Paul, thank you.OlegTue, 20 Apr 2010 21:06:08 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxNice article, Paul. With this and the previous one I think I'm beginning to understand apply (a feature I haven't yet used, but probably will sometime soon now that you have shown me how it can be used to express things in a nice clean modular fashion).Tue, 20 Apr 2010 20:49:34 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxAlways great stuff from you, Paul.Well done.Tue, 20 Apr 2010 02:42:59 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxThank you Paul!You post it just when I needed to use the CROSS APPLY to process a XML file :-)Tue, 20 Apr 2010 02:00:40 GMTJosephttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxMore great stuff Paul, thanks!Mon, 19 Apr 2010 19:40:35 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxMike and Green Clay:Yes the ORDER BY in the CROSS JOIN example should be as you say - oddly it is only the image in the article that has this typo, the downloadable code is correct.PaulMon, 19 Apr 2010 17:24:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxPaul, Wow, thanks for these two articles they really helped me understand better use of APPLY. Now if I can just remember all of that I think I will use it a lot more. Thanks again!Mon, 19 Apr 2010 15:52:45 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxGreat article series, Paul!It was a pleasure being selected to get a preview and to give some thoughts/comments.Let's see how long it takes until I have referenced those two articles more often then I did Jeffs CrossTab/DynamicCrossTab :-DMon, 19 Apr 2010 15:49:31 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspxExcellent job, Paul. It helped me learn a few things about a command I rarely use.Mon, 19 Apr 2010 11:38:28 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic905448-2669-1.aspx[quote][b]Paul White NZ (4/18/2010)[/b][hr]I would like to express my sincere thanks to the following people, for their help in producing this article:Amanda Lawrence[url=http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567]Jeff Moden MVP[/url][url=http://www.sqlservercentral.com/Forums/UserInfo873801.aspx]Chris Morris[/url][url=http://www.sqlservercentral.com/Forums/UserInfo724183.aspx]Lutz Müller[/url][url=http://www.sqlservercentral.com/Authors/Articles/Jason_Brimhall/124337/]Jason Brimhall[/url]Paul[/quote]You're welcome.It is a good article - and it was a pleasure to help.Mon, 19 Apr 2010 10:46:42 GMTSQLRNNR