Full Text Index Not Working (1 Viewer)

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

Hi there

Are you absolutely sure it's the full text indexed column that causing the slow down? maybe it's some other joins / where clauses slowing the query down.

Can you possibly give examples as it's difficult to answer off the cuff

Sure, I probably should have been more specific from the start, but I'm new to SQL...

Our queries are running fine. My problem is with the "Find"/search function in our Access 2007 front end. Most specifically with one of our fields, Loan Number, which is a nvarchar(255). Usually it is a 9-12 digit number, with dashes in it sometimes, which is why it has to be a varchar...
We have a table of only 14,000 rows, yet searching for one of these numbers can take a minute plus!?!
Am I doing something wrong setting up my full text index? I right clicked on the table, selected Full-text index, then Define Full-Text Index. went thought the steps to set up other columns to index, loan number being the most important, and set a time (3am) for the table to reindex itself on a nightly basis.
The Full text index will work for a day, the loan number search is still quite slow. But the next day we are back to all searches being slow, like something is over riding the index each night. The only think I can think of is that we are also reorganizing the table on a nightly basis as well. I'm doing this because our primary key is unique, but not being entered/created in numerical order.

My biggest concern is speeding up the searches...

Please let me know if you have any further question

Thank you for your help thus far,
David92595

Hi there

Thanks for your comprehensive reply.

How many fields are you adding to this index?

Does it have to be a text search using a full text index? it would be much quicker to do exact match searching on loan number using a normal non clustered index.

Why is your loan number column so large? if it's only 8-12 digits then nvarchar(15) would be a much better choice

Q: How many fields are you adding to this index?
A: there are about 7 fields total that I've added to the index. They are our most searched by fields, Loan number, address, state, File status, ect.

Q: Does it have to be a text search using a full text index? it would be much quicker to do exact match searching on loan number using a normal non clustered index.
A: how do I set up a non clustered index? this sounds like a potential solution to my problem.

Q:Why is your loan number column so large? if it's only 8-12 digits then nvarchar(15) would be a much better choice
A: Our database is still very new and that's what it defaulted to. Does it actually make a difference if it is a nvarchar 15 or 255, if only 15 digits are being used?

Full text indexes are for searching for key words in a string of text.

So by adding Loan number, address, state, File status to your full text index you are asking it to search for the word "Bristol" for example in

"123-456-45 high street Bristol Gloustershire br10 6lp file uploaded"

Try switching to a non clustered index for loan number as the first test.

Open management studio and expand the table that contains loan number, you will see an option for indexes, right click add new index. Add an index name.... I usually use IDX_tablename_columnname as my naming convention, select non-clustered (has to be non clustered because you will already have a clustered index on your primary key). Add the column loan number to the index and press ok.

Now do some testing with loan number as a where clause and let me know how quick the queries respond

I tried your suggestion. Downside, it over rode the PK clustered index. So now our files are all out of order (they were ordered by PK).

How do I create non clustered index's while still maintaining the clustered index as the primary index?

David92595

It wont have overridden your primary key index, it is now using the correct index for the query. Simply adding an ORDER BY statement will fix this.

I strongly suggest you get yourself a copy of this database, for running tests on, which is what I meant, I didn't realise you were going to apply it to the production database without testing

I'm not running a query. I'm trying to index a table so searches, in our access front end will run quicker. we are currently having to wait a minute plus for a search, of a table of only 14,000 rows.

If your form has a table as it's record source, aside from the bad design issues, then adding a non-clustered index on loan number would not have changed the order of the records, non-clustered indexes do not have order by properties.

If adding an index to a table messes up your application then you need to have a re-think of your design, simply basing the forms record source on a table is very bad design. What happens when you have 500,000 records...anyone who uses the form will have to wait for the database to return all the records before they can do anything.

Use un-bound forms and queries for your searches

I'm not indexing for queries. I'm indexing for data entry, and different parts of of different records are being added at different points. So I need a way to have our employees find the record in our DB so they can add addition information to a record. Having the table be the control source is the most direct and only way I know of adding data to a record using an access front end. I could possible connect the access front end to a view of the table, but I don't see how that would change anything or make anything run any faster.

Our queries run just fine, they take less then a second to populate.

I believe a Like search would be better in this instance with only 14,000 records. Even with 200,000 records somtimes it is better depending on the field type you are searching. I would try like search and then practice with the full text search in your test DB.

So now our files are all out of order (they were ordered by PK)

Click to expand...

Tables are unordered sets unless you use a clustered index. Use queries to order rows.

I'm not indexing for queries. I'm indexing for data entry

Click to expand...

Queries are the ONLY reason to index. The more indexes you have (to a point), the better your search speed and the slower each individual insert will be. If you have 10 indexes on a table, all 10 need to be updated when you add/update a row in the main table.

I think you are completely misusing the full text index. Create individual indexes on the most searched on columns. Avoid LIKE as the relational operator in your queries. It prevents indexes from being used entirely in some RDBMS' and I think with SQL Server if the search string starts with a wild card no index will be used and that may be why you are experiencing such slowness. I have tables 10 times that size that respond instantly to a search involving an indexed field.

Why do you care what order records are in a table anyway? All forms should be based on queries with selection criteria to minimize data transfered from the server. When you Access form is bound to a table, the form opens after a few records are retrieved and then sits there sucking down data until all 14,000 rows are retrieved. Then you search for the next record and it sucks down the same 14,000 records and all your users are doing this. No wonder your searches take over a minute. You're taking all the bandwidth with pulling down endless streams of unnecessary data.

SQL Server is slower than ACE when you use it the way you would use ACE. Use it correctly and it will be plenty fast enough.