Neroon

I'm running a query against a large table (5 millon+ records). The table has a composite index of surname, postcode and dob and a primary key which is a numeric value. When I run this code, the query takes 25 seconds and is performing a table scan:

DECLARE @surname char(25);
SET @surname='MySurname%';

SELECT *
FROM Prospect
WHERE surname LIKE @surname;

This statement uses the composite index and runs in less than a second:

SELECT *
FROM Prospect
WHERE
surname LIKE 'MySurname%';

We've rebuilt the indices which only made matter worse!

We're running 2005 version 09.00.3054.


Re: .NET Framework Data Access and Storage More slow SQL Server

VMazur

It depends on you are passing to the @surname parameter. If value of this parameter starts with text and then wildcard (like myName%), then database engine can use index because it has starting value of the string. But if parameter value starts with wildcard (like %Myname), then engine cannot use index because there is no starting string value and it has to do full table scan.






Re: .NET Framework Data Access and Storage More slow SQL Server

Neroon

No, we're querying with the wildcard at the end. It is only slow when we declare a variable and use that variable in the like statement. An equivalent statement with the like as a literal uses the correct index.




Re: .NET Framework Data Access and Storage More slow SQL Server

Neroon

For all of you poor souls out there who come across this...It's the optimizer *helping* our query along.

Read the excellent post:

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html