Best Practice when querying across DTREE

Gillian Robb

I have written a number of WebReports/LiveReports that query Dtree. The queries can be quite complex as I may have nested queries in order to select from a specific folder locations OR have multiple joins to LLATTR to bring back attributes OR both. This has raised concerns about impact on the database and therefore overall performance. Typical use for such queries would be: To display list of case folders to act as a basis for a WebReport for reporting purposes or to initiate workflows to amend the case; to get the node id of a folder in order that a workflow can add permissions; to allow the user to select a folder(node id) by searching on and displaying the attributes.Does anyone have any best practice or guidance available for querying DTREE?

Appu_Nair

I am not a sql expert but instead of querying llattrdata for defvern/defid the queries will be more understandable if you make the query joins against dtree to a view against dtree.Also the schema book says which indexes are on dtree so if you adhere to indexed ones it may run faster.The BI group of OpenText has written functions for Oracle and SQlserver that works similar to the view but is much more simpler to work with.I liked their approach when I was on a project when I coupled the BI tool to ContentServer.It should not be hard to write those kind of functions against llattrdata as well.My $0.02