Performance Tuning for Content Manager

Chapter 12. Troubleshooting performance problems 371

12.4.2 Rewrite XQuery

If you write the XQuery yourself and it is causing performance problems, you can

rewrite the XQuery. There are many technical notes about how to rewrite the

XQuery. You can find them in the IBM Content Manager support Web site:

http://www.ibm.com/software/data/cm/cmgr/mp/support.html

In this section, we describe some ideas based on some of these technical notes.

The generated SQL can vary depending on your exact original XQuery string.

Small differences in the original XQuery can yield differences in the SQL that can

cause the database to choose a different access plan.

In one scenario, users experienced poor performance when they ran a query to

return an item that included conditions about both the root component attributes

and child component attribute. Aside from maintaining the database, such as

making sure the database statistics were current for the database, we also tried

to rewrite the XQuery.

For example, the original query was:

/Journal[@Title LIKE "XML%" AND ./Journal_Article/@Author = "Kevin"]

That query exhibited poor performance when there was a large amount of data in

the system. We tried rewriting it as follows:

/Journal[@Title LIKE "XML%" AND(@ITEMID =

/Journal/Journal_Article/[@Author = "Kevin"]/@ITEMID)]

Although the results of the query will be the same, the SQL generated for this

query differs from the SQL generated to perform the original query (assuming

that versioning is turned off). This causes the database to choose a different plan

for execution.

You can do the same thing when versioning is used by adding conditions to the

query. For versioning, use this query:

/Journal[@Title LIKE "XML%" AND(@ITEMID =

/Journal/Journal_Article/[@Author = "Kevin"]/@ITEMID) AND (@VERSIONID =

/Journal/Journal_Article/[@Author = "Kevin"]/@VERSIONID)]

In general, avoiding redundant traversals is one of the ways to improve query

performance greatly.

For example, consider a component-type hierarchy within an item type, where

the Person component type is the parent of another component type, Address.

Assume that the query needs to find addresses of single or married persons in

California whose annual income is more than $100,000.