How to avoid the FTsearch limition?

hcl-bot · January 22, 2007, 1:18pm

Hi,We are using FTSearch in our program codes for searching some catogery in our Nots mail database, because the space issues, we do not suggest the email client to menually full text indexing their mail db. But because we use FTSearch in our codes,(j = SentView.FTSearch( SearchStr , 0 )) and it is not possible to full text index db before using FTSearch, so if the database is too big, I got this error message “Notes error:Maximum allowable documents exceeded for a temporary full text index”.

I did some search in this forum, somebody mentioned to use “FTG_INDEX_LIMIT= X” in notes.ini file, but we tried it, it does not work.

Anybody know anyways to avoid this error message when using view.FTsearch, or do not use FTsearch to get the result.

Thanks a lot,

hcl-bot · January 22, 2007, 3:20pm

Subject: How to avoid the FTsearch limition?

I believe that “FTG_INDEX_LIMIT= X” applies to how much of the document is indexed, not how many documents are indexed. By default only the first 6 Meg of a document is indexed.

You probably want to look at “FT_MAX_SEARCH_RESULTS=X” - max value is ‘2147483647’.

If you’re performing a simple search, you MIGHT be able to use db.Search, otherwise you might just have to build a Full Text Index.

I have successfully created a Full Text Index via Script, the key is that the script has to run on the server (use ‘Agent.RunOnServer’) - but sometimes I still ran into problems.

hcl-bot · January 22, 2007, 3:27pm

Subject: RE: How to avoid the FTsearch limition?

Hi Mark,thanks a lot for your help,

Could you please refer my last notes? as we always have the limitation of ftsearch, and for my case is simplej, so can we have some other way to avoid the ftsearch?

I would not want to use db.search, as that will be slow.

hcl-bot · January 22, 2007, 9:16pm

Subject: RE: How to avoid the FTsearch limition?

You really have no choice, if you want fast you must use an index. Simple as that

Formula search is faster than you might think
FTSearch your way has to create the index THEN search

I would doubt that FTSearch would be much faster if at all.

Tell your customer, index = fast, no index = slow. Theres no getting around it.

Simon

hcl-bot · January 24, 2007, 9:44am

Subject: RE: How to avoid the FTsearch limition?

Peter et al,

If the mail files are big, then db.search (and @Formula language) will indeed be slow to get the collection of docs. I’m assuming that the collection will tend to be small, like dozens of docs, not large, like thousands of docs.

I think the approach I’d try to take here would be to use view.GetDocumentByKey and walk the view. My reasoning:

If you use view.AutoUpdate = False then performance will be very fast, comparable to ftsearch, but without needing a full text index
Make sure the view (or build another one for this purpose, if needed) is sorted by PostedDate. It looks like you provide your agent a date range and want it to grab all the docs whose PostedDate falls within that date range.
set doc = view.GetDocumentByKey ( startDate ). You may need to mess around with formatting your date as a string because LS strips the leading zeros and view column formulas don’t. For this kind of work, I often format as yyyymmdd in the view column and for my LS key. A very slight hassle, but excellent results.

Note that you might have to increment your startDate until you find a match. If your range tends to be small, no big deal. If your range might be thousands of days long, with only a handful of total hits, then I suppose this could be a deal breaker.

Now you keep walking the view, adding each doc to your collection, or processing one at a time, whatever. You keep checking each doc to make sure it falls in your range. When it goes outside your range, you’re done.

just a thought,

raphael

hcl-bot · January 31, 2007, 2:56pm

Subject: RE: How to avoid the FTsearch limition?

I think that the idea of searching a particular view (as opposed to the entire database) is a good one, however, only the FTSearch method is available for a view.

Is this a ‘One Time Thing’? Or will there be an ongoing requirement to do this?

If it’s ongoing, I’d bite the bullet & create all the FTIs ahead of time, and leave them. Read on…

I’ve had a lot (read: “A LOT”) of experience lately with this exact same type of project.

Domino 6.5.4 (thru 7.0.2) is full of bugs with full text searching, (creating, searching, maintaining, etc) and I’ve got a few PMRs open with IBM right now.

If you expect that your agent will take very long, one thing that you’ll probably run into is the Chronos task will stomp all over the search, and it will crash. If you have multiple databases creating FTI at the same time, the same thing will happen, and the server CPU will climb ‘over’ 100%.

Another issue I ran into with searching multiple mail files, is that after 50 or so mail files would open, the agent would crash with a Memory Allocation Error - and the server needs to be restarted. In my case the search argument is fairly long/complex - but beware if you try it on a production mail server.

The best thing that you can do if this is a ‘One Time Thing’, is to set up a seperate (“duplicate”) server, copy all the mail files over, build the FTIs and perform the search. Minimal impact to users - both from overhead and server crashes.

