SQL 服务器 2014 : slow stored procedure execution time

Question

我有以下表结构:

AuditUserMethods:

+---------------+---------------+----------+
|  ColumnName   |   DataType    | Nullable |
+---------------+---------------+----------+
| Id            | INT           | NOT NULL |
| CreatedDate   | DATETIME      | NOT NULL |
| ApiMethodName | NVARCHAR(MAX) | NOT NULL |
| Request       | NVARCHAR(MAX) | NOT NULL |
| Result        | NVARCHAR(MAX) | NOT NULL |
| Method_Id     | INT           | NOT NULL |
| User_Id       | INT           | NULL     |
+---------------+---------------+----------+

AuditUserMethodErrorCodes:

+--------------------+----------+----------+
|     ColumnName     | DataType | Nullable |
+--------------------+----------+----------+
| Id                 | INT      | NOT NULL |
| AuditUserMethod_Id | INT      | NOT NULL |
| ErrorCode          | INT      | NOT NULL |
+--------------------+----------+----------+

ID 是两个表中的 PK。存在一对多的关系。一个 AuditUserMethod 可以有多个 AuditUserMethodErrorCodes。因此 FK AuditUserMethod_Id。

AuditUserMethods 表中的 AuditUserMethod_Id 和 CreatedDate 都有两个非聚集索引。

该过程的目的是返回基于过滤器的分页结果集。 @PageSize 确定返回多少行，@PageIndex 确定返回哪个页面。所有其他变量用于过滤。

返回三个结果集。

1. 包含 AuditUserMethods 详细信息
2. 包含 AuditUserMethodErrorCodes 详细信息
3. 包含找到的总行数(即，如果页面大小为 1000，并且有 5000 行符合所有条件，则返回 5000)。

存储过程:

CREATE PROCEDURE [api].[Audit_V1_GetAuditDetails]
(
    @Users XML = NULL,
    @Methods XML = NULL,
    @ErrorCodes XML = NULL,
    @FromDate DATETIME = NULL,
    @ToDate DATETIME = NULL,
    @PageSize INT = 5,
    @PageIndex INT = 0
)
AS
BEGIN
    DECLARE @UserIds            TABLE   (Id INT)
    DECLARE @MethodNames        TABLE   (Name NVARCHAR(256))
    DECLARE @ErrorCodeIds       TABLE   (Id INT)

    DECLARE @FilterUsers        BIT = 0
    DECLARE @FilterMethods      BIT = 0
    DECLARE @FilterErrorCodes   BIT = 0

    INSERT @UserIds
       SELECT
           x.y.value('.', 'int')
       FROM 
           @Users.nodes('Ids/x/@i') AS x (y)

    INSERT @MethodNames
       SELECT
           x.y.value('.', 'NVARCHAR(256)')
       FROM 
           @Methods.nodes('ArrayOfString/string') AS x (y)

    INSERT @ErrorCodeIds
       SELECT
           x.y.value('.', 'int')
       FROM 
           @ErrorCodes.nodes('Ids/x/@i') AS x (y)

    IF EXISTS (SELECT TOP 1 0 FROM @UserIds)
       SET @FilterUsers = 1

    IF EXISTS (SELECT TOP 1 0 FROM @MethodNames)
       SET @FilterMethods = 1

    IF EXISTS (SELECT TOP 1 0 FROM @ErrorCodeIds)
       SET @FilterErrorCodes = 1

    DECLARE @StartRow INT = @PageIndex * @Pagesize

    DECLARE @PageDataResults TABLE (Id INT,
                                    CreatedDate DATETIME,
                                    ApiMethodName NVARCHAR(256), 
                                    Request NVARCHAR(MAX),
                                    Result NVARCHAR(MAX),
                                    MethodId INT,
                                    UserId INT,
                                    TotalRows INT);

    WITH PageData AS
    (
        SELECT
            id AS id
            , createddate AS createddate
            , apimethodname AS apimethodname
            , request AS request
            , result AS result
            , method_id AS method_id
            , user_id AS user_id
            , ROW_NUMBER() OVER (ORDER BY createddate DESC, id DESC) AS row_number
            , COUNT(*) OVER() as TotalRows
        FROM 
            dbo.AuditUserMethods AS aum
        WHERE 
           (@FromDate IS NULL OR 
            (@FromDate IS NOT NULL AND aum.createddate > @FromDate))
           AND (@ToDate IS NULL OR 
                (@ToDate IS NOT NULL AND aum.createddate < @ToDate))
           AND (@FilterUsers = 0 OR 
                (@FilterUsers = 1 AND aum.user_id IN (SELECT Id FROM @UserIds)))
           AND (@FilterMethods = 0 OR 
                (@FilterMethods = 1 AND aum.ApiMethodName IN (SELECT Name FROM @MethodNames)))
           AND (@FiltererRorCodes = 0 OR 
                    (@FiltererRorCodes = 1 
                     AND EXISTS (SELECT 1
                                 FROM AuditUserMethodErrorCodes e
                                 WHERE e.AuditUserMethod_Id = aum.Id
                                   AND e.ErrorCode IN (SELECT Id FROM @ErrorCodeIds)
                                )
                    )
               )
    )

    INSERT @PageDataResults
        SELECT TOP (@Pagesize)
            PageData.id AS id
            , PageData.createddate AS createddate
            , PageData.apimethodname AS apimethodname
            , PageData.request AS request
            , PageData.result AS result
            , PageData.method_id AS method_id
            , PageData.user_id AS user_id
            , PageData.TotalRows AS totalrows
         FROM 
             PageData
         WHERE 
             PageData.row_number > @StartRow
         ORDER BY 
             PageData.createddate DESC

    SELECT 
        Id, CreatedDate, ApiMethodName, Request, Result, MethodId, UserId
    FROM 
        @PageDataResults

    SELECT 
        aumec.AuditUserMethod_Id, aumec.ErrorCode
    FROM 
        @PageDataResults ps
    INNER JOIN 
        AuditUserMethodErrorCodes aumec ON ps.Id = aumec.AuditUserMethod_Id

    SELECT TOP 1 
        TotalRowsNumberOfReturnedAuditEntries 
    FROM @PageDataResults
