Take the 2-minute tour ×

I have a table that I've created a Full Text Catalog on. The table has just over 6000 rows. I've added two columns to the index. The first could be considered a unique identifier of sorts and the second could be considered the content for that item (there are 11 other columns in my table that aren't part of the Full Text Catalog). Here is an example of a couple of rows:

TABLE: data_variables
ROW    unique_id    label
1      A100d1       Personal preference of online shopping sites
2      A100d2       Shopping behaviors for adults in household

In my web application on the front end, I have a text box that the user can type into to get a list of items that match whatever terms they're searching for in the UNIQUE ID or LABEL columns. So, for example, if the user typed in sho or a100 then a list would be populated with both of the rows above. If they typed in behav then a list would be populated with only row 2 above.

This is done via an Ajax request on each keyup. PHP calls a Stored Procedure on the SQL server that looks like:

SELECT TOP 50 dv.id, dv.id + ': ' + dv.label, 
              dv.type_id, dv.grouping, dv.friendly_label
FROM          data_variables dv
WHERE         (CONTAINS((dv.unique_id, dv.label), @search))

(@search is the text from the user that is passed into the Stored Procedure.)

I've noticed that this gets pretty sluggish, especially when I wasn't using TOP 50 in the query.

What I'm looking for is a way to speed this up either directly on the SQL Server or by abandoning the full-text indexing idea and using jQuery to search through an array of the searchable items on the client-side. I've looked a bit into the jQuery AutoComplete stuff and some other jQuery plugins for AutoComplete, but haven't yet tried to mock up anything. That would be my next step, but I wanted to check here first to see what advice I would get.

Thanks in advance.

share|improve this question
Can you confirm that you have measured the SQL performance alone and aren't just using your web page? If you are using the web page to test it, then lots of other things could be the issue, sure you know this, just wanting to double check. Does it get faster as you type longer search strings? If yes, this implies it isn't sql –  rlb Mar 2 '13 at 20:19
This is done via an Ajax request on each keyup -- is there a minimum length before this occurs? This is one of my pet peeves, so I won't rant too much, but I do hate it when the page swallows up my first few keystrokes. It would probably be better from a performance angle to wait for at least three or four characters before the full text search is invoked on keyup. The first few retrievals bring back boatloads. Even if you wait for a few characters before invoking, I would not expect this to scale very well. And I know it wouldn't work very well on a db in the cloud due to cloud latencies. –  Tim Mar 9 '13 at 11:07
Tim: yes - it triggers after 2 keystrokes. I can't make it more because there are unique IDs that are only two characters long. –  tptcat Mar 9 '13 at 18:40
add comment

5 Answers

up vote 5 down vote accepted

Several suggestions, based around the fact that you have only 6000 rows, so the database should eat this alive.

A. Try using Like operator, just in case it helps. Not expecting it too, but pretty trivial to try. There is something else going on here overall for you to detect this is slow given these small volumes.

B. can you cache queries in advance? With 6000 rows, there are probably only 36*36 combinations of 2 character queries, which should take virtually no memory and save the database any work.

C. Moving the selection out to the client is a good idea, depends on how big the 6000 rows are overall, vs network latency for individual lookups.

D. Combining b and c will give you really good performance I suspect, but with some coding effort required. If the server maintains a list of all single character results in cache, and clients download the letter cache set after first keystroke, then they potentially have a subset of all rows, but won't need to do more network IO for additional keystrokes.

share|improve this answer
I'm going to award you the bounty, rib, for your comment on my original post. I did some more complete measuring of my queries and the real issue is my front end. I'm going to work on optimizing that because that seems to be where the real slowdown is. Thanks for everyone's suggestions. –  tptcat Mar 9 '13 at 18:44
add comment

I would advise against a LIKE, unless you're using a linear index (left-to-right) and you're doing queries like LIKE 'work%'. If you're doing something like LIKE '%word%' a regular index isn't going to help you. You typically want to use a Full-Text index when you want to search for words inside a paragraph.

With a lot of data, typically the built-in Full-Text engines in databases aren't very stealer. For the best performance you typically have to go with an external solution that is built specifically for Full-Text.

Some options are Sphinx, Solr, and elasticsearch, just to name a few. I wouldn't say that any of these options are better than the other. There are definitely pros and cons to consider:

  • What kind of data do you have?
  • What language support do these solutions have?
  • What database engines do these solutions support?

The best thing you can do is benchmark these solutions against your existing data. Testing each and every individual component (unit testing) can help you identify the real problems and help you find good solutions.

share|improve this answer
add comment

I had the same problem and went for the LIKE solution. I found too that the or operator to be too taxing and divide the query into two selects with an union all (fastest, and in my scenario it was impossible to find the same text in the index column and the data).

Yours will be like

SELECT TOP 50 from (
select dv.id, dv.id + ': ' + dv.label, 
              dv.type_id, dv.grouping, dv.friendly_label
FROM          data_variables dv
WHERE         dv.unique_id like '%'+@search+'%'
select dv.id, dv.id + ': ' + dv.label, 
              dv.type_id, dv.grouping, dv.friendly_label
FROM          data_variables dv
WHERE         dv.label like '%'+@search+'%' 

Oh!! And test the performance in SQL Server, not the web!

share|improve this answer
add comment

If You plan to increase amount of data it will be best way to use reverse index for full-text searching.

Look at Apache Solr - best fulltext search engine at this moment.

You can simply periodically index Your database data and use solr as search-engine, it provide simple ajax api and can be queried directly from frontend.

share|improve this answer
from the personal experience solr option is good if the data has to be read only that is only data retrieval jobs has to be done, but if the data has to be upadted frequently then updating/maintaining solr index could be a costly/hectic task –  3nigma Mar 9 '13 at 12:51
Index can be splitted for this use-case. –  Nik Mar 11 '13 at 8:32
add comment

If you really need performance ..you may want to look at; FTS3 and FTS4 ...

snip... from another forum...

For example, if each of the 517430 documents in the "Enron E-Mail Dataset" is inserted into both an FTS table and an ordinary SQLite table created using the following SQL script:

Code: CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table / CREATE TABLE enrondata2(content TEXT); / Ordinary table */ Then either of the two queries below may be executed to find the number of documents in the database that contain the word "linux" (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.



share|improve this answer
add comment

Your Answer


