Home:Learn XQuery:XQuery Tutorial:Performance Tips

Performance Tips for DataDirect XQuery®

DataDirect XQuery® was designed for performance and scalability, which are critical to our customers. This document describes some of the performance optimizations in our XQuery implementation and provides guidelines that will make most applications perform well. Some of these guidelines may seem quite obvious to some users — for instance, most JDBC programmers know they should prepare queries, and most XML programmers know that the DOM requires more memory and is slower than SAX or StAX. This document is designed to be helpful to both groups.

Preparing Queries

The first time a query is executed, a significant percent of the total query time is spent preparing the query, which includes query optimization and generating the query plan. Because most programs invoke queries more than once, we spend the time needed to find an efficient way to process the query, even though that makes the first query slower. Fortunately, this effort does not need to be repeated. One way to ensure that a query is prepared only once in your program is to use a prepared query. (Another way is to use Query Pooling, described in the following section.)

Query Pooling

Prepared expressions let the programmer decide when a query should be prepared and when a prepared query is no longer needed. In programs that use hundreds or thousands of queries, it is generally better to let DataDirect XQuery® keep track of which queries are actually being used, preparing them the first time they are executed, and discarding the least frequently used prepared queries if there are too many queries. This is called query pooling.

Querying Large XML Files

DataDirect XQuery® provides several ways to query an XML file. In DataDirect XQuery®, you will get better performance, especially for large XML files, if you use fn:doc() to access XML in a query. You will get much worse performance if you parse the XML to create a DOM tree, bind the DOM tree to an external variable, and query the external variable.

Retrieving Query Results

DataDirect XQuery® is carefully designed to allow very large query results to be retrieved efficiently, and to allow incremental instantiation of query results, so that memory can also be used efficiently. If your query's results can be large, stream the results to an OutputStream or use SAX or StAX, so that retrieving the results does not introduce issues with memory or performance. Retrieving large query results as DOM can use large amounts of memory and significantly increase the time needed for processing queries.

Avoiding Compensation in WHERE Clauses and Predicates

When a database is queried, DataDirect XQuery® usually pushes queries to the database, where they are executed in SQL. However, some expressions, functions and casts are not supported by SQL, and are therefore performed in program memory, a strategy known as compensation. Unfortunately, when these operations are performed in main memory, they cannot take advantage of indexes and other optimizations available in the database server. This can dramatically hurt performance.

Specifying SQL Generation Algorithms

The algorithms DataDirect XQuery® uses to create hierarchies are optimal for creating large, complex documents, but still good for smaller documents with shallower hierarchies. If query results contain four or fewer levels of hierarchy, and your performance seems inadequate, you will sometimes get better performance if you tell DataDirect XQuery® to use either outer join or sorted outer union. (See the DataDirect 2.0 User's Guide and Reference for details on these algorithms.) This section describes several ways to specify SQL generation algorithms in DataDirect XQuery®.

Summary

DataDirect XQuery® was designed for performance and scalability. It does sophisticated query optimization, supports prepared queries and query pooling, and efficiently queries large XML documents using document projection and streaming. If you follow a few simple guidelines outlined in this summary, your DataDirect XQuery® applications will usually have good performance.