主页 / dba / 问题 / 77129
Accepted
User Smith
Asked: 2014-09-20 13:24:59 +0800 CST

Tsql查询速度慢由于Or inside where子句，导致索引扫描而不是seek

  • 772

我一直在努力使这个查询更有效地工作。

我发现 where 子句中 Ors 的数量是这个查询中最大的问题。此查询位于存储过程中。

我到了我能想到的唯一选择的地步。：

  • 为所有不同的入站参数可能性创建 16 个不同的查询。
  • 创建一个动态 sql 查询，但我不相信这会更快
  • 恢复到字符串 sql，但我不喜欢这样做，因为它们的执行速度不如存储过程。

我相信其他人之前遇到过这个问题。查询性能从大约一秒或更短的时间开始并不可怕，但在某些情况下，它被多次命中导致长达 5 或 6 秒的延迟。

下面查询。：

DECLARE @PERSON_ID AS INT
DECLARE @ITEM_ID AS INT
DECLARE @ITEM_VERSION AS INT
DECLARE @ITEM_SUB_NAME AS VARCHAR(250)
DECLARE @ITEM_SUB_SUB_NAME AS VARCHAR(250)

--DEFAULTS
SET @PERSON_ID = 0
SET @ITEM_ID = 0
SET @ITEM_VERSION = 1
SET @ITEM_SUB_NAME = NULL
SET @ITEM_SUB_SUB_NAME = NULL

    SELECT ID, PERSON_ID, 
           ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
           ISNULL(ITEM_SUB_NAME, '') AS 'ITEM_SUB_NAME',
           ISNULL(ITEM_SUB_SUB_NAME, '') AS 'ITEM_SUB_SUB_NAME',
           ISNULL(ITEM_DATE, '1/1/1900') AS 'ITEM_DATE',
    FROM PERSON_TBL s WITH (NOLOCK)     
    WHERE    ( PERSON_ID = @PERSON_ID OR @PERSON_ID = 0 )
    AND ( ITEM_VERSION = @ITEM_VERSION OR ( @ITEM_VERSION = 1 AND ITEM_VERSION IS NULL ))
    AND ( EMPLOYEE_ID = @EMPLOYEE_ID OR @EMPLOYEE_ID = 0 )
    AND ( ITEM_SUB_NAME = @ITEM_SUB_NAME OR @ITEM_SUB_NAME IS NULL )
    AND ( ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME OR @ITEM_SUB_SUB_NAME IS NULL )
    ORDER BY PERSON_ID, ITEM_SUB_NAME
sql-server t-sql

1 个回答

  1. Best Answer

    这就是我所说的“厨房水槽”存储过程——您需要一个过程来处理用户可能输入的所有可能的搜索条件组合。几乎不可能让 SQL Server 派生出一个对所有这些组合都是最优和高效的单一执行计划——我不在乎你认为什么样的技巧ISNULL可以拉动COALESCE或OR不能拉动它。

    我通常按​​此顺序尝试的解决方案是：

    1. 添加OPTION (RECOMPILE)到查询。是的，您每次都需要支付编译成本，但您将获得正确的计划，因为您提供了所提供的参数及其值。

    2. 使用动态 SQL。现在您将能够根据传递的不同参数缓存多个不同的计划。将此与服务器级设置optimize for ad hoc workloads（来自 Kimberly Tripp 的更多信息here和here）相结合，以便仅完全缓存多次使用的计划版本。例子：

      DECLARE
  @PERSON_ID         INT = 0,
  @ITEM_ID           INT = 0,
  @ITEM_VERSION      INT = 1,
  @ITEM_SUB_NAME     VARCHAR(250),
  @ITEM_SUB_SUB_NAME VARCHAR(250);

DECLARE @sql NVARCHAR(MAX) = N'',

SET @sql = N'SELECT ID, PERSON_ID, 
   ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
   ISNULL(ITEM_SUB_NAME, '''') AS ITEM_SUB_NAME,
   ISNULL(ITEM_SUB_SUB_NAME, '''') AS ITEM_SUB_SUB_NAME,
   ISNULL(ITEM_DATE, ''19000101'') AS ITEM_DATE
FROM dbo.PERSON_TBL AS s WITH (NOLOCK)     
WHERE ITEM_VERSION ' 
+ CASE WHEN @ITEM_VERSION <> 1 THEN 
   N' = @ITEM_VERSION' ELSE N' IS NULL' END
+ CASE WHEN @PERSON_ID <> 0 THEN 
   N' AND PERSON_ID = @PERSON_ID' ELSE N'' END
+ CASE WHEN @EMPLOYEE_ID <> 0 THEN
   N' AND EMPLOYEE_ID = @EMPLOYEE_ID' ELSE N'' END
+ CASE WHEN @ITEM_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_NAME = @ITEM_SUB_NAME' ELSE N'' END
+ CASE WHEN @ITEM_SUB_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME' ELSE N'' END
ORDER BY PERSON_ID, ITEM_SUB_NAME;';

EXEC sys.sp_executesql @sql,
  N'@PERSON_ID INT, @ITEM_ID INT, @ITEM_VERSION INT,
    @ITEM_SUB_NAME VARCHAR(250), @ITEM_SUB_SUB_NAME VARCHAR(250)',
  @PERSON_ID, @ITEM_ID, @ITEM_VERSION, 
  @ITEM_SUB_NAME, @ITEM_SUB_SUB_NAME;

    如果您发现参数嗅探仍然是一个问题，即使您对提供的每种参数组合都有单独的计划（由于基于实际值的基数大不相同），您也可以OPTION (RECOMPILE)在动态 SQL 中添加 - 您再次支付编译成本，但当你有一个更简单的 where 子句时应该会更好。

    有关此模式的一些资源：

    Paul White 也有一篇很棒的文章值得一读：

    作为旁白：

    • 9

相关问题