Optimizing SQL Queries in a Rails API

Here are a few cautions and suggestions for improving load times from a Ruby-on-Rails API, based on what I experienced developing the back-end for the PLURView App.

For background, PLURView analyzes EDM artists’ audio features to create a color gradient describing their vibe. The back-end is Ruby-on-Rails, and the front-end is a React/Redux app.

Prime the app with a subset of your data

The PLURView data store rapidly grew from a handful of artists I manually added into a database with thousands of entries. Artists have related artists (a reciprocal relationship) and multiple subgenres.

In the beginning it wasn’t a huge problem to “splat” (*) out the entire dataset to see if the algorithm was returning sensible gradients for artists. Once I was north of 100 or so entries though, loading times became excessive.

On the front-end, Redux was calling the entire dataset into the store at page load. As the dataset got heavier, it became important to cull the amount of information to pull in for the initial view. I did this by reducing the first data call to only the list of artists coming to New York City in the near future.

Serializers are great- but not always

Ideally when I make an API call, I want to receive a discrete JSON object containing every relevant data point. ActiveRecord allows for this, with no opinion on whether your tactics are performant.

Between core artist information, related artists, and genres, there are several tables getting hit per artist entry. On the front-end it would be nice to call in some sort of a God-object containing everything I could ever want to know, but it’s taxing on the back-end. Especially when there’s little specificity to which data should load.

In earlier development it saved some time to describe the object in a serializer, but things got pretty messy pretty quick.

Eventually it made more sense to use serializers to reduce the scope of information coming from an API call, rather than expanding it. The models contain fields the front-end doesn’t need, so they don’t need to wind up in a response.

Oh CRUD! There’s more to routing…

Thinking of Index and Show routes specifically, every bit of data doesn’t need loading by default. In PLURView, it wound up making more sense to build more specific routes to ping on the back-end for extended data.

Artist relations and subgenre information are useful for discovering acts, but these data take up a bunch of rows per artist. For performance it made more sense to call them on demand, hidden behind a “more details” type of pattern with a chevron icon on the Artist view on the front-end.

Shameless plug

I’m available for hire! My stack is Ruby-on-Rails on the back-end with React and Redux on the front-end. Email me if you want to get in contact 🙂

Leave a Reply

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

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax

Mark Bello

Mark Bello