Performant Database Access with Relate

At Lucid, we deal with data—a LOT of it. Ever since we migrated our code to the Play! Framework, we’ve been leveraging the Anorm database access library provided by the framework. Being able to write our own SQL queries gave us a lot more control than using an ORM, and that was one of our favorite features. However, when our queries got big enough to involve 100k rows, Anorm’s query parsing started taking minutes to do, and it quickly became clear that Anorm’s focus on syntactic sugar, while nice, was not meeting our needs. The Lucid ecosystem eventually outgrew Anorm, and we needed something with better performance.

Introducing Relate

To fulfill our increased performance needs, we developed Relate, our own data access layer. We set out to create an easy-to-use API that maintained the inherent speed of using JDBC. As such, Relate is a thin layer over JDBC, abstracting away the pain of developing with JDBC, but keeping performance overhead to a minimum. Speed has always been one of our highest priorities and we’re happy to announce that Relate is pretty darn fast.

Our first set of benchmarks dealt with data retrieval and Relate’s parser. A simple select query that selects only one string column from a table of a million records illustrates Relate’s speed. Here’s a graph that shows the effects of running the same query 20 times with different result set sizes:

Selecting 10 bigint columns shows a similar trend:

The second set of benchmarks concerned data insertion. Once again, using Relate for inserting rows into the database produced significantly better times and growth rates than Anorm. Here’s a graph demonstrating inserting rows of 10 integer columns:

Inserting 10 columns worth of bigints and strings resulted in similar graphs:

Finally, we benchmarked updating rows in the database. Here’s the result of updating rows containing 10 int columns:

As you can imagine, we’re rather pleased with these results.

How we use Relate at Lucid

These statistics are nice, but how does that help us here at Lucid? Besides performance gains, Relate also provides nice utilities to make inserting data into a query simpler; Relate makes list insertion (such as for the IN clause or multiple insertion) easy. It uses Scala’s CanBuildFrom trait, so retrieving data as a list, map, or other collection is faster than ever. Best of all, using Relate in our existing Play! projects is a breeze. All we have to do is drop in the Relate dependency in our Build.scala file and begin writing queries using Relate.

Relate is open source, on Github, and accepting pull requests. Contribute to it. Fork it. Use it. We’re confident that when you get involved with Relate, you’ll be just as excited about it as we are!