Clever Geek Handbook

SQL Server 2014: Slow Stored Procedure Execution Time

I have the following table structure:

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

is the PK in both tables. There are one, many relationships. AuditUserMethod

may have a lot AuditUserMethodErrorCodes

. Therefore, FK AuditUserMethod_Id

.

There AuditUserMethods

are two nonclustered indexes on the table for AuditUserMethod_Id

and CreatedDate

.

The purpose of the procedure is to return a split result set based on filters. @PageSize

determines the number of rows returned and @PageIndex

determines which page will be returned. All other variables are for filtering.

Three sets of results are returned.

  • Contains detail AuditUserMethods

  • Contains detail AuditUserMethodErrorCodes

  • Contains found rows found (i.e. if the page size was 1000 and there were 5000 rows that matched all the criteria, this will return 5000).

Stored procedure:

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

The table AuditUserMethods

contains 500,000 rows and AuditUserMethodErrorCodes

contains 67,843 rows.

I am performing the procedure with the following parameters:

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

The stored procedure takes just over 2 seconds to execute and return 5000 rows. I need this stored procedure to be much faster and I'm not sure how to improve it.

According to the actual execution plan. It is CTE that takes 99% of the party. In CTE, this is a Grade that takes 95% of the cost:

Actual Execution Plan

+3


source to share


3 answers


I would start by declaring several types of table parameters.

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

and

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

Then I would change the signature of the store routine to use them.

Note I would also return the total as an output rather than as a separate result set.

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
)

I know you still need to do the XML extraction, but it will help the query planner if you do it outside of SP.



Now, in SP, I wouldn't use @PageDataResults

to get only ids for a page. I wouldn't use CTE either, which doesn't help in this scenario.

I would simplify the query and run it once to aggregate the total counter, and then if it is greater than 0, repeat the same query to return only the IDs page. The bulk of the request will be cached internally by the server.

Also, Id 'does swap with extensions OFFSET

and FETCH

before ORDER BY

,

There are a number of logical simplifications which I outline below,

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;

If that doesn't improve the situation, you will need to look at the query plan and consider which indexes will be optimal.

Caveat . All codes are written off the cuff, so while the ideas are correct, the syntax may not be perfect.

+1


source 


(@FromDate IS NULL OR 
            (@FromDate IS NOT NULL AND aum.createddate > @FromDate))

coincides with

(@FromDate IS NULL OR  aum.createddate > @FromDate)


try something like this

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)

    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 NOT EXISTS (SELECT TOP 1 0 FROM @UserIds)
       INSERT INTO @UserIds values (-1)

    IF NOT EXISTS (SELECT TOP 1 0 FROM @MethodNames)
       INSERT INTO @MethodNames values ('empty')

    IF NOT EXISTS (SELECT TOP 1 0 FROM @ErrorCodeIds)
       INSERT INTO @ErrorCodeIds values (-1)

    IF @FromDate is null 
       @FromDate = '1/1/1900'

    IF @ToDate is null 
       @ToDate = '1/1/2079'

    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
        JOIN @UserIds
          ON (aum.user_id = @UserIds.ID OR @UserIds.ID = -1)
         AND aum.createddate > @FromDate
         AND aum.createddate < @ToDate
        JOIN @MethodNames 
          ON aum.ApiMethodName = @MethodNames.Name 
          OR @MethodNames.Name = 'empty'
        JOIN AuditUserMethodErrorCodes e
          on e.AuditUserMethod_Id = aum.Id 
        JOIN @ErrorCodeIds 
          ON e.ErrorCode = @ErrorCodeIds.ID 
          OR @ErrorCodeIds.ID = -1
    )
+1


source


You can start by providing your CTE with some sort of index, which can be done like this: see/**/ for "changed rows":

WITH PageData AS
(
    SELECT
/**/    TOP 100 PERCENT                                                                 
        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)
                        )
                )
        )
/**/ORDER BY 
/**/    PageData.createddate 
/**/    ,PageData.row_number
)

I would also experiment with changing the "order" order on the CTE between createddate and then row_number and then row_number and then create date.

Then you pass the CTE to the next process when it is already in the order it expects. This can speed up the process. ORDER BY requires TOP 100 PERCENT.

0


source






More articles:


All Articles