SqlCommand在为LIKE传递文本参数时表现不佳
本文关键字:参数 文本 在为 LIKE SqlCommand | 更新日期: 2023-09-27 18:01:20
我遇到了一个很奇怪的问题:
我动态生成SQL server命令,其中一部分是一组LIKE测试,用于对多个列和表进行文本搜索。它看起来像:
SET @text = '%' + REPLACE(REPLACE(REPLACE(@text, '!', '!!'), '%', '!%'), '_', '!_') + '%'
INSERT INTO
@textFiltered
FROM
@documents d
LEFT JOIN docData t1 WITH (NOLOCK)
ON t1.ID = d.ID AND
(t1.Topic like @text escape '!'
OR t1.Subject like @text escape '!')
LEFT JOIN docData2 t2 WITH (NOLOCK)
ON t2.ID = d.ID AND
t2.Name like @text escape '!'
WHERE
t1.ID IS NOT NULL
OR t2.ID IS NOT NULL
(当然,这不是文本搜索的最佳方式,但这不是重点)
现在,当我在c#中创建SQLCommand时,像这样:
using (var cmd = new SqlCommand())
{
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.Add("text", NVarChar, 4000).Value = searchText;
var reader = cmd.ExecuteReader();
....
}
的执行性能非常差(例如8秒),而在SQL Management Studio中执行相同的查询要快得多(例如500毫秒)。但是,如果不是将文本作为参数传递,而是将其嵌入到SQL文本中:
DECLARE @text nvarchar(max)
SET @text = '<embedded-text>'
那么SqlCommand也运行得很快。更奇怪的是,这种行为似乎与LIKE子句中使用的一组列相关(不知道是如何关联的)。这些列的类型可以是nvarchar(size), nvarchar(max), ntext.
我怀疑问题出在参数上——可能是它的类型不正确或者其他什么。
注:试图创建size =搜索文本长度+ 1的参数-没有帮助。
我最好的猜测是它与SQL Server为您选择的查询计划有关。使用SQL服务器可以根据现有的统计数据计算的常量varchar和使用服务器不知道的任意变量之间有很大的区别。
你可以尝试OPTION (RECOMPILE)提示。虽然这个提示会导致在每次调用时编译存储过程,但它也允许SQL Server搜索给定值的最佳计划,也许在您的情况下,这将是一个很好的折衷。
您还可以为存储过程的两个选项添加查询计划,也许有人能够看到差异,并指出确切的问题。