查询优化; Table.Column = @Param OR @Param IS NULL

In WHERE clause when using condition like this Table.Column = @Param OR @Param IS NULL It does not use INDEX on Column.

在WHERE子句中使用类似这样的条件时Table.Column = @Param OR @Param IS NULL它不在列上使用INDEX。

Is it true and if so then how to write this kind of query which also use INDEX

是真的,如果是这样,那么如何编写这种也使用INDEX的查询

Query Example

SELECT Col1, Col2 ...
FROM Table
WHERE (Col1 = @col OR @col IS NULL)
AND   (Col2 = @col2 OR @col2 IS NULL)
AND   (Col3 = @col3 OR @col3 IS NULL)

Any help.

4 个解决方案

#1

Unfortunately, the generation of execution plans does not behave as you expect.

不幸的是,执行计划的生成并不像您期望的那样。

For that single query, a single plan is created. In creating that plan the indexes to use are selected, and fixed. It doesn't matter what parameters you provide, the same plan, same indexes, etc, are always used.

对于该单个查询,将创建单个计划。在创建该计划时,将选择并修复要使用的索引。无论您提供什么参数,始终使用相同的计划,相同的索引等都无关紧要。

The otpimiser has tried to find the best plan that can fit all eventuallities, but by the nature of this type of query, there isn't one. A characteristic born out by the plan you have not using an index at all.

otpimiser试图找到适合所有事件的最佳计划,但根据这种类型的查询的性质,没有一个。计划产生的一个特征,你根本没有使用索引。

The solution is to use dynamic SQL. This feels untidy, but if you use parameterised queries with sp_executesql, it can actually be quite stuctured, and very performant.

解决方案是使用动态SQL。这感觉不整齐,但如果你使用sp_executesql参数化查询,它实际上可以非常结构化,并且非常高效。

Here is a link to a very useful article on the subject: dynamic search

以下是关于此主题的非常有用的文章的链接:动态搜索

It's very in depth, but it is a very robust approach to this problem.

这是非常深入的,但它是解决这个问题的一种非常强大的方法。

#2

SELECT Col1, Col2 ...
FROM Table
WHERE EXISTS(
    SELECT Col1, Col2, Col3
    INTERSECT
    SELECT @col, @col2, @col3)

Intuitively, this seems like it should perform very badly, but SQL Server's query optimiser knows how to give INTERSECT special treatment, and internally translates it to (pseudo-SQL)

直觉上,这似乎应该表现得非常糟糕,但SQL Server的查询优化器知道如何给INTERSECT特殊处理,并在内部将其转换为(伪SQL)

SELECT Col1, Col2 ...
FROM Table
WHERE (Col1, Col2, Col3) IS (@col, @col2, @col3)

as you can see in the query plan. If you have indices on these columns, they can and do get used.

正如您在查询计划中看到的那样。如果您在这些列上有索引,它们可以并且确实可以使用。

I originally picked this up from Paul White's Undocumented Query Plans: Equality Comparisons blog post, which may be an interesting further read.

我最初是从Paul White的未记载查询计划:Equality Comparisons博客文章中选择的,这可能是一个有趣的进一步阅读。

#3

-1

Why don't try this:

为什么不试试这个:

SELECT Col1, Col2 ...
FROM Table
WHERE Col1 = IsNull(@col,Col1)
AND Col2 = IsNull(@col2,Col2)
AND Col3 = IsNull(@col3,Col3)

About your question: Your query analyzer say it don't use the index on column1,2,3 ? You made a index for all 3 columns? Then it should use it regardless the other OR IS NULL

关于您的问题:您的查询分析器说它不使用column1,2,3上的索引?你为所有3列做了一个索引?然后它应该使用它,无论其他OR IS NULL

#4

-1

Try to have index on all where clause columns and try to use the more structured query as given below:

尝试在所有where子句列上建立索引,并尝试使用更具结构化的查询,如下所示:

SELECT Col1, Col2 ... 
FROM Table 
WHERE Col1 = **COALESCE**(@col,Col1)
AND Col2 = **COALESCE**(@col2,Col2)
AND Col3 = **COALESCE**(@col3,Col3)

The COALESCE() function returns the first non-null argument so if STATUS is NULL it will return ''.

COALESCE()函数返回第一个非null参数,因此如果STATUS为NULL,它将返回''。

#1