I am trying to join multiple table in SQL Server and keep the performance fast.
CREATE TABLE #tmpRecords
(
recordID INT NOT NULL,
filter1ID INT NOT NULL,
filter2ID INT NOT NULL,
filter3ID INT NULL,
filter4ID INT NULL,
filter5 VARCHAR(200) NOT NULL
)
CREATE NONCLUSTERED INDEX ix_tempRecords
ON #tmpRecords (filter1ID, filter2ID, filter3ID, filter4ID, filter5);
INSERT INTO #tmpRecords
VALUES (1, 1, 1, 1, 1, 'test1'),
(1, 2, 1, 1, 1, 'test1'),
(1, 1, 1, 1, 1, 'test2'),
(1, 2, 1, 1, 1, 'test2'),
(2, 1, 1, 1, 1, 'test1'),
(2, 2, 1, 1, 1, 'test1'),
(2, 1, 1, 1, 1, 'test2'),
(2, 2, 1, 1, 1, 'test2'),
(3, 1, 1, 1, 1, 'test1'),
(4, 1, 1, 1, 1, 'test1'),
(5, 1, 1, 1, 1, 'test1'),
(6, 1, 1, 1, 1, 'test1'),
(7, 1, 1, 1, 1, 'test1'),
(8, 2, 1, 1, 1, 'test1'),
(9, 3, 1, 1, 1, 'test1')
CREATE TABLE #tmpRecordsStock
(
filter1ID INT NULL,
filter2ID INT NULL,
filter3ID INT NULL,
filter4ID INT NULL,
filter5 VARCHAR(200) NULL,
StockQuantity INT NOT NULL
)
CREATE NONCLUSTERED INDEX ix_tempRecordsStock
ON #tmpRecordsStock (filter1ID, filter2ID, filter3ID, filter4ID, filter5);
INSERT INTO #tmpRecordsStock
VALUES
(1,null,null,null,null,10),
(2,null,null,null,'test1',15),
(null,null,null,null,'test1',20),
(null,null,null,null,'test2',30),
(null,1,null,null,'test1',40),
(null,2,null,null,'test1',50)
SELECT
T.recordID, T.filter1ID, T.filter2ID, T.filter3ID, T.filter4ID,
MAX(T.StockQuantity) AS StockQuantity
FROM
(SELECT
r.recordID, r.filter1ID, r.filter2ID, r.filter3ID,
r.filter4ID, r.filter5, MIN(s.StockQuantity) AS StockQuantity
FROM
#tmpRecords r
LEFT JOIN
#tmpRecordsStock s ON ((s.filter1ID IS NULL OR s.filter1ID = r.filter1ID)
AND (s.filter2ID IS NULL OR s.filter2ID = r.filter2ID)
AND (s.filter3ID IS NULL OR s.filter3ID = COALESCE(r.filter3ID, 0))
AND (s.filter4ID IS NULL OR s.filter4ID = COALESCE(r.filter4ID, 0))
AND (s.filter5 IS NULL OR s.filter5 = r.filter5))
GROUP BY
r.recordID, r.filter1ID, r.filter2ID, r.filter3ID,
r.filter4ID, r.filter5) T
GROUP BY
T.recordID, T.filter1ID, T.filter2ID, T.filter3ID, T.filter4ID
DROP TABLE #tmpRecords
DROP TABLE #tmpRecordsStock
Trying to figure it out how to do this in a better way than above.
I already tried the indexes with real tables and also pass the #tmpRecordsStock
instead of the min
do a
SELECT TOP 1 StockQuantity
FROM #tmpRecordsStock
ORDER BY StockQuantity ASC
1条答案
按热度按时间7qhs6swi1#
In my experience with derived tables, using the TOP keyword can improve performance.