Home:Learn XQuery:XQuery Tutorial:Performance Tips:Avoid Compensation in WHERE Clauses

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 can not take advantage of indexes and other optimizations available in the database server. This can dramatically hurt performance.

The DataDirect XQuery® User's Guide and Reference clearly states which expressions, functions, and operators are compensated. Most of the commonly used functions that are compensated fall into the following categories:

When querying relational data, if a compensated function or operator is used in the WHERE clause of a FLWOR expression or in a predicate of a path expression, performance can suffer dramatically. For instance, in the following query, normalize-space() is compensated, and forces evaluation in memory; since this function must be called once for every row, the performance hit is very significant.

     for $h in collection('stocks.dbo.historical')/historical 
     where normalize-space($h/ticker) = 'AMZN' 
     return $h
            

One way to avoid compensation is to use a SQL function instead of a compensated XQuery function, as in the following example.

    declare function ddtek-sql:rtrim($in as xs:string) as xs:string external;

    for $h in collection('historical')/historical 
    where ddtek-sql:rtrim($h/ticker) = 'AMZN' 
    return $h           

When querying relational data, using numeric predicates or an AT clause in a FLWOR expression forces compensation, which can dramatically impact performance, especially when it forces a join to be done in memory. Consider the following query.

     for $user at $i in collection("users")/users,
           $hold at $j in collection("holdings")/holdings
     return
          <holding>
              <userid>{ $i }</userid>
              <user>{ string($user/userid) }</user>
              <stockid>{ $j }</stockid>
              <stock>{ string($hold/stockticker) }</stock>
              <share>{ string($hold/shares }</share>
          </holding>                
            

This query uses AT clauses to identify the individual users and stocks, and these AT clauses force DataDirect XQuery® to do the joins in memory instead of letting the database engine do them. Not only is this a dramatic performance hit, it's also a bad way to identify the users and holdings — because rows in a relational table do not generally have a guaranteed stable order, these identifiers might change each time the query is run. It is generally much better to use the primary key from a table to identify the rows of that table.

 (:-------------------------------------------------------------------
  :  userid is the primary key for collection("users")
  :  stockticker, userid is the primary key for collection("holdings")
  --------------------------------------------------------------------:)
                
     for $user in collection("users")/users,
         $hold in collection("holdings")/holdings
     return
          <holding>
              <user>{ string($user/userid) }</user>
              <stock>{ string($hold/stockticker) }</stock>
              <share>{ string($hold/shares }</share>
          </holding>