Hi,We are using Oracle 10g communicate with Domino thru iiop / diop
and the marriage was successful until we start using database.search (formula) and the search came to a crawl. Why do you think this is? Does trimming the fat from the domino database help?
Subject: Is the datbase full text indexed?
Full text indexing will make search faster.
If that doesn’t work, avoid doing search and then go for Lookups. Idea is to create a view in Notes database with all fields you specify in Oracle. View lookup works much much faster than going for Database level text search.
If you are searching using a Numeric field, keep the field as Numeric only - don’t convert it into text when travels from Oracle to Notes. Numeric search is much faster than text search.
Regards
Byju Joy
Subject: RE: Is the datbase full text indexed?
“Full text indexing will make search faster.”
I’ve read that before, but never found any reference to why the full-text index should affect db.Search() at all. I would consider them to be completely unrelated. Can you shed some more light on that statement?
Subject: RE: Is the datbase full text indexed?
Yes the database is fully indexed. I too didn’t think that has anything to do with the method of database.search
Here is what we do.
There is a certain field in documents that we search based on the user input. i.e
DocumentCollecton collecton = database.search(“Form ="someform" & @contains(thecertainfield;"”+userinput+"")
Document document = collection.getFirstDocument();
while (document != null)
{
prnt something…
document = collection.getNextDocument(document);
}
Subject: Create a View with formula used in Search
Creating a view (physical view, as you mentioned) in the database will give better response time.But this is applicable only if the key word you are searching appears in the beginning of lookup field value. Not applicable if it appears in the middle.
In other words db.search can find documents even if the key word appears in the middle of field. View.getAllDocumentByKey will find documents only the key word appears in the beginning of field.
Obiviously db.search is much more powerful, and it can look in ALL places (beginning/middle) of a field value, for ALL fields in ALL documents in a database. The price for this is performance.
So cretaing a view, is just an idea of performance tuning for your code, which now works very slowly for you.
Regards
Byju Joy
Subject: RE: Create a View with formula used in Search
Thank you all for your help.We solve the problem this way.
We created a batch work that dumps the document collections in an oracle table. Using just a couple of the document fields and the universal id as a primary key. This way we can link the search result document back to domino by calling the getDocumentByUNID method.
Now the search is superfast.
This is a beautiful marriage between Domino’s integrated desktop collaboration with Oracle robust web performance.
Subject: RE: Is the datbase full text indexed?
Hi Byju, ( is that a silent J? like ByU Joy ?-- cool name)The database is fully indexed. The OC4J ( the oracle web server) merely does what any other web server would. Just simulates domino httptask by communicating to the notes database thru iiop. I search the document collections that I get out of the databse.search using a view selection formula.
i.e databse.search(“Form="someform" & @contains(somefield;"something"”)
Is that what you meant by a “Lookup” ? or a lookup on a physically stored view?