主页 / dba / 问题 / 280899
Accepted
ChrisF
Asked: 2020-12-05 03:29:41 +0800 CST

防止 SQL 明显执行昂贵的子查询

  • 772

我有一个形式的存储过程：

CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN

    SELECT  A.Value1,
            B.Value1,
            C.Value1,
            ...
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
END

即使在@SearchString设置为N''查询的情况下调用它也需要一分半钟的时间来执行。如果我删除“可能很昂贵的查询”部分，那么查询几乎会立即返回预期的数据。

从我所读到的内容来看，上述内容不应该评估外部语句ELSE部分中的任何内容，那么为什么它表现得好像它是一样的呢？CASE@SearchStringN''

如果这按预期工作，那么我如何编写代码以强制 SQL 不执行“可能昂贵的查询”部分？

尽管我努力确保根本不调用它们，但我试图理解为什么它们似乎会影响主查询。

sql-server query-performance

2 个回答

  1. 如果你添加OPTION (RECOMPILE)到WHERE子句的最后，它看起来像这样，程序现在运行得更快吗？

    WHERE A.DateValue > '2020-10-01'
    And 1 = CASE @SearchString
        WHEN N'' THEN 1
        ELSE
            CASE @ComparisonType
                WHEN 0 THEN -- potentially expensive query 1 referencing TableC
                WHEN 1 THEN -- potentially expensive query 2 referencing TableC
                WHEN 6 THEN -- potentially expensive query 3 referencing TableC
                WHEN 8 THEN -- potentially expensive query 4 referencing TableC
                WHEN 10 -- potentially expensive query 5 referencing TableC
                ELSE 0
            END
        END
OPTION (RECOMPILE)

    如果是这样，那么您可能会遇到参数嗅探问题，这可能会导致生成错误的执行计划且基数估计不佳。如果你用你的执行计划更新你的帖子，那么我们可以准确地指出这是如何发生的。

    如果是这种情况，这是一篇关于Parameter Sniffing and OPTION (RECOMPILE)的好文章。

    注意我通常只使用 OPTION (RECOMPILE) 进行调试，以快速确认参数嗅探问题并尝试解决底层问题。OPTION (RECOMPILE) 有一些用例，但是由于每次执行查询时都会重新编译查询，因此它是对较小的性能损失的权衡。

    Brent Ozar 有一系列关于参数嗅探问题的精彩博客文章。老实说，单独链接太多了，所以这里是链接主要重要链接的根页面：参数嗅探

    • 2
  2. Best Answer

    首先检查该@ComparisonType值以获得所需的参数值，然后在原始查询中使用该值。

    这样做将允许 SQL Server 只运行每次调用存储过程时所需的查询。

    CREATE PROCEDURE StoredProcedure
    @SearchString NVARCHAR(MAX),
    @ComparisonType INT
AS
BEGIN
DECLARE @result SMALLINT;
IF (@SearchString = '')
BEGIN
   SET   @result = 0;
END
ELSE
    BEGIN
      IF (@ComparisonType = 0)
        BEGIN
           SELECT @result =  A   FROM TableC
        END
      IF (@ComparisonType = 1)
        BEGIN
           SELECT  @result =  B   FROM TableC
        END
      IF (@ComparisonType = 6)
        BEGIN
           SELECT  @result =  C   FROM TableC
        END
      IF (@ComparisonType = 10)
        BEGIN
           SELECT  @result =  D   FROM TableC
        END                                        
      ELSE
      BEGIN
         SET   @result = 0;
      END
    END
    SELECT  A.Value1,
            B.Value1,
            C.Value1
    FROM TableA as A
    INNER JOIN TableB as B on B.Key1 = A.Key1
    INNER JOIN TableC as C on C.Key2 = B.Key2
    WHERE A.DateValue > '2020-10-01'
    And 1 = @result
END
    • 1

相关问题