Formula - Script

HCL Domino Domino Forum
1

Hello all

I have a search form which has a computed field resolving to a search formula. I then use this formula in a script to do a db.search. It worked fine when there were only a few documents, but the dtabase has grown and the search is slooooooow.

The db is full text indexed and I have read that a db.ftsearch could be much faster. Can anyone tell me if there is a quick way to convert my formula to work wih ftsearch or do I have to rewrite?

formula

op := @If(searchoption=“Any”;“|”;“&”);

Suffix := @If(mailshottype=“P”; “&( paddress!=""&ptitle!=""&psurname!="")”;mailshottype=“S”;“”;“&( pemail!="")”);

md := @If(mailshottype!=“S”;“& (!pmaildisable="1")”;“”);

fuz := @If(ftoption=“F”;“@begins”;“@matches”);

n:=@If(psurname=“”;“”;@Implode(fuz+“(@LowerCase(psurname);"”+@LowerCase(psurname)+“"”+“) | “+fuz+”(@LowerCase(psurname_1);"”+@LowerCase(psurname)+“"”+“) |”));

n:= @If(n=“”;“”;@Ends(n;“|”);“(”+@LeftBack(n;“|”)+“)”+op;“(”+n+“)”+op);

cs := @If(currentstatus=“”;“”;“pcurrentstatus=”+currentstatus);

m := @If(pmoverequired=“”;“”;“Pmoverequired >=@Today & pmoverequired<=@TextToTime("”+@Text(pmoverequired)+“")”+op);

S:= @If(pstatus=“”;“”;@Implode(“@Contains(pstatus;"”+pstatus+“"”+“) |”));

S := @If(S=“”;“”;@Ends(s;“|”);“(”+@LeftBack(s;“|”)+“)”+op;“(”+s+“)”+op);

mp :=@If(pminprice=“”;“”;“pmaxprice >=”+@Text(pminprice/1.05)+op);

P := @If(pmaxprice=“”;“”;“pmaxprice <=”+@Text(pmaxprice*1.05)+op);

V := @If(ppropvalue=“”;“”;“ppropvalue”+Propvaloperator+“=”+@Text(ppropvalue*1.05)+op);

T := @If(ptypeinterest=“”;“”;@Implode(“@Contains(ptypeinterest;"”+ptypeinterest+“"”+“) |”));

T := @If(t=“”;“”;@Ends(t;“|”);“(”+@LeftBack(t;“|”)+“)”+op;“(”+t+“)”+op);

D := @If(psiteinterest=“”;“”;@Implode(“@Contains(psiteinterest;"”+psiteinterest+“"”+“) |”));

D := @If(d=“”;“”;@Ends(d;“|”);“(”+@LeftBack(d;“|”)+“)”+op;“(”+d+“)”+op);

PL:= @If(pplotinterest=“”;“”;@Implode(“@Contains(pplotinterest;"”+pplotinterest+“"”+“) |”));

pl := @If(pl=“”;“”;@Ends(pl;“|”);“(”+@LeftBack(pl;“|”)+“)”+op;“(”+pl+“)”+op);

Px:= @If(ppxinterest=“”;“”;@Implode(“@Contains(ppxinterest;"”+ppxinterest+“"”+“) |”));

px := @If(px=“”;“”;@Ends(px;“|”);“(”+@LeftBack(px;“|”)+“)”+op;“(”+px+“)”+op);

A:= @If(pareas=“”;“”;@Implode(“@Contains(pareas;"”+pareas+“"”+“) |”));

A:= @If(A=“”;“”;@Ends(A;“|”);“(”+@LeftBack(A;“|”)+“)”+op;“(”+A+“)”+op);

srch := mp+m+n+n2+cs+S+P+V+T+d+pl+px+A;

@If(srch=“”;“”;@Do(

@If(@Ends(srch;op);@LeftBack(srch;op);srch)+suffix+md))

thanks

2

Subject: Formula - Script

You can’t really use the same criteria for db.FTSearch as for db.search.

A db.FTSearch searches the text of the document and doesn’t allow you to specify field values (it’s like searching this forum).

A db.search allows you to find matches in specific fields (like creating a view formula).

You will probably need to re-write.

3

Subject: RE: Formula - Script

Ed is not completely correct.

You can specify fieldnames in a full-text search. E.g. to find documents whose Subject field contains the word “Fright” you could use this query:

[Subject] = “Fright”

Note, however, that this will match “Fright” wherever it appears in the Subject field – not just if the field is exactly equal to that. Full text search is also only optionally case sensitive.

Also, there is no full-text search equivalent for @Begins or @Matches.

You might well be able to use a full-text search that will narrow down the documents you want to find, but to implement parts of the search criteria, you’ll have to iterate thru the result set and throw out any that don’t match. How efficient this is, depends on how many documents you can manage to eliminate with the full text search.

The complete full-text syntax is documented in the Notes client help.

4

Subject: RE: Formula - Script

Thanks guys

I’ll definately have to do something as it can take upwards of 3 minutes to return a result when searching around 6000 documents :frowning:

I may try Andre’s suggestion of ftsearching then eliminating docs, but if you know of any way to speed up a normal dbsearch it would be most welcome!

Thanks again

Tom

5

Subject: RE: Formula - Script

There are two alternatives to Search – FTSearch and searching in views that are sorted on the values you’re searching for, e.g. using GetAllDocumentsByKey or GetAllEntriesByKey. If you use views, they’re nice because you can match the precise field value, or just the beginning part of a string (a la @Begins) depending on the exact argument. Since the view index is maintained by the server, the user doesn’t have to wait for the server to test each document for a match – that work has already been done. Range searching can also be done in views, but you have to program it yourself as a binary search in the view entries.

If you use @Matches and you’re going to “or” that with other criteria, you have to use db.Search on all the documents anyway, so you might as well just use that for the whole thing. Wildcard matches are always a pain because no sort of pre-created index is helpful – unless there are only certain matching strings you need to use, in which case you could create a view that uses @Matches in a column formula to categorize documents according to what they match.

You can improve db.Search performance by making sure to put the "Form=“xxx” & part first (if any), and select Optimize document table map in the advanced database properties tab. This only helps if the Form selection criteria actually eliminates a significant number of documents – if 99% of the documents in the db use that form, you’re not going to see much improvement this way.

If you search views for matching fields, and you have multiple criteria to search for, you can use program logic to combine your search results. E.g. you can keep Lists of UNIDs of documents that matched specific criteria, and merge them in different ways depending whether you’re doing an “or” or an “and” of the different criteria.

Once you have a document collection, you can also use FTSearch within the collection. That’s faster than iterating thru the collection and throwing out documents that don’t match your additional criteria.

6

Subject: RE: Formula - Script

Wow what a great answer, thanks Andre

You’ve certainly given me a few ideas and I’ll give them a try.

I really appreciate your time and the extensive answer.

Many thanks