SQL Server Full Text Catalog and Autocomplete

Question

Before I explain my problem, I would like to share some information about the context.

System we have a web site that is used to record tickets by users from many destinations. We have 1370 active users, 1012 destinations. Daily, they record around 30000 tickets, and at this moment we have a total of 10637019 tickets. Each ticket has an average of three positions.

In the system we also save the clients for each ticket, and when we want to record a new ticket, we choose a specific client from the list or we create a new one. At this moment we have 3763787 clients and every day we save 4500 new clients more.

Server We have two servers, one for web site and another for database. We use Microsoft Technology, what it means we have IIS7 and SQL Server 2008 R2. Data base server has 6 CPU 2.9GHz, 8 GB RAM.

Problem The problem that we have comes when we want to choose one client for a new ticket. We are using a web control with autocomplete to choose it. The autocomplete process runs at the data base server using a Full Text Index based on the clients's full name. The Full Text Index is populated every nigth.

We have a query in the form of:

select
    clientId
    ,name
    ,lastName
    ,fullName (calculated column in the clients table)
    ,gender
    ,birthDate
    ,type
from clients 
where contains(fullName, '"Carl*" AND "Gari*"')

This query is using 63% of the CPU resources in the server and we want to reduce that number.

How can we increase the performance of this? Is there an alternative to use autocomplete search with SQL Server 2008 R2?

Thanks in advance,

Answer 1

My initial comment, I assume computer fullname is something similar to FirstName + ' ' + LastName -- can you make the query firstname like 'Carl%' and lastname like 'Gari%' was intended to gather info. Thanks for answering it.

I have not tried this myself (and it only matters with your enviroment), but your could add the like clauses to the contains based query and compare the query plans to the contains version.

There are 3 possible queries obvious queries

A Contains version -- your existing query
A Like Version -- as I commented
A Combined version -- using like and contains

I don't think you are necessarily right about using full-index search always being faster than the LIKE version, because I think the correct answer is, it depends.

If you have indexes on the lastname (or firstname too), the "like" version should do a indexed lookup. It will depend on your distribution of keys and what percentage match. I.e., search for lastname like 'G%' and firstname like 'Carl%' than 'Gari%' and 'Carl%' would be much slower if index on lastname only. So, if you have sufficiently long search keys, the LIKE version will likely be faster than than the contains version. You will have to test to know what works best for sure.

It may be that the combined version is always the best choice or at least good enough. But I would definitely try this first before following the strategy below.

The overall strategy I would suggest is:

Stop using incremental search until user has typed in at least a few characters -- you are likely paying a lot of the performance hit for doing so at it has almost no real value to the users of your website. As a suggestion, don't do incremental search before they enter at least 3 characters. Since you did not mention that you already do require minimum characters before incremental search.

If this is vetoed, same basic strategy, but you don't invoke the incremental search until NN milliseconds lapsed since last character typed or number characters typed > some length. In fact, since some last names are only 2 characters, you pretty much have to use this strategy in reality.

Likewise, I would not do any incremental search at all regardless of length as long as user is quickly entering new characters to avoid wasted searches that the user is not using.

Unless the combined query is always good enough, Have two different stored procs on your server to hand back search results, one being the like version, the other being the contains version. Call the version expected to get the best results depending upon how much of the name was provided.