主页 / dba / 问题 / 81922
Accepted
sabandurna
Asked: 2014-11-06 01:43:27 +0800 CST

使用窗口函数的 SQL Server 查询性能

  • 772

我们编写了一个查询，其中包括 unpivot、partition by 和 order by。
查询是：

SELECT PersonId
    ,SalaryDate
    ,ID
    ,Type
    ,SalaryValue
    ,ROW_NUMBER() OVER (
        PARTITION BY PersonId ORDER BY SalaryValue
        ) AS rn
FROM (
    SELECT lp.PersonId
        ,lp.SalaryDate
        ,lp.Salary1
        ,lp.Salary2
        ,lp.Salary3
        ,lp.ID
    FROM rdd.Salaries AS lp WITH (NOLOCK)
    WHERE lp.SalaryDate > DATEADD(day, - 31, getdate())
    ) AS t
unpivot(SalaryValue FOR Type IN (
            lp.Salary1
            ,lp.Salary2
            ,lp.Salary3
            )) AS UnpivotTable

在此处输入图像描述

查询返回大约 68.000.000 行，执行时间为 20 分钟。
我可以提高查询的性能或有效地重写吗？
partition by 的替代方案是什么？

sql-server sorting

1 个回答

  1. Best Answer

    您可能会发现以下索引和查询重写执行得更好，因为它对每个人进行排序而不是对整个集合进行一次排序，并且行估计更有可能是准确的：

    -- Index
CREATE INDEX IX_Salaries_PersonId_SalaryDate_Inc_ID_Salary1_Salary2_Salary3
ON rdd.Salaries (PersonId, SalaryDate)
INCLUDE (ID, Salary1, Salary2, Salary3);

-- Query
WITH People AS
(
    SELECT DISTINCT
        S.PersonId
    FROM rdd.Salaries AS S
    WHERE 
        S.SalaryDate > DATEADD(DAY, -31, GETDATE())
)
SELECT 
    P.PersonId, 
    CA.SalaryDate, 
    CA.ID, 
    CA.SalaryValue, 
    CA.rn
FROM People AS P
CROSS APPLY
(
    SELECT
        S.SalaryDate, 
        S.ID, 
        V.SalaryValue, 
        rn = ROW_NUMBER() OVER (ORDER BY V.SalaryValue)
    FROM rdd.Salaries AS S
    CROSS APPLY
    (
        SELECT S.Salary1 WHERE S.Salary1 IS NOT NULL
        UNION ALL
        SELECT S.Salary2 WHERE S.Salary2 IS NOT NULL
        UNION ALL
        SELECT S.Salary3 WHERE S.Salary3 IS NOT NULL
    ) AS V (SalaryValue)
    WHERE 
        S.PersonId = P.PersonId
        AND S.SalaryDate > DATEADD(DAY, -31, GETDATE())
) AS CA
ORDER BY
    P.PersonId,
    CA.rn
OPTION (QUERYTRACEON 8649);

    OPTION如果您发现并行查询是自然生成的，或者如果您发现非并行性能足够好，则可以省略该子句。想要的平面形状大致如下：

    计划形状

    • 2

相关问题