Home:Learn XQuery:White Papers:Generating SQL

DataDirect XQuery®™ Performance: Generating SQL

Marc Van Cappellen

Implementing XQuery efficiently for relational databases is not trivial, because XQuery and SQL support different operations on quite different data models. At DataDirect, we emphasized performance and scalability in the design of our XQuery engine from the beginning, with a strong focus on relational data. This paper presents some of the techniques we use to generate efficient SQL for relational databases to implement XQuery for these data sources.

These techniques work, and give us better performance than other XQuery implementations. Some XQuery implementations we have tested return an entire table for queries where we return only part of a single row. Others generate the same SQL regardless of the database involved, a strategy which simply can not offer good performance. Some XQuery implementations rely on the least-common-denominator functionality of the least capable JDBC drivers, which limits performance significantly. Some XQuery implementations perform most XQuery functions in the XQuery engine instead of evaluating them in the database.

DataDirect is the leading vendor of database connectivity software, and we know how to measure performance and scalability. We have spent many man-years developing extensive XQuery performance test suites, and we run these suites regularly as part of our standard development cycle. When our support staff identifies interesting customer performance scenarios, these are added to our performance test suites. In our testing, we have been pleased to find that applications written using DataDirect XQuery®™ generally perform better than equivalent applications written using SQL, JDBC, and an XML API. Not all XQuery implementations can offer that kind of performance.

In this paper, we will explain some of the techniques we have developed, showing the SQL that DataDirect XQuery® generates for a number of specific XQueries. If you want to see the SQL generated for one of your queries, you can do that using the profiler provided by your database vendor. If you are comparing XQuery products, comparing the SQL they generate can help you understand how these products will perform. Most of the generated SQL shown in this paper is for Oracle 10gR2 — SQL generated for other databases may look significantly different.

Table of Contents

Overview

Before we explore individual queries, let's take a high-level look at the techniques we use.

Selecting Data

To minimize data retrieval, DataDirect XQuery® generates very selective SQL, returning only the data that is needed for a given XQuery.

Quantifiers

Another way DataDirect XQuery® minimizes data retrieval is by implementing quantified expressions with SQL in the database.

Joins

Relational databases are designed to optimize joins, so DataDirect XQuery® leverages the database when an XQuery join involves SQL data.

Sorting Data

DataDirect XQuery® leverages the database for sorting, because the database can leverage indexes to sort much more efficiently.

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.

External Variables

XQJ prepared queries are analogous to SQL prepared statements; they use XQuery external variables in the same way that SQL uses parameter markers.

XQuery Global Variables

SQL does not have a construct equivalent to XQuery's global variables. DataDirect XQuery® generally inlines the variable in the statement created for the query body.

XQuery Built-in Functions

XQuery has a large library of built-in functions, which are all supported by DataDirect XQuery®. The vast majority of these are translated into SQL.

User-declared Functions

DataDirect XQuery® uses intelligent function inlining so that user-declared functions can be executed in the database.

Calling Database Functions

DataDirect XQuery® lets you call any function defined in your database directly as an XQuery external function. Both built-in database functions and user-defined SQL functions are supported.

Consistent SQL Generation

In XQuery, many queries may be semantically equivalent. DataDirect XQuery® recognizes common equivalences in queries and ensures that efficient SQL is generated.

Relaxing XQuery Semantics

When translating XQuery to SQL, DataDirect XQuery® is careful to preserve XQuery semantics, even if this complicates the generated SQL.

Summary

DataDirect XQuery® was designed for performance and scalability, which are essential in business-critical applications.