Lessons learned and best practices

---

General query guidelines

Use of (A | B | C): Use of (A | B | C) does not work after the reference operator. Instead, use union. For example, /O => A | B | C does not work; use: /O =>A union /O=>B union /O =>C

Length of query: The power of the Content Manager query language allows complex searches of Content Manager data. Sometimes these complex searches do not map naturally to SQL. In these cases, the generated SQL queries are longer and more complex than the limit set in the database. In most cases rewriting the XQuery to generate an acceptable SQL query was possible. In some cases it was necessary to split the query into multiple queries to reduce its length/complexity to an acceptable value.

Platform matters: When working with a long query, do not assume that queries that meet the query length requirement in one platform meet the requirements of another platform. Test queries on all platforms before releasing to production

Use of reverse reference: Be careful when there is a need to use reverse reference. The use of reverse reference 'REFERENCEDBY/@REFERENCER' provides flexibility in queries. However, the query syntax does not allow specifying the reverse attribute name. If there are multiple reference attributes with the same type, the reverse traversal applies to all reference attributes in the component type and cannot be limited to a single reference attribute.

Query optimization

Turn off access control to improve query performance: In general, if the Content Manager built-in access control is not used, turn off the default access control. In this way the generated queries do not contain the block of conditions in the SQL statement to check if the user has the permissions to access data on the target item types. We achieved this by setting ACL binding level to library and setting public access off.

Use of syntax 'in' performs better than 'or': If the search condition involves 'or' for the same attribute, use of 'in' performs better than 'or'. For example, A in (1, 2) is better than A=1 or A=2.

Use of union performs better than 'or': If the search condition involves 'or' for different attributes on child components, given that the length of query is not an issue, use of union performs better than 'or'. It depends on the query optimizer. In our case, the query with 'or' caused a table scan and the query with union seemed to use indexes and return rather quickly.

Object access

Retrieve options: There are different options for retrieve operations, ranging from only PID to complete component hierarchy. While certainly flexible, the feature can cause some confusion because the retrieve option is not persisted with the DKDDO. That means by getting a DDO from another module the code cannot tell if the object is fully or partially loaded. We informed the Content Manager developers of this finding for consideration as a future product enhancement. They suggested a temporary work around. You can also introduce some custom attribute to indicate the retrieve options so other developers know if data is complete enough for their use. In our case the minimum retrieve option is used in certain situations to minimize the performance implication, and the more complete option is used where details are required.

Later versions of Content Manager introduced a special property on the DKDDO that is automatically populated with the retrieve option when a retrieve is done.

Updating child components: Two approaches can be taken to update child components:

• Delete and add all child components

• Find and update

While the former is easier to implement, the latter usually performs better because of fewer database transactions. More specifically on the find and update approach, the program has to keep track of updated, deleted, and inserted components, and then perform appropriate actions on the DKChildCollection. Note that DKChildCollection does not expose a remove method, so we get a dkIterator from the DKChildCollection and remove objects from the iterator.

Transactions

Use of transactions: Content Manager explicit transaction support is used to ensure multiple updates are committed or rolled back together. The general guideline is to make the transaction short so that items are locked and then quickly released to improve data availability.

DDO checkout: To update, the DDO has to be checked out internally by Content Manager. Usually it is checked in after the object is updated successfully. If anything goes wrong between the checkout and checkin code, the object remains checked out. We found Content Manager transactions can help to resolve such issues. We put the checkout code inside a transaction so that when the transaction is rolled back due to any failure, the checkout is also rolled back. Without this technique, you need to constantly query the checkout status and react accordingly in most update situations.

Update locking: In the customer data model, the system configuration item type has a 'NextItemId' attribute that is managed by an ID generator. Content Manager locks a parent item when a referenced item is being updated. It causes a deadlock during updates because one thread (the main thread) is in a transaction updating the referenced item while the other thread (the ID generator thread) is trying to update the referencer item. The solution is to decouple such item types, that is, to remove the reference attribute, to avoid the deadlock.

Connection pooling parameters

Minimum number of connection pool size: The Content Manager default value is 0, which means no minimum connections are kept in the pool, and connections are created as needed. To better reuse connections, use a small number, such as 5, so Content Manager does not create and destroy connections all the time.

Maximum number of connection pool size: The Content Manager default value is 0, which indicates the pool can contain unlimited number of connections. When there are many concurrent users, the system resource can be used up quickly. Use a reasonably large number depending on the system capability and user scenario, such as 40, so Content Manager does not use excessive system resource and most users can have connection concurrently.

Datastore pooling parameters

Connection timeout parameter: This is the number of minutes a datastore connection should remain unused in the pool before the datastore is disconnected and destroyed. The Content Manager default value is 0, which means no limit, meaning an unused connection can possibly take valuable system resource and never be reused. Use a reasonable number, such as 10, so Content Manager will reclaim a datastore connection if it is unused for ten minutes.

---