SQL Server Performance Parameterized Queries Using Leading Wildcards

Question

I have a SQL 2008 R2 database with approximately 2 million rows in one of the tables, and I am struggling with the performance of a particular query when using parameterized SQL.

The table has a field containing the name in it:

[PatientsName] nvarchar NULL,

The field also has a simple index:

 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 make this request in the management studio, it takes about 4 seconds to complete:

 declare @StudyPatientsName nvarchar(64) set @StudyPatientsName= '%Jones%' SELECT COUNT(*) FROM Study WHERE Study.PatientsName like @StudyPatientsName
declare @StudyPatientsName nvarchar(64) set @StudyPatientsName= '%Jones%' SELECT COUNT(*) FROM Study WHERE Study.PatientsName like @StudyPatientsName 

But, when I execute this request:

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

it takes more than one and a half seconds to complete.

Considering execution plans, a query without parameterization performs an index scan using the aforementioned index, which is obviously effective. A parameterized query uses an index, but whether the range is executed by index.

Part of the problem is with the main template. When I delete the main template, both queries return for a split second. Unfortunately, I need to support leading wildcards.

We have a home ORM that sets up parameterized queries that have a problem. These queries are executed based on user input, so parameterized queries make sense to avoid things like SQL injection attack. I am wondering if there is a way to make a parameterized query function, as well as a non-parameterized query?

I did some research, considering various ways to give hints to the query optimizer, trying to get the optimizer to repeat the query plan for each query, but have not yet found anything to improve 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 matter, but it did not matter.

Any help would be appreciated.

Answer 1

It looks like you want to force a scan. There is a FORCESEEK hint, but I do not see any similar FORCESCAN hint. That should do it though.

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

Although, perhaps you can try the following according to your data and see how it works.

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

Answer 2

I think your best chance to improve performance here is to explore.

Answer 3

I am having trouble finding documentation to check this out, 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 that cannot be null and / or has a specific index, I believe (again, I can’t find the documents to confirm, so I could disconnect from the database), which will be Faster.

What happens if you change the request 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 4

If all else fails, you can try

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

Perhaps you could wrap it in 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 particular query, this is probably not the best way to do this, since scanning the index of an existing index is likely to be faster. However, this may be applicable in such situations.