Question

我正试图加入在服务器上的多个桌子,并保持快速业绩。

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

说明如何以比上述更好的方式做到这一点。

我已经用真实的表格对指数进行了测试,并通过“条形表>。而不是“条形” a

SELECT TOP 1 StockQuantity 
FROM #tmpRecordsStock 
ORDER BY StockQuantity ASC

Answer 1

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

友情链接