2.9.6 Prepared Queries

WARNING: the prepared function cache still makes use of the old compiler backend and does not use the optimizing algebraic query compiler. Therefore, its query performance can sometimes be inferior to other queries handled by MonetDB/XQuery. Also, as the old compiler backend is gradually phased out, it gets to be less well-maintained and tested in general. Use with caution. We hope to port XRPC to the algebra backend soon.

In SQL databases, interfaces like ODBC or JDBC allow to prepare prepared queries for faster execution of frequently used query patterns. For example, web-sites powered by a database usually generate a fixed set of queries to that database; each page fires off the same (set of) queries, possibly though with different parameters (that contain e.g. a customer identity or cookie). SQL systems can prepare for such prepared queries in advance, such that when a web-page is served out and the query result is needed quick, only the actual parameters need to be fed (bound) into a ready-to-run query (saving query parsing and optimization time, which is unavoidable for ad-hoc queries).

Such prepared queries can be seen as a queries whose result is a function of a number of parameters. MonetDB/XQuery takes this definition literally in its implementation of prepared queries:

prepared query = XQuery function

XQuery modules allow to define XQuery functions (and variables, but that's beside the point now). So:

• if a XQuery consists of only a function call, and
• that function is defined not inside the query itself, but inside a module (so there's an import module statement preceding the function call), and
• the query parameters are simple constants (atomic values of type xs:integer, xs:decimal, xs:double, or xs:string)

To put the caching mechanism to the test, first import the XMark document into the database:

mclient -lx
> pf:add-doc("http://monetdb.cwi.nl/xmark/auctions.xml",
             "http://monetdb.cwi.nl/xmark/auctions.xml")

then run the queries q.xq (standard), m.xq (function from module) while monitoring elapsed time:

  shell> mclient -lx -t q.xq
  1729
  
  Trans      18.000 msec
  Shred       0.000 msec
  Query       5.000 msec
  Print       0.000 msec
  Timer      24.436 msec
  
  shell> mclient -lx -t m.xq
  1729
  
  Trans       0.000 msec
  Shred       0.000 msec
  Query       2.000 msec
  Print       0.000 msec
  Timer       2.778 msec

We see that the latter "prepared" query (that uses a function defined in a module) performs almost ten times faster than the ad-hoc query! The translation time has completely disappeared for the prepared query m.xq and the query execution also improved by a factor of two.

This performance trend was confirmed on the XMark benchmark. We developed prepared queries for the benchmark, which produced a factor 10 (small 116KB documents) to 2 (larger 10MB documents) performance increase over the ad-hoc benchmark queries. Obviously, complex queries or queries on huge (>=GB) documents, which take many seconds to run anyway, do not benefit from prepared query mechanism (but neither are hindered by it).

As a general rule, the benefit of canned queries is larger for short-running than for long-running queries, because for short-running queries the XQuery translation and optimization time weighs in more heavily. We especially recommend the use of canned queries when MonetDB/XQuery is used to power web-sites.