带有视图的T-SQL查询中的文字与变量会产生截然不同的查询时间

When I use a literal in a WHERE clause in a query against a view, the result is basically instantaneous. When I use a variable set to that same value, a completely different and very slow query plan takes its place. How is this possible? How can these be vastly different:

当我在对视图的查询中的WHERE子句中使用文字时，结果基本上是即时的。当我使用设置为相同值的变量时，一个完全不同且非常慢的查询计划取而代之。这怎么可能？这些如何大不相同：

DECLARE @a INT = 5

SELECT ... 
WHERE myview.mycol = @a

SELECT ... 
WHERE myview.mycol = 5

Here is the exact query and timing that I am encountering (I can post additional information about the view itself, but I don't know that posting the view definition itself is that useful: it is complex and relies on a dozen other views and tables. I can post it if it's helpful in answering the question.)

这是我遇到的确切查询和时间（我可以发布有关视图本身的其他信息，但我不知道发布视图定义本身是有用的：它很复杂并且依赖于十几个其他视图和表如果它有助于回答这个问题，我可以发布它。）

DECLARE @productdbuid INT = 5

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

---------------------
SET @t1 = GETDATE();

SELECT 
  * 
FROM 
  vwPublishingActions 
WHERE 
  productdbuid = 5 AND storedbuid = 1

SET @t2 = GETDATE();
SELECT DATEDIFF(MILLISECOND,@t1,@t2) time1;

---------------------
SET @t1 = GETDATE();

SELECT 
  * 
FROM 
  vwPublishingActions 
WHERE 
  productdbuid = @productdbuid AND storedbuid = 1

SET @t2 = GETDATE();
SELECT DATEDIFF(MILLISECOND,@t1,@t2) time2;

time1: 13
时间1:13
time2: 2796
时间2：2796

What is causing SQL Server to treat the literal 5 so differently from @productbuid INT = 5?

是什么导致SQL Server以与@productbuid INT = 5不同的方式处理文字5？

Thanks so much for any guidance.

非常感谢任何指导。

UPDATE: I've been asked to include the view definition:

更新：我被要求包含视图定义：

SELECT
    T2.productdbuid, 
    T2.storedbuid, 
    ISNULL(paca.publishactiondbuid, 8) publishingaction,  -- 8 = ERROR_REPORT
    T2.change_product,
    T2.change_price,
    T2.change_stockstatus,
    T2.inventory_belowtrigger,
    T2.ruledbuid ruledbuid
