Home:Learn XQuery:XQuery Tutorial:Performance Tips:Preparing Queries

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).

To parameterize a query, use external variables to change the values used for the query each time it is invoked. For instance, the following query creates a portfolio for a given user:

Example 1. A Query with an External Variable

declare variable $user as xs:string external;
collection('holdings')/holdings[userid=$user]

In this query, $user is an external variable which must be bound before executing the query. The following XQJ code shows how to prepare the query and bind a value to $user.

Example 2. Preparing a Query

// Get a connection, prepare the query

XQDataSource dataSource = new DDXQDataSource();
dataSource.setJdbcUrl("jdbc:xquery:sqlserver://server1:1433;databaseName=stocks");
XQConnection connection = dataSource.getConnection();
XQPreparedExpression preparedExpression = connection.prepareExpression(xqueryText);

// Bind variable $user to 'Jonathan' and execute

preparedExpression.bindString(new QName("user"), "Jonathan");
XQSequence xqSequence = preparedExpression.executeQuery();

The following code shows how to bind $user to a different value and execute prepared query again.

Example 3. Executing a Prepared Query with New Values

// Bind variable $user to 'Minollo' and execute

preparedExpression.bindString(new QName("user"), "Minollo");
xqSequence = preparedExpression.executeQuery();

Benchmarks that measure the speed of a query should mirror the usage you expect in your program. Since most programs execute a query many times, when you design a benchmark you should prepare your query, then execute it many times.