sql - 在 like 子句中搜索通配符 '%%' 的编译器成本

Question

我有一个动态存储过程，并且必须在 SQL Server 2008 中的 case 语句中动态添加 where 子句。

我的程序如下:-

CREATE PROCEDURE SPGETDATA
@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)

AS
BEGIN
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '' THEN 
'%'+ @STRNAME + '%' ELSE '%%' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '') THEN 
'%' + @STRCODE + '%'  ELSE '%%' END** 

END

用户可以选择@strname 或@strcode。但不能同时两者。

在这种情况下，一个 like 语句是可以的，但替代方案始终是查询的负担，因为它始终是

@STRNAME like '%%'

或如下

@STRCODE like '%%'

现在，如果我使用这种方法，编译器是否会花费一些时间来搜索“%%”，即使没有任何匹配的内容，还是会绕过它并且不花费任何费用？我还检查了执行计划，但它没有显示 like 子句。

因此我必须在 webApps 中使用它，因此必须考虑 sp 的速度。该表有数百万行。

两者的执行计划都是相同的。如果我在查询中使用 like claase 或从查询中删除它，它会显示 - 聚集索引 sacn 100%。

请帮忙。

Answer 1

首先，如果任一列可为空，则测试 col LIKE '%%' 不是无操作，但实际上相当于测试列 col IS NOT NULL code> 这很可能不是想要的效果。

其次，如果列不可为空，因此它确实是空操作，那么这不是一个特别好的方法，因为 SQL Server 将不会优化检查。请参阅Dynamic Search Conditions in T-SQL以获得更好的方法。

在你的情况下，这可能不会有太大区别。

由于您总是使用前导通配符针对一个或其他列进行搜索，并且您正在执行 SELECT * ，因此您很可能最终会进行全表扫描。

一种可能产生影响的情况是，您在一个或多个列上有一个较窄的索引，可以优先扫描这些列，而不是扫描整个聚集索引/表。但即便如此，SQL Server 仍然需要执行书签查找来检索 *，因此可以非常便宜地评估该特定查询的剩余谓词。

但是，生成的计划完全不适合使用其他参数进行调用，因此这种捕获所有查询的尝试可能会给您带来参数嗅探问题，如下所示。

测试表

CREATE TABLE myTable
(
id int primary key,
STRNAME VARCHAR(100) NOT NULL,
STRCODE VARCHAR(100) NOT NULL,
IsDELETED BIT NOT NULL DEFAULT 0,
Filler CHAR(7000) NULL,
)
INSERT INTO myTable(id, STRNAME, STRCODE)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)), 
       ISNULL(name,type), 
       ISNULL(name,type)
FROM master..spt_values

CREATE INDEX ix ON myTable(STRNAME)

首先使用名称参数调用(扫描计数 1，逻辑读取 7)

EXEC sp_executesql N'
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN 
''%''+ @STRNAME + ''%'' ELSE ''%%'' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN 
''%'' + @STRCODE + ''%''  ELSE ''%%'' END 
', 
N'@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)
', @STRNAME = '(rpc)', @STRCODE=''

使用代码参数重用相同计划进行调用(扫描计数 1，逻辑读取 7690)

EXEC sp_executesql N'
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN 
''%''+ @STRNAME + ''%'' ELSE ''%%'' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN 
''%'' + @STRCODE + ''%''  ELSE ''%%'' END 
', 
N'@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)
', @STRNAME = '', @STRCODE='(rpc)'

使用代码参数调用生成特定计划(扫描计数 1，逻辑读取 2517)

EXEC sp_executesql N'
SELECT  myTable.*
FROM myTable
WHERE 
IsDELETED = 0  
AND STRNAME LIKE CASE WHEN (RTRIM(LTRIM(@STRNAME))) <> '''' THEN 
''%''+ @STRNAME + ''%'' ELSE ''%%'' END 
AND STRCODE LIKE CASE WHEN (RTRIM(LTRIM(@STRCODE)) <> '''') THEN 
''%'' + @STRCODE + ''%''  ELSE ''%%'' END 
OPTION (RECOMPILE)
', 
N'@STRNAME NVARCHAR(100),
@STRCODE NVARCHAR(100)
', @STRNAME = '', @STRCODE='(rpc)'