SQL Server Performance of Parameterized Queries with leading wildcards

Question

I have a SQL 2008 R2 Database with about 2 million rows in one of the tables and am struggling with the performance of a specific query when using parameterized SQL.

In the table, there's a field containing a name in it:

[PatientsName] nvarchar NULL,

There's also a simple index on the field:

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

When I do this query in the management studio, it takes around 4 seconds to execute:

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

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

But, when I execute this query:

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

it takes a bit more than a half second to execute.

Looking at the execution plans, the query without parameterization does an Index Scan using the above mentioned index, which obviously is efficient. The parameterized query uses the index, but does a range seek on the index.

Part of the issue is having the leading wildcard. When I remove the leading wildcard, both queries return in a fraction of a second. Unfortunately, I do need to support leading wildcards.

We have a home grown ORM that does parameterized queries where the problem originated. These queries are done based on input from a user, so parameterized queries make sense to avoid things like a SQL injection attack. I'm wondering if there's a way to make the parameterized query function as well as the non-parameterized query?

I've done some research looking at different ways to give hints to the query optimizer, trying to force the optimizer to redo the query plan on each query, but haven't found anything yet to improve the performance. I tried this query:

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

which was mentioned as a solution in this question, but it didn't make a difference.

Any help would be appreciated.

Answer 1

I think your best chance of improving performance here is to look into using a full text index.

Answer 2

I'm having trouble finding the documentation to verify this, but IIRC, COUNT(*) does a full table scan in MS SQL (as opposed to using a cached value). If you run it against a column which cannot be null and/or has an index defined, I believe (again, still can't find docs to confirm, so I could be off base here) that will be faster.

What happens when you modify the query to something like:

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

or

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

Answer 3

It seems like you want to force a scan. There is a FORCESEEK hint but I couldn't see any analogous FORCESCAN hint. This should do it though.

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

Although maybe you could try the following on your data and see how it works out .

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

Answer 4

Maybe you can try this:

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

select count(*) 
from Study 
where Study.PatientsName like '%' + @StudyPatientsName + '%'

you can also add with(nolock) and it might help:

select count(*) 
from Study  with(nolock)
where Study.PatientsName like '%' + @StudyPatientsName + '%'

Answer 5

If all else fails you could try

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

Perhaps you could wrap it in an IF

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

Edit: As martin pointed out, for this specific query this is probably not the best way to do it since an index scan of the existing index is likely faster. It might be applicable in similar situations, though.