代码之家 › 专栏 › 技术社区 › Steve Wranovsky

带前导通配符的参数化查询的SQL Server性能

parameterized sql-server-2008 sql-server

6

Steve Wranovsky · 技术社区 · 15 年前

我有一个SQL2008R2数据库,其中一个表中有大约200万行,在使用参数化SQL时,我正在努力提高特定查询的性能。

[PatientsName] nvarchar NULL,

字段上还有一个简单的索引:


CREATE NONCLUSTERED INDEX [IX_Study_PatientsName] ON [dbo].[Study] 
(
    [PatientsName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [INDEXES]
GO


declare @StudyPatientsName nvarchar(64)
set @StudyPatientsName= '%Jones%'

SELECT COUNT(*) FROM Study WHERE Study.PatientsName like @StudyPatientsName

但是,当我执行这个查询时:


SELECT COUNT(*) FROM Study WHERE Study.PatientsName like '%Jones%'

从执行计划来看,没有参数化的查询使用上述索引进行索引扫描,这显然是有效的。参数化查询使用索引,但对索引执行范围查找。

问题的一部分是使用前导通配符。当我删除前导通配符时,两个查询都会在几秒钟内返回。不幸的是,我确实需要支持前导通配符。

我们有一个自行开发的ORM,它在问题发生的地方执行参数化查询。这些查询是基于用户的输入来完成的,因此参数化查询对于避免SQL注入攻击之类的事情是有意义的。我想知道是否有一种方法可以实现参数化查询函数和非参数化查询函数?

我做了一些研究,寻找给查询优化器提示的不同方法,试图强制优化器在每个查询上重做查询计划,但还没有发现任何可以提高性能的方法。我尝试了以下查询:


SELECT COUNT(*) FROM Study WHERE Study.PatientsName like @StudyPatientsName
OPTION ( OPTIMIZE FOR (@StudyPatientsName = '%Jones%'))

其中提到的解决方案 this 问题,但没什么区别。

任何帮助都将不胜感激。

4 回复 | 直到 8 年前

1

4

Martin Smith 15 年前

FORCESEEK 但我看不到任何类似的 FORCESCAN

SELECT COUNT(*) 
FROM Study 
WHERE Study.PatientsName + '' like @StudyPatientsName

不过,也许你可以在你的数据上试试下面的方法,看看结果如何。

SELECT COUNT(*) 
FROM Study 
WHERE Study.PatientsName  like @StudyPatientsName
option (recompile)

2

3

Joe Stefanelli 15 年前

我认为你在这里提高表现的最好机会是考虑使用 full text index .

3

0

AllenG 15 年前

我很难找到文档来验证这一点,但是IIRC,COUNT(*)在mssql中执行完整的表扫描(而不是使用缓存值)。如果对不能为null和/或已定义索引的列运行它,则I 相信

将查询修改为以下内容时会发生什么情况:

SELECT COUNT(id) FROM Study WHERE Study.PatientsName Like @StudyPatientsName

SELECT COUNT(PatientsName) FROM Study 
WHERE Study.PatientsName 
LIKE @StudyPatientsName

4

0

erikkallen 15 年前

如果一切都失败了,你可以试试

SELECT COUNT(*) FROM Study WITH(INDEX(0)) WHERE Study.PatientsName like @StudyPatientsName

IF substring(@StudyPatientsName, 1, 1) = '%'
    SELECT COUNT(*) FROM Study WITH(INDEX(0)) WHERE Study.PatientsName like @StudyPatientsName
ELSE
    SELECT COUNT(*) FROM Study WHERE Study.PatientsName like @StudyPatientsName

编辑: 正如martin指出的,对于这个特定的查询,这可能不是最好的方法,因为对现有索引的索引扫描可能更快。不过,它可能适用于类似的情况。