TSQL Searching a large indexed database using LIKE

Question

I have a database containing addresses, one address per Row. Contains every address in the UK, so ~28 million rows. One of the columns is 'Street', which I am using to perform searches. I have a non-unique, non-clustered index on this column.

I'm having inconsistencies with search speeds however.

select * from Postcodes where Street = 'London Road'

Takes ~1s to run.

select * from Postcodes where Street like'London Road%'

Likewise takes about a second.

declare @Street varchar(20) = 'London Road%'
select * from Postcodes where Street like @Street

However, this statement, although apparantly identical to the second, takes about 40 seconds to run.

I'm at a complete loss to what is causing this speed difference. Any ideas?

Answer 1

The problem is that MSSQLServer can't know in advance what is going to be contained in @Street variable, so it can't use an index on Street column.

If your statement is:

select * from Postcodes where Street = 'London';
select * from Postcodes where Street like 'London Road%';

It can used an index on Street column because it knows the string starts with London.

If your statement is:

select * from Postcodes where Street like @VariableName;

It can't use the index because with this statement you should be able to do:

select * from Postcodes where Street like 'London%';
select * from Postcodes where Street like '%London%';

Or any other valid value for VariableName. It's a typical problem in precompiled statements.

What you can do is using the string directly, or not using parameters and change your statement to be a dynamic SQL statement in TSQL.

Answer 2

If you know in advance you will never have leading wildcards and are on SQL Server 2008 you could try

declare @Street varchar(20) = 'London Road%'
SELECT * FROM 
Postcodes WITH (FORCESEEK)
where Street like @Street