查询Server存储过程对特定数据的时间比对所有数据所用的时间要长

Question

我在存储过程中有一个查询，它从Server数据库的几个表中检索数据。在我的应用程序中，我用一些参数调用这个存储过程来过滤数据。

奇怪的是，对于"X“周返回的所有数据(大约2600条记录)，检索数据大约需要30秒。但是，如果我为一年中的同一周添加了一个特定的“中心”过滤器(大约1800条记录)，那么获取数据需要大约3分钟的时间！

如果我只运行这个查询，它就能正常工作(所有数据30秒，过滤后的数据大约22秒)。问题是当我通过存储过程运行查询时！

这怎麽可能？为什么过滤后的数据比获取所有数据花费更多的x6时间？为什么我失踪了？我是否正确地编写了存储过程？我怎样才能更有效地做到这一点呢？

我的存储过程代码如下所示：

ALTER PROCEDURE EventMonitoring
    @EventType AS CHAR(1),
    @Year AS INT,
    @Week AS INT,
    @CenterID AS CHAR(2),
    @AreaID AS INT
AS
    DECLARE @SQLCommand AS VARCHAR(MAX)
    DECLARE @MessageError AS VARCHAR(MAX)

    SET @SQLCommand = ' SELECT ....
                   ....
                   ....
                   ....
                   FROM   Event E
                   INNER JOIN ...
                   INNER JOIN ...
                   INNER JOIN ... 
                   WHERE E.EventYear = ' + CAST(@Year AS VARCHAR) +  
                   ' And E.EventWeek = ' + CAST(@Week AS VARCHAR) +
                   ' And E.EventType = ' + CAST(@EventType AS VARCHAR) 

    IF @Centro <> '-' --If application sends - as the parameter, it gets all centers
    BEGIN
        @SQLCommand = @SQLCommand + ' AND E.CenterID = ''' + @CenterID + '''' 
    END

    IF @Area <> 0 --If application sends 0 as the parameter, it gets all  areas
    BEGIN
        @SQLCommand = @SQLCommand + ' AND E.AreaID = ' + CAST(@AreaID AS VARCHAR) 
    END

    SET @SQLCommand = @SQLCommand + 'GROUP BY ....'

    BEGIN TRY
        EXEC(@SQLCommand)
    END TRY
    BEGIN CATCH
        ....
    END CATCH

Answer 1

无论这种情况如何，我敢打赌，可以通过在索引中添加一个额外的列(CenterId)来解决这个问题。

当差异归结为“当我向where子句中添加一个额外的列时，查询将花费更长的时间”，这意味着该额外的列没有包含在查询能够不使用该子句的索引中。

要解决这个问题，请查找查询使用的没有附加子句的现有索引，并将附加列添加到该索引的include()中，或者创建一个新的索引(不过，创建一个全新的索引来再添加一个int列似乎是浪费的)。

使用快速查询查找用于Event的索引，并查看它是否包括CenterId。如果您不知道如何找到它，您基本上需要检查执行计划。如果你仍然找不到它，你可以分享你的执行计划，我们会帮助你找到它。使用粘贴计划@ brentozar.com共享您的执行计划--以下是说明：如何使用粘贴计划。

我只能(模糊地)猜测索引将是什么样子，而不知道Event如何加入查询的其余部分，但是索引看起来可能是这样的，只是缺少了CenterId。

create nonclustered index ix_Event_cover 
  on dbo.Event(EventYear,EventWeek,EventType)
    include (CenterId, AreaID, [JoinColumns], [SelectedColumns])

Answer 2

我在工作中遇到了这样的情况，我们解决了在proc中添加SET ARITHABORT ON参数的问题，因为ADO连接。

编辑

当过程在运行时生成脚本时，sql引擎不会看到真正的参数来为查询构建一个好的计划。这叫参数嗅探。所以试着做这样的事情：

ALTER PROCEDURE EventMonitoring
    @EventType AS CHAR(1),
    @Year AS INT,
    @Week AS INT,
    @CenterID AS CHAR(2),
    @AreaID AS INT
AS   
    DECLARE @MessageError AS VARCHAR(MAX)

    BEGIN TRY

    IF @Centro <> '-' --If application sends - as the parameter, it gets all centers
    BEGIN
        SELECT ....
                   ....
                   ....
                   ....
                   FROM   Event E
                   INNER JOIN ...
                   INNER JOIN ...
                   INNER JOIN ... 
                   WHERE E.EventYear = @Year 
                    And E.EventWeek = @Week  
                   And E.EventType =EventType  AND E.CenterID = @CenterID 
                   GROUP BY ....
    END

    IF @Area <> 0 --If application sends 0 as the parameter, it gets all  areas
    BEGIN

        SELECT ....
                   ....
                   ....
                   ....
                   FROM   Event E
                   INNER JOIN ...
                   INNER JOIN ...
                   INNER JOIN ... 
                   WHERE E.EventYear = @Year 
                    And E.EventWeek = @Week  
                   And E.EventType =EventType  AND E.AreaID = @AreaID 
                   GROUP BY ....
    END





    END TRY
    BEGIN CATCH
        ....
    END CATCH

又一次尝试

在SqlZim指出的文章中快速阅读之后，尝试使用ARITHABORT ON和option(recompile)的最后一张

    ALTER PROCEDURE EventMonitoring
        @EventType AS CHAR(1),
        @Year AS INT,
        @Week AS INT,
        @CenterID AS CHAR(2),
        @AreaID AS INT
    AS
SET ARITHABORT ON
        DECLARE @SQLCommand AS VARCHAR(MAX)
        DECLARE @MessageError AS VARCHAR(MAX)

        SET @SQLCommand = ' SELECT ....
                       ....
                       ....
                       ....
                       FROM   Event E
                       INNER JOIN ...
                       INNER JOIN ...
                       INNER JOIN ... 
                       WHERE E.EventYear = ' + CAST(@Year AS VARCHAR) +  
                       ' And E.EventWeek = ' + CAST(@Week AS VARCHAR) +
                       ' And E.EventType = ' + CAST(@EventType AS VARCHAR) 

        IF @Centro <> '-' --If application sends - as the parameter, it gets all centers
        BEGIN
            @SQLCommand = @SQLCommand + ' AND E.CenterID = ''' + @CenterID + '''' 
        END

        IF @Area <> 0 --If application sends 0 as the parameter, it gets all  areas
        BEGIN
            @SQLCommand = @SQLCommand + ' AND E.AreaID = ' + CAST(@AreaID AS VARCHAR) 
        END

        SET @SQLCommand = @SQLCommand + 'GROUP BY .... OPTION (RECOMPILE)'

        BEGIN TRY
            EXEC(@SQLCommand)
        END TRY
        BEGIN CATCH
            ....
        END CATCH