b5535084 (1/31/2012)
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 o2
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
The 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!
I'm confused. Why can't you use:
SELECT
nco.*,
nci.ORDER_ITEM_ID AS o2
FROM OR_NCCore.dbo.Order_Dim AS nco
JOIN OR_NCCore.dbo.Status_Dim AS s
ON nco.STATUS_ID = S.STATUS_ID
CROSS 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;
...? 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?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi