SQL Breaks Through For Tick Data in New STAC-M3 Tests



McObject® recently put its eXtremeDB® Financial Edition database management system (DBMS) through two rounds of testing using the Securities Technology Analysis Center’s  STAC-M3 benchmark suite, the gold standard for assessing tick data management performance.  The audited test results include new performance records, with speed increased by as much as 400% for certain queries, and completion of all 17 tests in 62% of the time of the previous record. Reports are available from the STAC Web site.

But if you’re not into the nitty-gritty of data management, you might miss a critical detail: these results were obtained using the SQL database programming language to store, retrieve and crunch market data. In contrast, past STAC-M3 implementations by McObject and others relied on low-level programming languages like C/C++ and q to query the DBMS. This breakthrough performance using SQL could open new doors for trading organizations seeking a technology advantage, because SQL is both widely used in enterprise IT circles – enabling companies to draw on a much larger pool of expert programmers – and it is also perceived as highly productive (compared to the low-level alternatives), enabling code jocks to crank out solutions in response to market opportunities more quickly than competing approaches.

SQL’s edge in productivity stems from its relatively high level of abstraction, including English-like commands. The abstraction is convenient, but comes at the price of more processing overhead, plus reliance on an optimizer that will consider different ways to carry out a given command, resulting in sometimes unpredictable response times. Thus, SQL has not been synonymous with the low latency required by trading systems.

What enabled eXtremeDB Financial Edition’s record-setting STAC-M3 results using SQL? First, the tests leveraged the DBMS’s ability to pipeline vector-based statistical functions from within SQL. Pipelining – or using eXtremeDB Financial Edition’s vector-based functions to create an assembly line of operations on time series data, so that the output of one function becomes the input for the next – keeps market data in CPU cache during processing. This eliminates latency-inducing handoffs that would otherwise occur as interim results move between CPU cache and temporary SQL tables created in main memory.

Second, the record-setting SQL-based STAC-M3 tests utilized distributed query processing, with tick databases partitioned horizontally (into 72 shards in one of the test runs, and 64 shards in the other) and query processing distributed across multiple servers, CPUs and CPU cores. This approach accelerates performance via parallel execution of database operations and by harnessing the capabilities of many host computers rather than just one. Distributed query processing is possible through hand-coding in low-level programming languages, but accomplished transparently with eXtremeDB’s distributed query processing of SQL.

Third, eXtremeDB Financial Edition’s SQL optimizer is rules-based, meaning that query execution plans are determined via rules established in advance by the developer/user. In contrast, most SQL DBMSs rely on a cost-based optimizer that must collect sample data, generate statistics and analyze thousands — sometimes hundreds of thousands — of possible execution plans. Use of a rule-based optimizer results in query execution that is faster, less CPU-intensive and more predictable. Cost-based SQL optimization would normally result in widely varying results (high “jitter”) between instances of the same query. But due in part to rules-based optimization, eXtremeDB Financial Edition achieved record-setting low standard deviation of results in the STAC-M3.

The importance of SQL in these benchmark tests cannot be overstated. In enterprise computing, SQL is by far the most widely used database language. Its suitability for use in time-sensitive trading systems enables organizations to tap into a vast pool of experienced developers. SQL’s relative simplicity shortens development cycles, lending a critical timing advantage in today’s capital markets.

This post has been provided by McObject. McObject is a sponsor at the upcoming Trading Show Chicago. The Trading Show Chicago is the premier strategic conference for quant, automated trading, exchange technology, big data and derivatives. For the full list of speakers, conference agenda, and to register, please click here to visit our website or click here to download the brochure.


Leave a Reply

Your email address will not be published. Required fields are marked *