Just wanted to look at an alternative that appears to perform better:--Sample Tables
CREATE TABLE #Table1 (ID int PRIMARY KEY CLUSTERED, Depth int);
CREATE TABLE #Table2 (ID int, Vegetable varchar(10), Depth int, CONSTRAINT IX_Table2_Depth_INCLUDE_Vegetab PRIMARY KEY CLUSTERED (ID, Depth));
GO
--Sampel data
INSERT INTO #Table1
VALUES (123, 700),(456,500);
INSERT INTO #Table2
VALUES
(123,'Potato',500),
(123,'Carrot',625),
(123,'Onion',800),
(456,'Carrot',325),
(456,'Onion',450),
(456,'Radish',680);
GO
DBCC FREEPROCCACHE;
GO
SET NOCOUNT ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
WITH DepthRanges AS (
SELECT ID, Vegetable,
Depth AS StartDepth,
--To give an upper limit on the last one, NULL returns are the Start Depth + 300
LEAD(Depth,1,Depth+300) OVER (PARTITION BY ID ORDER BY Depth) AS EndDepth
FROM #Table2)
SELECT T1.ID, T1.Depth,
DR.Vegetable
FROM #Table1 T1
JOIN DepthRanges DR ON T1.ID = DR.ID AND T1.Depth BETWEEN DR.StartDepth AND DR.EndDepth;
GO
DBCC FREEPROCCACHE;
GO
SELECT T1.ID, T1.Depth, V.Vegetable
FROM #Table1 AS T1
CROSS APPLY (SELECT MAX(Depth) AS MAX_DEPTH FROM #Table2 AS T2 WHERE T2.ID = T1.ID AND T2.Depth <= T1.Depth) AS MD
CROSS APPLY (SELECT Vegetable FROM #Table2 AS T3 WHERE T3.ID = T1.ID AND T3.Depth = MD.MAX_DEPTH) AS V
ORDER BY T1.ID;
GO
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DROP TABLE #Table1;
DROP TABLE #Table2;
Be sure to remove the DBCC FREEPROCCACHE and SET STATISTICS IO and SET STATISTICS TIME statements before running in production as those are there just to demonstrate the numbers. See the screenshots attached for the Profiler Trace data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)