Building XML Hierarchies

XQuery is all about XML, which is based on hierarchy and sequence. If an XQuery is to be fast, the XQuery implementation must handle hierarchy efficiently. Choosing the correct SQL algorithm is important, so our performance testing of DataDirect XQuery included a variety of algorithms. You can choose any of these algorithms for a given query.

The sort-merge join algorithm is an efficient way to obtain data for building hierarchies in all queries; in many cases it is the fastest, and it has very good worst-case performance, so this is the default algorithm in DataDirect XQuery. For each level of hierarchy, SQL result sets are generated using the same sort order. These result sets are then merged in the XQuery engine, which creates the XML structures that represent the hierarchy.

Example 8. Sort-merge Join

Using the sort-merge algorithm, the inner and outer FLWOR expressions are each translated into a SQL query, sorted by row id, and the results are merged in the XQuery engine.

XQuery

for $u in collection("USERS")/USERS
return
  <user name="{$u/LASTNAME}">{
    for $h in collection("HOLDINGS")/HOLDINGS
    where $h/USERID = $u/USERID
    return
      <holding id="{$h/STOCKTICKER}">{data($h/SHARES)}</holding>
  }</user>

SQL (for users)

SELECT ALL
  nrm5."LASTNAME" AS RACOL1,
  nrm5."ROWID" AS RACOL2
FROM
  "PEPPINO"."USERS" nrm5
ORDER BY
  nrm5."ROWID" ASC

SQL (for holdings)

SELECT ALL
  nrm5."ROWID" AS RACOL2,
  nrm9."STOCKTICKER" AS RACOL3,
  nrm9."SHARES" AS RACOL4
FROM
  "PEPPINO"."USERS" nrm5,
  "PEPPINO"."HOLDINGS" nrm9
WHERE
  nrm9."USERID"  = nrm5."USERID" AND
  LENGTH(nrm9."USERID") = LENGTH(nrm5."USERID")
ORDER BY
  nrm5."ROWID" ASC

In some cases, such as when the XML nesting level is limited to four or less, you get better performance using the outer join algorithm, which uses only a single SQL statement. However, when the XML nesting level is greater than four, this single SQL statement can be very complex, so this is not the default algorithm in DataDirect XQuery. The following query shows how to choose the outer join algorithm.

Example 9. Outer Join

This example shows how to use a declaration option to tell DataDirect XQuery to use the outer join algorithm when generating SQL for building hierarchies.

XQuery

declare option ddtek:sql-rewrite-algorithm "outer-join";

for $u in collection("USERS")/USERS
return
  <user name="{$u/LASTNAME}">{
    for $h in collection  ("HOLDINGS")/HOLDINGS
    where $h/USERID = $u/USERID
    return
      <holding id="{$h/STOCKTICKER}">{data($h/SHARES)}</holding>
  }</user>

Generated SQL

SELECT ALL
  RAREL3.RACOL3 AS RACOL3,
  RAREL3.RACOL4 AS RACOL4,
  RAREL3.RAOJ1 AS RAOJ1,
  nrm5."LASTNAME" AS RACOL1,
  nrm5."ROWID" AS RACOL2
FROM
  {oj
    "PEPPINO"."USERS" nrm5
  LEFT OUTER JOIN
    (SELECT ALL
      nrm9."STOCKTICKER" AS RACOL3,
      nrm9."SHARES" AS RACOL4,
      nrm9."USERID" AS USERID,
      2 AS RAOJ1
     FROM "PEPPINO"."HOLDINGS" nrm9) RAREL3
  ON
    RAREL3.USERID = nrm5."USERID" AND
    LENGTH (RAREL3.USERID) = LENGTH(nrm5."USERID")} ORDER BY RACOL2 ASC
Previous

Sorting Data 		HomeNext

External Variables