Slow search when using wildcards on indexed table
Posted on 2001-08-06
I have an application that makes queries on a moderately sized table of company names with upwards of 200,000 records.
My users will want to make searches on the field tblCompanies.CompanyName. My problem is that the time it is taking to get a response from SQL Server with a recordset is just too long.
The field is indexed - an ordinary index with default values for fill factor etc.
When a user makes a query " select * from tblCompanies where tblCompanies.CompanyName = @Name", the search executes VERY fast.
When a user makes a query " select * from tblCompanies where tblCompanies.CompanyName like @Name + '%' ", the search executes satisfactorily.
However, when a user makes a query " select * from tblCompanies where tblCompanies.CompanyName
like '%' + @Name + '%' ", the search executes very slowly. It can take up to 5 minutes to return a resultset. I have had to set query timeout to 0 so that the server can complete the search in its own times.
I know that SQL query ignores indexes when the '%' wildcard is used. But are there other ways of at least reducing the times taken to get results when the search is made?
I am using MSSQL 6.5 and have the queries in stored procedures being called from VB5 with relevant parameter values.