FROM
    (SELECT
         T.productdbuid, 
         T.storedbuid, 
         -- pick first fully matching set of conditions
         (SELECT TOP 1 paca.dbuid
          FROM dbo.z_PublishingActionCalcs paca
          WHERE (paca.storetypedbuid = T.storetypedbuid) 
            AND (paca.publishingcommanddbuid = T.publishcommanddbuid OR paca.publishingcommanddbuid IS NULL) 
            AND (ISNULL(paca.publishingstatusdbuid, 0) = ISNULL(T.publishstatusdbuid, 1007) OR paca.publishingstatusdbuid IS NULL)  -- 1007 = NOTSET
            AND (ISNULL(ABS(paca.change_product),0) = ISNULL(ABS(T.change_product),0) OR paca.change_product IS NULL) 
            AND (ISNULL(ABS(paca.change_price),0) = ISNULL(ABS(T.change_price),0) OR paca.change_price IS NULL) 
            AND (ISNULL(ABS(paca.change_stockstatus),0) = ISNULL(ABS(T.change_stockstatus),0) OR paca.change_stockstatus IS NULL) 
            AND (ISNULL(ABS(paca.inventory_belowtrigger),0) = ISNULL(ABS(T.inventory_belowtrigger),0) OR paca.inventory_belowtrigger IS NULL)
            AND (ISNULL(paca.stockstatusdbuid, 0) = ISNULL(T.stockstatusdbuid, 0) OR paca.stockstatusdbuid IS NULL) 
      ORDER BY paca.sort) ruledbuid,
      ABS(ISNULL(T.change_product,0)) change_product,
      ABS(ISNULL(T.change_price,0)) change_price,
      ABS(ISNULL(T.change_stockstatus,0)) change_stockstatus,
      ABS(ISNULL(T.inventory_belowtrigger,0)) inventory_belowtrigger
  FROM            
      (SELECT
           p.productid, 
           s.storetypedbuid, 
           CASE 
              WHEN pdpcm.publishcommanddbuid <> 4 
                 THEN NULL  -- STOCKSTATUS
                 ELSE pss.stockstatusdbuid
           END product_stockstatus, 
           CASE 
              WHEN pdpcm.publishcommanddbuid <> 5 
                 THEN NULL  -- INVENTORY
                 ELSE itr.inventory_belowtrigger
           END inventory_belowtrigger, 
           p.dbuid productdbuid, 
           s.dbuid storedbuid, 
           pdpc.change_product, 
           pdpc.change_price, 
           pdpc.change_stockstatus, 
           pdpcm.publishcommanddbuid,
           pdps.publishstatusdbuid,
           pss.stockstatusdbuid
       FROM
           dbo.ProductDetailsPublishingCommands pdpcm
       INNER JOIN 
           dbo.Stores s ON s.dbuid = pdpcm.storedbuid 
       INNER JOIN 
           dbo.Products p ON pdpcm.productdbuid = p.dbuid 
       INNER JOIN 
           dbo.StoreTypeSet st ON st.dbuid = s.storetypedbuid 
       LEFT JOIN 
           dbo.vwPublishingChanges pdpc ON pdpc.productdbuid = p.dbuid 
                                        AND pdpc.storedbuid = s.dbuid
       LEFT JOIN 
           dbo.ProductDetailsPublishingStatuses pdps ON pdps.productdbuid = p.dbuid 
                                                     AND pdps.storedbuid = s.dbuid
       LEFT JOIN 
           dbo.vwProductStockStatus pss ON pss.productdbuid = p.dbuid
       LEFT JOIN 
           dbo.vwProductInventory pri ON pri.productdbuid = p.dbuid
       LEFT JOIN 
           dbo.vwInventoryTriggers itr ON itr.storedbuid = s.dbuid 
                                       AND itr.productdbuid = p.dbuid) T
    ) T2
LEFT JOIN 
    dbo.z_PublishingActionCalcs paca ON T2.ruledbuid = paca.dbuid

1 个解决方案

#1

You would need to look at the execution plan to be sure.

您需要查看执行计划以确定。

When you use a variable mycol = @a SQL Server will create a plan based on average column density for values in mycol.

当您使用变量mycol = @a时，SQL Server将根据mycol中值的平均列密度创建计划。

The mycol = 5 predicate may be significantly above or below the average. When you use a literal SQL Server can lookup the value 5 in the column statistics and potentially get more accurate estimates and thus a more appropriate plan.

mycol = 5谓词可能明显高于或低于平均值。当您使用文字SQL Server时，可以在列统计信息中查找值5，并可能获得更准确的估计值，从而获得更合适的计划。

Additionally using a literal can allow some additional optimisations and simplifications.

另外，使用文字可以允许一些额外的优化和简化。

An example is that a view with a PARTITION BY mycol can have the literal predicate pushed further down than a variable or parameter generally can (except when using OPTION (RECOMPILE)).

一个例子是，带有PARTITION BY mycol的视图可以使文字谓词进一步向下推动，而不是变量或参数通常可以（除非使用OPTION（RECOMPILE））。

Additionally with the literal value available at compilation SQL Server may be able to simplify expressions and use contradiction detection to eliminate some of the work at run time.

此外，在编译时可用的文字值SQL Server可能能够简化表达式并使用矛盾检测来消除运行时的一些工作。

#1