• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

wildcard search timeout on server

I have an archive table that is currently about 1.9 million records big. If I try a simple search on a surname such as:
"SELECT fldSurname FROM tblArchive WHERE fldSurname LIKE 'Hague' "
a recordset is returned in the blink of an eye.
However, if I try to query with wildcards, such as :
"SELECT fldSurname FROM tblArchive WHERE fldSurname LIKE '%Hague%' "
I eventually get an error message that the server has timed out and no recordset is returned.
The field fldSurname is indexed.
What is going wrong and how do I correct it so that I can do wildcard searches swiftly?
2 Solutions

You may want to have a look into Free Text Indexes.
(see here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp)

Which will then allow you to use CONTAINS
(see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2y2h.asp)

CONTAINS should give better performance for the search
Anthony PerkinsCommented:
>>The field fldSurname is indexed.<<
It does not matter.  Since you are using LIKE with wildcards you are condemned to a sequential search.  So with any table greater than a few laks you are going to be in for a long wait.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now