Retrieving data from a Documentum Repository using DQL is a common and robust process with Documentum. With addition of the xPlore search engine, there are some challenges that experienced DQL users may find unique. This post will discuss our experience with DQL and xPlore.
Searching in DQL
DQL queries are quite familiar to most Documentum Administrators. A modified SQL like langauge allows for administrators and developers to write queries for simple retrieval of metadata to more advanced calls for Documentum features such as executing methods or granting privileges. For a typical DQL statement without xPlore, Documentum converts the DQL statement to proper SQL (for the underlying database), and executes it against the database to returns the results.
Verity and FAST (Full Text Search Before xPlore)
Older implementations, Verity for Documentum 5.2 and FAST for Documentum 5.3 through 6.5 implemented a similar conversions to a language that the underline engine could interpret. FAST was ultimately replaced by xPlore since the performance, scalability, cost-of-ownership, and quality of search was far superior in xPlore.
Searching in Full Text (xPlore)
With the introduction of the xPlore index, there are now 2 places a DQL query can be run, first against the database like a traditional DQL statement, or converted to XQuery and run directly against the xPlore index. In addition to adding the obvious benefit of full text search capability, the relevancy, performance, and scale-ability are far superior to the DQL / SQL approach.
Issues with full Text DQL
By default, when a DQL query is executed, the DFC query builder uses the hint TRY_FTDQL_FIRST, meaning the full-text constraints run against the xPlore index and the attribute constraints run against the database. When all the constraints of the query can be fulfilled by the full text index, the query will perform great, but when it needs to divide operations between the index and the database, major performance problems can occur. We have seen simple queries on small repositories take as long as 30 seconds to return due to the inefficiencies in how the query tasks are parsed, divvied up, returned, and merged.
The DQL Quick Reference Guide details what DQL features do not run against xPlore, meaning the query will need to divide its efforts between xPlore and the database. It is quite shocking that some of the most basic functions of DQL made the list. Some of these items include:
- Repeating attributes in the WHERE clause
- keywords BETWEEN, NOT LIKE, IN, EXISTS
- UNION, GROUP BY, or HAVING clause
- Any valid form of the FOLDER predicate
Solutions to Performance Issues
The good news, is that if your application is experiencing performance issues related to DQL queries, this can be fixed by implementing the xPlore API. Documentation can be downloaded from Download center – https://emc.subscribenet.com/. The xPlore API has all the functions necessary to execute a query native to xPlore which will provide the best performance possible.
If implementing the xPlore API is not an option, the next best thing is to test if the query is xPlore compatible. This can be done by adding an “ENABLE (FTDQL)” to the end of the query. If the query completes successfully, it is running fully in xPlore. If it fails with an error [DM_QUERY_E_QUERY_IS_NOT_FTDQL] then you have used a notation that is not supported by xPlore. Re-writing the query to conform to the FTDQL limitations will optimize the performance of the queries execution.
Finally as a last resort to improve performance, there is tuning both for xPlore and DQL. DQL tuning is best performed by a DBA on the database level. If indexes can be added to the database (large audit trails are often a culprit) this can greatly improve a slow query. For xPlore, EMC provides details to look into tuning performance from many apsects of the implementation: https://community.emc.com/docs/DOC-19053
Please share your thoughts below
TSG Blog 
ORDER BY clause is also not supported by xPlore. It automatically sees it as NO_FTDQL and pass the query to the Database. But EMC, did said that xPlore version of 1.3 to 1.4 has now a feature to support this in DFS Search Service (OrderByClause) but only from DFS/DFC 7.0. So when you upgraded your environment you need to write your own DFS code to generate correct order by expression in xQuery.