SQL Server Efficient LEFT JOIN on two tables

46qrfjad  于 2023-10-15  发布在  其他
关注(0)|答案(1)|浏览(91)

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
7qhs6swi

7qhs6swi1#

In my experience with derived tables, using the TOP keyword can improve performance.

SELECT
    T.recordID, T.filter1ID, T.filter2ID, T.filter3ID, T.filter4ID, 
    MAX(T.StockQuantity) AS StockQuantity
FROM
    (SELECT TOP (2147483647)
         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

相关问题