END

AuditUserMethods 表包含 500,000 行，AuditUserMethodErrorCodes 包含 67843 行。

我正在使用以下参数执行该过程:

EXEC [api].[Audit_V1_GetAuditDetails]   @Users = N'<Ids><x i="1" /></Ids>'
                                        ,@Methods = NULL
                                        ,@ErrorCodes = N'<Ids />'
                                        ,@FromDate = '2015-02-15 07:18:59.613'
                                        ,@ToDate = '2015-07-02 08:18:59.613'
                                        ,@Pagesize = 5000
                                        ,@PageIndex = 0

存储过程仅用了 2 秒多一点的时间来执行并返回 5000 行。我需要这个存储过程运行得更快，但我不确定如何改进它。

以实际执行计划为准。相对于该批处理，CTE 占了 99%。在 CTE 中，排序占了 95% 的成本:

Answer 1

我将从声明几个表参数类型开始。

CREATE TYPE [api].[IdSet] AS TABLE
(
    [Id] INT NOT NULL
);

和，

CREATE TYPE [api].[StringSet] AS TABLE
(
    [Value] NVARCHAR(256) NOT NULL
);

然后我会更改存储过程的签名以使用它们。

注意我还会将总计数作为输出参数而不是单独的结果集返回。

CREATE PROCEDURE [api].[Audit_V2_GetAuditDetails]
(
    @userIds [api].[IdSet] READONLY,
    @methodNames [api].[StringSet] READONLY,
    @errorCodeIds [api].[IdSet] READONLY,
    @fromDate DATETIME = NULL,
    @toDate DATETIME = NULL,
    @pageSize INT = 5,
    @pageIndex INT = 0,
    @totalCount BIGINT OUTPUT
)

我知道您可能仍需要进行 XML 提取，但如果您在 SP 之外进行提取将有助于查询规划器。

现在，在 SP 中，我不会使用 @PageDataResults 我只会获取页面的 ID。我也不会使用 CTE，这在这种情况下没有帮助。

我将简化查询并运行一次以汇总总计数，然后如果它大于 0，则再次运行相同的查询以仅返回 ID 页面。查询的主体将由服务器内部缓存。

此外，Id' 使用 OFFSET 和 FETCH 扩展对 ORDER BY 进行分页,

我在下面概述了一些逻辑上的简化，

CREATE PROCEDURE [api].[Audit_V2_GetAuditDetails]
    (
        @userIds [api].[IdSet] READONLY,
        @methodNames [api].[StringSet] READONLY,
        @errorCodeIds [api].[IdSet] READONLY,
        @fromDate DATETIME = NULL,
        @toDate DATETIME = NULL,
        @pageSize INT = 5,
        @pageIndex INT = 0,
        @totalCount BIGINT OUTPUT
    )
