2

I have a table named Locations with a FullText Index on all columns. There's one PK Column (INT) and the rest are TEXT/VARCHAR. This table has 300,000 records.

The following query is taking 2 minutes to return one record.
SELECT TOP 1 * FROM Locations WHERE CONTAINS(*, '"1*"') ORDER BY LocationID

This slow query time is consistant when using any combination of numbers from 1 to 3 digits in length.

Using a characters (a-zA-Z) are performing normally, with a sub 25 milisecond response time.

Any idea why the numeric values are causing such a performance hit?

Michał Szkudlarek
  • 1,443
  • 1
  • 21
  • 35
Michael Potvin
  • 21
  • 2

1 Answers1

0

I suspect it is a combination of 2 causes.

Cause 1: Wildcard searches on common prefixes are slow. Do the records contain a lot of strings (numeric or alphanumeric) that begin with "1"? If so, that might explain the poor performance.

Wildcard searches tend to be slower than other full text searches. The more terms there are that contain the prefix ("1" in your case), the more work the full text engine has to do.

Although 300,000 records is not a lot of records for the full text engine to handle, factors like the number of unique terms in each record and the number of records and columns in which each of those terms is found will contribute even more to the search performance.

Cause 2: Missing index on ORDER BY columns. You should make sure the LocationID column is indexed since that is how you're sorting the results. It is possible that "1*" is generating a lot of results, all of which need to be sorted. If there is no index, the sort could take a long time.

Keith
  • 20,636
  • 11
  • 84
  • 125