Some other notes that I made:

To avoid possible conflicts with Antivirus software, exclude the directories that will be searched. (TN 1208778)

It will be helpful to build the Full Text Indexes on the server (this can be a long-running process) before running this search agent.

Check / Set the following NOTES.INI variables as needed:

FT_MAX_SEARCH_RESULTS

The default value is 5000. Maximum Value: 2,147,483,647

FTG_INDEX_LIMIT (TN 1095171)

By default, Notes will only index the first 6 Meg (6291456 bytes) of an attachment. There is no maximum.

Example: 12 Meg defined as: FTG_INDEX_LIMIT=12582912

If using a complex (or long) search argument, the agent may error out. Note the database on which the error occurred, create another search document, and Utilize the ‘Restart at specific database’.

Kill the ‘Cronos’ job

That having been said…

This is some code from my agent that I’m using to perform FTSearch of all mail databases. This subroutine checks for the existance of a FTI, creates the FTI if necessary, performs the search, and goes on. Since FTI can only be created on a local database, This agent must be called as a “RunOnServer” agent.

Again, if this agent runs for over an hour, other tasks running on the server will invariably cause it to crash. I added code to allow me to specify which database to restart the search at:

The (partial) Code:

'Find all databases in the specified folder and search for text

	Set MaildbDir = New NotesDbDirectory( db.Server ) 

	Set Maildb = MaildbDir.GetFirstDatabase( DATABASE ) 

	Do Until Maildb Is Nothing

		If Not Lcase(Left(Maildb.FilePath, 5)) = "mail\" Then Goto nextdb



'Restart			

			If RestartDB$	<> "" Then				

				If Not Lcase(RestartDB$) = Lcase(Maildb.FilePath) Then 

					Print "..Skipping 'Mail' file: " + Maildb.Title

					Goto nextdb				

				End If

				RestartDB$ = ""

			End If



			Print "Searching 'Mail' file: " + Maildb.Title



		Gosub SearchDB

nextdb:

		Set Maildb = MaildbDir.GetNextDatabase

	Loop

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SearchDB:

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

’ CreateFTIndex works on Local Database only…

options& = FTINDEX_ATTACHED_FILES + FTINDEX_ENCRYPTED_FIELDS



If maildb.IsFTIndexed Then

‘’ Print "FTI available for: " + maildb.title

Else

	Print "Creating new FTI for: " + maildb.title

	Call maildb.CreateFTIndex(Clng(options&) , False)	'Need to convert to 'Long' per TN 1172696

End If

'Perform the Search

Set mailcoll = maildb.FTSearch( query$ , 0 ,  )

’ If mailcoll.Count > 0 Then Print “Found " + Cstr(mailcoll.Count) + " items. Copying…”

Set maildoc = mailcoll.GetFirstDocument()

While Not(maildoc Is Nothing)

'If found…

	Set newDoc = MailDoc.CopyToDatabase( db )	

	

	Set maildoc = mailcoll.GetNextDocument(maildoc)

Wend

’ Works only for ‘local’ databases

Print "Deleting FTI for " + maildb.title

If maildb.IsFTIndexed Then

	Call Maildb.RemoveFTIndex( )

End If			



Return

hcl-bot · January 22, 2007, 2:24pm

Subject: How to avoid the FTsearch limition?

Peter,

I know of no way to get around the rule that says that the ftindex will only be built on the fly for db’s with less than 5k docs, or whatever the limit is.

I would suggest not using ftsearch for mail files if the mail files are big and the use of your code is small. Instead, maybe have a view that is set up for your searches to use GetAllDocumentsByKey?

regards,

raphael

hcl-bot · January 22, 2007, 3:22pm

Subject: RE: How to avoid the FTsearch limition?

Hi Raphael,Thanks a lot for the help,

That is good idea, yes, I agree with you, it is better we do not use the ftsearch function. but I do not think it is easy,

My case is basically simple, but I just can not find a way. here it is:

I created a special view for the search in mail db called “NonCorpRecSent” which lists the view I wanted. Then I will use FtSearch to search this view in a specific period.

Set SentView=MailDb.GetView(“NonCorpRecSent”)

SearchStr= | [PostedDate] >= | & RanDoc.Sdate(0) & | And [PostedDate] <= | & RanDoc.Edate(0)

j = SentView.FTSearch( SearchStr , 0 )

So my question is how to dynamic get the result without using ftsearch?

Any other ideas? thanks a lot for the help again.

hcl-bot · January 22, 2007, 3:34pm

Subject: RE: How to avoid the FTsearch limition?

set coll=db.search(SearchStr, Nothing, 0)

You might also want to add the view selection formula to the search formula to include only same documents that are shown in the view:

SearchStr=SearchStr+| & Form=“Document” & Status=“Open”|