Full Text Index Not Working

Question

Hello,

Basics: I'm running SQL Server 2008 R2 back end with access 2007 front end.

I have a Full Text Index set up on our main table which is have set up to re-index every morning at 3am, or at least that's what I'm reading in the Properties of the table. However, day after day our searches are still very slow. The table is relatively small only 14,000 records or so. So I know this should not be a big deal for SQL.

Any suggestions for what I should be looking into to speed up searching of indexed fields?

I am currently reorganizing the table on a nightly basis, a few hours before the re-index takes place. Could this have anything to do with it???

Thank you in advance, and please let me know if you need more information.

David92595

Answer 1

I gather you are using a Full Text index but it tells me very little about how you are querying the table. Are you certain the front end is using the "contains" clause and not the "like" clause? Have you tried to capture the query and run it from management studio to see if there is a difference?

To answer this question, you will want to at least give us the script of the relevant table, its indexes (including the full text index definition) and the search query that is running slowly.

---

Ajith Krishnan
Please let me know if my post was helpful.

Answer 2

I'm not really querying a table, as much as I'm using the "Find" button on our access 2007 front end.  Our users are complaining that searches of fields that I thought I set up to be full text indexed are searching very very slowly.  Our main table only has about 14,000 rows, so SQL should make short work out of that small of a Table.

The Columns I am trying to index are:

the Primary Key, which is a 6-9 digit number.  This one seams to be responding the quickest.

Loan Number, is a 9-12 digit number.  This is one of our slowest responding fields.

Address, I think is self explanatory.

Primary Contact, this is a nvarchar 255 field, which holds names, also very slow.

There are a few more but these are the important ones...

Part of me is wondering if I've set up the full text index correctly.  I right clicked on the table is SSMS clicked on full text index, selected what columns I wanted to run a full text index on, and set a schedule for the table to reindex on a nightly basis, when no one is accessing the DB.

If it sounds like I missed a step please let me know, or if you have further questions.

Thank you for your help thus far,

David92595

Answer 3

Hello David,

I do not have any experience with the Access 2007 frontend so i am not sure what SQL Server activity the find button would translate to. It looks to me that you have setup a full text index appropriately but you may need to do something in the front end to take advantage of it. You could either ask the nearest Access expert or in the Access forums to determine whether there is an option in access that lets it take advantage of this. And when you do find out, i would like to know as well. :)

If the Loan Number is a very frequent search and if you dont have one already, a regular nonclustered index on it may get you significant relief. Under the table in the object explorer, right click on Indexes -> New Index. The new index wizard will guide you through the remaining. Loan Number should either be the only column in index key columns.

An index on a text column will help as long as you can provide the starting characters. For example a search for 'David*' would be faster than a '*avid*'. Based on this, you can take a call on whether the other columns (Primary contact for eg.) merit additional indexes. If on the other hand, you make most of your searches as '*string*' format, you will find the full text index more performant.

Maybe you are already aware of this but a full-text index is essentially an advanced text only index which uses word stemming to make searching through text very fast. Stemming refers to conversion of various forms of a word to its root form. As an example, running and ran would stem to the same word run. When you search by a string, the search string is "stemmed" and a reverse lookup is performed from the string to the rows of data where it was found. This would not be useful for columns like the Loan Number and the Primary Key. It would work better for the Address and the Primary Contact fields. Also, unlike regular indexes, SQL Server will not automatically start using a full-text index. The application has to take advantage of it by issuing queries appropriately.

Let me know if you have more questions.

---

Ajith Krishnan
Please let me know if my post was helpful.