October 20, 2003 at 3:42 pm
I have a SQL 2K db running on a remote co-located box as well as a nightly replication of it locally. Part of the application involves a lot of ad-hoc user searches, and I'm using the Full Text search capabilites to do this as efficiently as possible.
Here's the problem: I'm getting different results with searches from the remote DB and the local copy using the CONTAINS criteria. I can run the same application code and point to the different DB's, and get different results. This happens with completely identical data, using identical stored procedures.
Example:
SQL: SELECT foo FROM productTable WHERE CONTAINS (productCode, '"FSB 5000*"')
This gets 9 results in my local copy but no results in the remote version. I've rebuilt identical indexs in both, and the same thing happens both servers are Windows 2000 with the latest service packs, both OS and SQL Server.
Are there settings related to the Full Text search capability that I may have set up differently between SQL installs? This is driving me nuts, any help is greatly appreciated.
-eyepulp
October 21, 2003 at 5:27 am
How are you updating the full-text indexes? If you're using the background process, are you inserting or updating using the WRITETEXT and/or UPDATETEXT statements? Do the results of sp_help_fulltext_tables from each server compare exactly? How do the sizes of the catalogs and noise word files compare?
I am a bit puzzled due to the inferences I make from your example query. You have a table named ProductTable with a column named "ProductCode" that you search for a specific product code using a full-text index. Either your data isn't normalized, or you're mistakenly using full-text indexing instead of a SQL Server index, or your naming convention is misleading (at least to me).
--Jonathan
--Jonathan
October 21, 2003 at 5:28 am
Of the rows you know of the local copy have data in them use the key field and query the other DB to validate the data. Could be something wrong in the replication.
October 21, 2003 at 9:37 am
Thanks for taking the time to respond.
Jonathon: Although the productCode I used was a valid one, the table names and field names have been changed to protect the innocent. The product code is a small varchar field, and the client dictates it - we do not use it for a PK. They also search on it quite often. I ran sp_help_fulltext_tables on each server and the results are identical. The noise files are identical. However, the Full-Text Catalog properties do differ in the Unique key count. The server that fails the search has 19,622 unique keys, while the local copy has considerably more with 33,219 keys. So far this is the only difference between both catalogs. Would this account for the problem, and if so, how do I force equality, preferably with the larger key count being the result?
Antare: I wasn't sure what you were indicating other than to verify the data being identical, which other than the catalog key count, it is.
October 21, 2003 at 10:01 am
quote:
Thanks for taking the time to respond.Jonathon: Although the productCode I used was a valid one, the table names and field names have been changed to protect the innocent. The product code is a small varchar field, and the client dictates it - we do not use it for a PK. They also search on it quite often.
Why use full-text search instead of just a SQL Server index on the column? I think, on a "small varchar" column, even a search that cannot use the index (like WHERE ProductCode LIKE '%FSB 5000%') will be faster than using a facility outside of SQL Server.
quote:
I ran sp_help_fulltext_tables on each server and the results are identical. The noise files are identical. However, the Full-Text Catalog properties do differ in the Unique key count. The server that fails the search has 19,622 unique keys, while the local copy has considerably more with 33,219 keys. So far this is the only difference between both catalogs. Would this account for the problem, and if so, how do I force equality, preferably with the larger key count being the result?
Sounds like your REBUILDs are not working right.
--Jonathan
--Jonathan
October 21, 2003 at 10:20 am
Hi Jonathon
You're quite right that a LIKE search would do the trick. That is in fact what is currently being used, and it's a tremendous hit on the server, CPU-wise (we get a lot of traffic). The CONTAINS version barely causes a blink. So for efficiency, I'm trying to move to CONTAINS. And since I got it working locally, I figured it should be no problem getting it to work remotely... =) (famous last words).
I already have done indexing on the column, but it didn't reduce the load the way CONTAINS appears to offer. So now my focus has turned to the Unique Key Count and determnining what's holding it back. Any thoughts? I'm not seeing any errors in the event log.
Thanks again for your responses.
October 21, 2003 at 10:59 am
quote:
Hi JonathonYou're quite right that a LIKE search would do the trick. That is in fact what is currently being used, and it's a tremendous hit on the server, CPU-wise (we get a lot of traffic). The CONTAINS version barely causes a blink. So for efficiency, I'm trying to move to CONTAINS. And since I got it working locally, I figured it should be no problem getting it to work remotely... =) (famous last words).
I already have done indexing on the column, but it didn't reduce the load the way CONTAINS appears to offer.
That's interesting; I know performance will be better with large numbers of rows and long character strings, but I never heard of anyone using FTS with short strings to gain performance. Do the LIKE SARGs usually need the wildcard before the significant characters (e.g. '%FSB 5000%') or can you instead use the index(e.g. 'FSB 5000%')? Have you tried creating the clustered index on this column?
If there are many inserts and updates, I would worry about the additional overhead of keeping the full-text index updated. As MS Search and SQL Server must be on the same server, you're also taking processing power and memory away from SQL Server...
quote:
So now my focus has turned to the Unique Key Count and determnining what's holding it back. Any thoughts? I'm not seeing any errors in the event log.Thanks again for your responses.
As I wrote before, I suspect a problem with the way the rebuilds are being done on the remote server.
--Jonathan
--Jonathan
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply