代码之家 › 专栏 › 技术社区 › fengd

在WHERE中使用或子句时将使用索引

indexing tsql sql-server sql

fengd · 技术社区 · 16 年前

我用可选参数编写了一个存储过程。

 CREATE PROCEDURE dbo.GetActiveEmployee
   @startTime DATETIME=NULL,
   @endTime   DATETIME=NULL
 AS
   SET NOCOUNT ON

   SELECT columns
   FROM table
   WHERE (@startTime is NULL or table.StartTime >= @startTime) AND
         (@endTIme is NULL or table.EndTime <= @endTime)

我想知道是否会使用starttime和endtime的索引?

6 回复 | 直到 16 年前

Justin 16 年前

是的,它们将被使用(很可能,检查执行计划——但我知道参数的可选性不会有任何区别)

如果您的查询有性能问题,那么这可能是参数嗅探的结果。尝试对存储过程进行以下更改,看看是否有任何不同:

CREATE PROCEDURE dbo.GetActiveEmployee
    @startTime DATETIME=NULL,
    @endTime   DATETIME=NULL
AS
    SET NOCOUNT ON

    DECLARE @startTimeCopy DATETIME
    DECLARE @endTimeCopy DATETIME
    set @startTimeCopy = @startTime
    set @endTimeCopy = @endTime

    SELECT columns
    FROM table
    WHERE (@startTimeCopy is NULL or table.StartTime >= @startTimeCopy) AND
         (@endTimeCopy is NULL or table.EndTime <= @endTimeCopy)

这将禁用参数嗅探(SQL Server使用传递给SP的实际值对其进行优化)-在过去,我已经解决了一些奇怪的性能问题-但我仍然无法满意地解释原因。

您可能要尝试的另一件事是根据参数的空性将查询拆分为几个不同的语句:

IF @startTime is NULL
BEGIN
    IF @endTime IS NULL
        SELECT columns FROM table
    ELSE
        SELECT columns FROM table WHERE table.EndTime <= @endTime
END    
ELSE
    IF @endTime IS NULL
        SELECT columns FROM table WHERE table.StartTime >= @startTime
    ELSE
        SELECT columns FROM table WHERE table.StartTime >= @startTime AND table.EndTime <= @endTime
BEGIN

这很混乱,但如果您遇到问题,可能值得一试——原因是SQL Server只能为每个SQL语句制定一个执行计划,但是您的语句可能返回非常不同的结果集。

例如,如果传入空值和空值,则返回整个表和最理想的执行计划,但是如果传入的日期范围很小,则更可能是行查找最理想的执行计划。

将此查询作为单个语句,SQL Server将被迫在这两个选项之间进行选择,因此在某些情况下,查询计划可能是次优的。但是,通过将查询拆分为多个语句,SQL Server在每种情况下都可以有不同的执行计划。

(您也可以使用 exec 函数/动态SQL以实现相同的功能(如果您愿意)

Kevin Ross 16 年前

在SQL中有一篇关于动态搜索条件的伟大文章。本文中我个人使用的方法是x=@x或@x是空样式,在末尾添加了选项(重新编译)。如果你读了这篇文章,它会解释为什么

http://www.sommarskog.se/dyn-search-2008.html

OMG Ponies 16 年前

是的,根据查询提供的索引 StartTime 和 EndTime 可以使用列。

然而, [variable] IS NULL OR... 使查询不可分析。如果不想使用if语句(因为case是一个表达式,不能用于控制流决策逻辑),那么动态SQL是性能SQL的下一个替代方法。

IF @startTime IS NOT NULL AND @endTime IS NOT NULL
BEGIN

  SELECT columns
    FROM TABLE 
   WHERE starttime >= @startTime
       AND endtime <= @endTime

END
ELSE IF @startTime IS NOT NULL
BEGIN

  SELECT columns
    FROM TABLE 
   WHERE endtime <= @endTime

END    
ELSE IF @endTIme IS NOT NULL
BEGIN

  SELECT columns
    FROM TABLE
   WHERE starttime >= @startTime

END
ELSE
BEGIN

  SELECT columns
     FROM TABLE

END

onedaywhen 16 年前

大概不会。请看一下来自Tony Rogerson SQL Server MVP的博客:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx

你至少应该有这样的想法:你需要用可信的数据进行测试,并检查执行计划。

KM. 16 年前

基于给定参数动态更改搜索是一个复杂的主题,以一种方式对另一种方式进行搜索,即使只有很小的差异,也可能会产生巨大的性能影响。关键是要使用索引,忽略压缩代码,忽略担心重复代码,必须制定一个好的查询执行计划(使用索引)。

阅读本文并考虑所有方法。您的最佳方法将取决于您的参数、数据、模式和实际使用情况:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

上述项目中应用于此查询的部分是 Umachandar's Bag of Tricks ,但它基本上是将参数默认为某个值,以消除使用或的必要性。这将提供最佳的索引使用率和总体性能:

CREATE PROCEDURE dbo.GetActiveEmployee
    @startTime DATETIME=NULL,
    @endTime   DATETIME=NULL
AS
    SET NOCOUNT ON

    DECLARE @startTimeCopy DATETIME
    DECLARE @endTimeCopy DATETIME
    set @startTimeCopy = COALESCE(@startTime,'01/01/1753')
    set @endTimeCopy = COALESCE(@endTime,'12/31/9999')

    SELECT columns
    FROM table
    WHERE table.StartTime >= @startTimeCopy AND table.EndTime <= @endTimeCopy)

-1

Glen Little 16 年前

我认为你不能保证索引会被使用。这将很大程度上取决于表的大小、显示的列、索引的结构和其他因素。

您最好使用SQL Server Management Studio(SSMS)并运行查询,并包含“实际执行计划”。然后您可以研究它,并准确地看到使用了哪些索引。

你经常会对你的发现感到惊讶。

尤其是在 OR 或 IN 在查询中。