OK , 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=1

Stats:

(70108 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 '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:

CPU time = 391 ms, elapsed time = 1075 ms

with 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_ID

cross 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

) nci

stats:

(70108 row(s) affected)

Table 'Worktable'. Scan count 70147, logical reads 448162, 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:

CPU time = 828 ms, elapsed time = 1080 ms.