条件查询（搜索表单）的性能注意事项-解网

问：

我经常发现存储过程的代码如下：

SELECT columns
FROM table_source
WHERE 
    (@Param1 IS NULL OR Column1 LIKE @Param1)
AND (@Param2 IS NULL OR Column2 = @Param2)
AND (@Param3 IS NULL OR ISNULL(Column3,'') LIKE @Param3 + '%')
…

这比这样的事情更好吗：

WHERE 
    (Column1 LIKE COALESCE(@Param1, Column1))
AND (Column2  = COALESCE(@Param2, Column2))
AND (ISNULL(Column3,'') LIKE COALESCE(@Param3 + '%', ISNULL(Column3, '')))
…

如果我拉出仅依赖于参数的表达式，这有关系吗？

DECLARE @Param3Search nvarchar(30);
SET @Param3Search = @Param3 + '%';

然后用代替？@Param3Search@Param3

很抱歉问这么宽泛的问题，但我相信编写此类查询有一些一般的经验法则。我只是找不到关于这个问题的现有问题。

sql-server sql-server-2008

DECLARE @Param1 [DataType]
DECLARE @Param2 [DataType]
DECLARE @Param3 [DataType]

DECLARE @SQL NVARCHAR(MAX);


SET @SQL = N'SELECT columns FROM table_source  WHERE 1 = 1 '
           + CASE WHEN @Param1 IS NOT NULL 
                THEN N' AND Column1 LIKE @Param1 '        ELSE N' ' END
           + CASE WHEN @Param2 IS NOT NULL 
                THEN N' AND Column2 = @Param2 '           ELSE N' ' END
            + CASE WHEN @Param3 IS NOT NULL 
                THEN N' AND Column3 LIKE @Param3 +''%'' ' ELSE N' ' END 

EXECUTE sp_executesql @SQL
                    ,N'@Param1 DataType, @Param2 DataType, @Param3 DataType'
                    ,@Param1
                    ,@Param2
                    ,@Param3

其他方法的问题是 Sql Server 不会像这样短路查询。即使参数为 null，它仍可能继续尝试计算表达式 Column2 = @Param2 。(@Param2 IS NULL OR Column2 = @Param2)

因此，使用动态 sql，您可以根据变量构建查询，然后仅使用必需的 where 子句执行查询。

此外，在存储过程中使用动态 sql 使您能够为存储过程制定参数化的执行计划。

使用您当前的方法，参数嗅探将从非常简单的查询中吸取性能。

故事的寓意：坚持使用具有这些可选参数的动态 sql，并使用sp_executesql系统存储过程（保护您免受 sql 注入攻击），为您的 sql 服务器提供更好的性能和更少的工作量。

4赞 Martin Smith 12/25/2014 #2

有关此主题的权威文章链接自 T-SQL 中的动态搜索条件

您的问题标记为 SQL Server 2008。如果至少使用 SP1 CU5，则可以利用“参数嵌入优化”行为作为动态 SQL 的替代方法。

SELECT columns
FROM   table_source
WHERE  ( @Param1 IS NULL
          OR Column1 LIKE @Param1 )
       AND ( @Param2 IS NULL
              OR Column2 = @Param2 )
       AND ( @Param3 IS NULL
              OR ISNULL(Column3, '') LIKE @Param3 + '%' )
OPTION (RECOMPILE);

将在每次调用时重新编译，并能够考虑该执行的实际变量/参数值。

暂时假设所有都是。该计划将编制NOT NULL

SELECT columns
FROM   table_source
WHERE  Column1 LIKE @Param1
       AND Column2 = @Param2
       AND ISNULL(Column3, '') LIKE @Param3 + '%'

（我可能会看看扩展 Column3 上的谓词是否也会导致更好的计划）

现在假设它们都是 NULL。该计划应简化为

SELECT columns
FROM   table_source

这可能比动态 SQL 方法更易于维护，并且意味着缓存中可能只有更少的一次性使用计划，但确实有重新编译的额外开销。

1赞 shadow walker 12/26/2014 #3

我会选择使用 COALESCE 而不是 ISNULL，尽管在大多数情况下，两者之间的性能是相同的，但 T-SQL 本身可能是问题所在，如果我们使用 ISNULL，它会变得不必要地冗长。

请参见：http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

上一个：Visual Studio 解决方案中 SccNumberOfProjects 的含义

下一个：术语“自定义类”的含义是什么？

条件查询（搜索表单）的性能注意事项

Performance Considerations for Conditional Queries (Search Forms)

评论