AS

DECLARE @offset INT = @pageSize * @pageIndex; 
DECLARE @filterUsers BIT = 0;
DECLARE @filterMethods BIT = 0;
DECLARE @filterErrorCodes BIT = 0;

IF EXISTS (SELECT 0 FROM @userIds)
    SET @filterUsers = 1;
IF EXISTS (SELECT 0 FROM @methodNames)
    SET @filterMethods = 1;
IF EXISTS (SELECT 0 FROM @errorCodeIds)
    SET @filterErrorCodes = 1;

SELECT
            @totalCount = COUNT_BIG(*)
     FROM
            [dbo].[AuditUserMethods] [aum]
         LEFT JOIN
            @userIds [U]
                ON [U].[Id] = [aum].[user_id]
         LEFT JOIN
            @methodName [M]
                ON [M].[Value] = [aum].[ApiMethodName]
    WHERE
            (
               @fromDate IS NULL
            OR
               [aum].[createddate] > @fromDate
            )
        AND
            (
               @toDate IS NULL
            OR
               [aum].[createddate] < @toDate
            )
        AND
            (
                @filterUsers = 0
            OR
                [U].[Id] IS NOT NULL
            (
        AND
            (
                @filterMethods = 0
            OR
                [M].[Value] IS NOT NULL
            (
        AND
            (
                @filterErrorCodes = 0
            OR 
                (
                    EXISTS(
                        SELECT
                                    1
                            FROM
                                    [dbo].[AuditUserMethodErrorCodes] [e]
                                JOIN
                                    @errorCodeIds [ec]
                                       ON [ec].[Id] = [e].[ErrorCode]
                            WHERE
                                    [e].[AuditUserMethod_Id] = [aum].[Id])
                );

DECLARE @pageIds [api].[IdSet];

IF @totalCount > 0
INSERT @pageIds
SELECT
            [aum].[id]
     FROM
            [dbo].[AuditUserMethods] [aum]
         LEFT JOIN
            @userIds [U]
                ON [U].[Id] = [aum].[user_id]
         LEFT JOIN
            @methodName [M]
                ON [M].[Value] = [aum].[ApiMethodName]
    WHERE
            (
               @fromDate IS NULL
            OR
               [aum].[createddate] > @fromDate
            )
        AND
            (
               @toDate IS NULL
            OR
               [aum].[createddate] < @toDate
            )
        AND
            (
                @filterUsers = 0
            OR
                [U].[Id] IS NOT NULL
            (
        AND
            (
                @filterMethods = 0
            OR
                [M].[Value] IS NOT NULL
            (
        AND
            (
                @filterErrorCodes = 0
            OR 
                (
                    EXISTS(
                        SELECT
                                    1
                            FROM
                                    [dbo].[AuditUserMethodErrorCodes] [e]
                                JOIN
                                    @errorCodeIds [ec]
                                       ON [ec].[Id] = [e].[ErrorCode]
                            WHERE
                                    [e].[AuditUserMethod_Id] = [aum].[Id])
                )
    ORDER BY
            [aum].[createddate] DESC,
            [aum].[id] DESC
        OFFSET @offset ROWS
        FETCH NEXT @pageSize ROWS ONLY;

SELECT 
            [aum].[Id],
            [aum].[CreatedDate],
            [aum].[ApiMethodName],
            [aum].[Request],
            [aum].[Result],
            [aum].[MethodId],
            [aum].[UserId]
    FROM
            [dbo].[AuditUserMethods] [aum]
    JOIN
            @pageIds [i]
                ON [i].[Id] = [aum].[id] 
ORDER BY
            [aum].[createddate] DESC,
            [aum].[id] DESC;

SELECT 
            [aumec].[AuditUserMethod_Id],
            [aumec].[ErrorCode]
    FROM
            [dbo].[AuditUserMethodErrorCodes] [aumec]
        JOIN
            @pageIds [i]
                ON [i].[Id] = [aumec].[AuditUserMethod_Id];

/* The total count is an output parameter */
RETURN 0;

如果这不足以改善情况，您将需要查看查询计划并考虑哪些索引是最佳的。

警告 所有代码都是即兴编写的，因此，虽然想法是正确的，但语法可能并不完美。