Performant Database Access with Relate

Mark Siebert

Reading time: about 3 min

Topics:

  • Database
  • Open Source

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!

About Lucid

Lucid Software is a pioneer and leader in visual collaboration dedicated to helping teams build the future. With its products—Lucidchart, Lucidspark, and Lucidscale—teams are supported from ideation to execution and are empowered to align around a shared vision, clarify complexity, and collaborate visually, no matter where they are. Lucid is proud to serve top businesses around the world, including customers such as Google, GE, and NBC Universal, and 99% of the Fortune 500. Lucid partners with industry leaders, including Google, Atlassian, and Microsoft. Since its founding, Lucid has received numerous awards for its products, business, and workplace culture. For more information, visit lucid.co.

Get Started

  • Contact Sales

Products

  • Lucidspark
  • Lucidchart
  • Lucidscale
PrivacyLegalCookie privacy choicesCookie policy
  • linkedin
  • twitter
  • instagram
  • facebook
  • youtube
  • glassdoor
  • tiktok

© 2024 Lucid Software Inc.