wildcard search timeout on server

Posted on 2005-05-10
Last Modified: 2010-03-19
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?
Question by:alitor99
    LVL 23

    Assisted Solution


    You may want to have a look into Free Text Indexes.
    (see here:

    Which will then allow you to use CONTAINS

    CONTAINS should give better performance for the search
    LVL 75

    Accepted Solution

    >>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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now