Tuesday, May 31, 2016

HSQLDB

HSQLDB

It's been almost two years I've been using HSQLDB in one of my client-critical application.
When I was given the task of working on this application (which was already in production), main challenge was to reduce latency and also to make it flexible enough to accommodate any future use-cases.

Without going into to much details, here's my use-case:
  • Application receives data from multiple sources
  • Parse data from each source and extract required data
  • Aggregate this extracted data and find best candidate which satisfies certain rules

One simplest solution is to join data from all sources by having a (for-)loop, one each for a source and apply rules inside inner-most loop. As number of data sources increases, so does the latency.
HSQLDB came to my rescue. I used in-memory variant, as the data is non more required, once a candidate is found.

Data extracted from each source is stored into DB (from here on, DB => in-memory HSQLDB), wherein we have one table each for a data source.

Here come the best part.
Now all my rules for computation are mere SQL queries. Instead of going through the code, one can easily understand the rules by looking at this SQL queries.
By adding proper indices, which can be done by simple annotations, we can perform any complex query in almost no time (subject to the size of data).
It also comes with one more added benefit, flexibility. We can add/modify rules with almost no effort.

HSQLDB has a huge community and all your queries, if any, would have been already answered.

Please share your comments/queries below.
Will add samples and mocks, if required.