Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

BLOG: Squaring the SQL circle

Chris Burke | April 18, 2013
As algorithmic trading takes hold in Asia, choosing the right underlying infrastructure becomes critically important. Chris Burke, director of Asia Pacific for Kx Systems, brings clarity to the complex and rapidly evolving world of databases, examines some of the essential building blocks, and argues that form must follow function.

From a design perspective, it is also a more elegant option - not to mention easier to maintain. It can even run complex analytics as the data is received. If a server is heavily loaded because of a large number of clients or complex queries, then the solution would be to set up another server with its own copy or a replica of the database, i.e. the data feed would then go to two or more machines.

Since the work involved in storing, accessing and updating NoSQL records is much larger, the volumes that any one machine can handle are much lower than for traditional databases. In fact, NoSQL databases are typically intended to be distributed over several machines, and so must manage distributed queries, load balancing, backups, replication and so on. Such databases typically have poor query performance.

For example, a join may need to read from several machines, and each read has to work with the internal structure of the key-value record. For this reason, some of these databases do not support ad hoc queries like joins, instead requiring custom routines for such data access.

NoSQL databases typically use a key-value store. Suppose a database is used for storing website orders. An order might be considered as a single transaction, but might encompass several items, special instructions and other meta data. Storing that in a traditional SQL database might require adding many rows to several tables. This obviously complicates the design, and in particular, would be troublesome for a distributed database.

However, in the key-value store model, an entire transaction would be stored in a single record, where the key might be the transaction number, and the value might be a list of items that correspond to the columns of a traditional database, but where each item in turn can have structured contents such as lists of parts, prices, timestamps and so on. Necessarily, the work involved in storing, accessing and updating such records is much larger than for records in a typical SQL database.

There's no doubt that NoSQL developments offer a great deal of potential when deployed in the circumstances for which they were intended. However, they are currently less well-suited to the demanding, time-series oriented applications required by financial market data. For the current needs of trading firms, SQL databases offer superior performance and cost, but this debate looks set to run and run.

 

 

Previous Page  1  2 

Sign up for CIO Asia eNewsletters.