March 25, 2009 at 12:24 pm
I have been tasked with doing a Proof Of Concept using Full Text Search. We have a database which is a document management system that holds metadata abiout documents as weell as the actual image itself. There is a new requirement where people can search through the web site for any part of a document name. We have over 60 million documents in the system and DocumentName is stored in a varchar(100) field. We tried the old fashioned way on TSQL usinf LIKE '%XXX%' and it took more than 30 minutes. Our immediate solution is to cluster on the DocumentName which generates a 100GB tran log every time it is reindexed and has become a maintenanc nightmare.
With that said, I created a catalog, and then an index on tblDocument(DocumentName).
I am having problems in 2 areas.
1. It is in its initial population phase, and I have no way to rtell how much progress it has made. I run the same query and get a few more records returned every few minutes which tells me it is stiull building its internal searching lists. Does anyone know how to see this progress, or administer the popuilation of the index. I have found the following that just tells me a population is in progress
exec sp_help_fulltext_catalogs @fulltext_catalog_name = 'DocumentName'
2. What is the recommendation for maitnenance for full text indexes. I have this one set to update automatically, but do I still need to rebuild, reorganize, or totally re-populate it and what makes that determination
March 25, 2009 at 1:48 pm
I had the same issue with LIKE '%searchterm%'. It took 18 minutes to search one million text files.
I changed to the "CONTAINS" statement and it went down to 1.5 seconds ! Yes, 720 TIMES faster !!!
March 25, 2009 at 1:53 pm
Did you have your Full Text Index set to update automatically, or did you have to do any maintenance on it?
The preliminary tests I have done show a drastic improvement but I am trying to plan ahead for when we push this into production and think of unforseen things that might hurt the performance
March 25, 2009 at 2:01 pm
FTI was set to automatic, but i have a much smaller database (only 35GB).
I load approx 4k new text documents daily and then run a reindex(6 minutes), tlog backup (5 minutes) then full backup (12 minutes) and get great performance:)
Good Luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply