如何在没有COALESCE的情况下创建动态WHERE选择

I have found out that my SQL 2008 R2 database is really struggling with COALESCE function if used within search.

我发现,如果在搜索中使用,我的SQL 2008 R2数据库确实在使用COALESCE功能。

CODE:

where 
    i.id_categ = COALESCE(@id_categ, i.id_categ )
    and i.id_brand = COALESCE(@id_brand , i.id_brand )
    and i.id_model = COALESCE(@id_model , i.id_model )
    and i.id_type = COALESCE(@id_karoseria, i.id_type )
    and i.id_fuel = COALESCE(@id_palivo, i.id_fuel )
    and (i.year between @year_from and @year_to)
    and (i.price between @price_from and @price_to)

DYNAMIC variables:

ALTER PROCEDURE [dbo].[spInzeratSelect]
     @id_categ int = null,
     @id_brand int = null,
     @id_model int = null,
     @id_fuel int = null,
     @id_type int = null,

Search should work with or without these variables.

搜索应该使用或不使用这些变量。

Benchmark:

  with COALESCE = Total Execution Time: 3582
  without COALESCE conditions = Total Execution Time: 13

You get the difference ...

你得到了不同......

Is there a nice solution how to ignore COALESCE and create dynamic SQL select with different approch ?

有一个很好的解决方案如何忽略COALESCE并使用不同的approch创建动态SQL选择?

Thanks.

1 个解决方案

#1

tIn your very specific case you should replace all your COALESCE search parameters with the following pattern:

在您的具体情况下,您应该使用以下模式替换所有COALESCE搜索参数:

 AND ( (@id_brand IS NULL) OR (i.id_brand = @id_brand) )

etc

The parameter is evaluated as a literal before execution, so doing it this way makes the condition sargable. This is functionally equivalent to your query except you're first checking against the literal value, which can be optimized away if it is, in fact, null.

该参数在执行前被计算为文字,因此这样做会使条件成为可搜索的。这在功能上等同于您的查询,除非您首先检查文字值,如果它实际上是null,则可以对其进行优化。

EDIT: Apparently this is the approach recommended in @Joe Stefanelli's link as well. I originally poached it from Erland Sommerskog.

编辑:显然这也是@Joe Stefanelli链接中推荐的方法。我最初是从Erland Sommerskog那里偷猎的。

EDIT2: And I always forget to mention OPTION (RECOMPILE) too.

编辑2:我总是忘记提及OPTION(RECOMPILE)。

#1