Optimizing Sequelize Queries

Trevor Jung
Mar 25 · 5 min read
Image for post
Image for post

The Problem

Whenever you start to see an obvious slowdown on the front end, you know something somewhere is lagging. When you’re working on a full stack application, that could mean anywhere. Do we check our React components first, our actions and endpoints, or our database queries or indexes? Could it even be a DevOps issue or something with our content delivery network? Does our Docker container need more resources?

These are the questions I started to ask myself as I went down the rabbit hole of trying to optimize AdBuilder. AdBuilder is a design program used to build advertising creatives here at GumGum. It’s used by our design, operations and sales teams as an automation tool to build and serve ads faster. A user can construct one of our ad units by uploading assets, resizing and dragging those assets, adding animations, and finally, exporting HTML markup. Over the years, it’s grown from a prototype to a full-scale application, and as we’ve continued to add more and more features, the application has felt it.

The Investigation

AdBuilder is a full stack application with a React front end and Node.js back end. We also use an ORM called Sequelize to help manage our interactions with the database.

Initially, our Sequelize queries were quite simple because our functionality was pretty straightforward. Many requests were to a single table without any nested data:

Image for post
Image for post

But as the application functionality grew, our queries became much more complex. We needed to add associations which came with associated data and eventually culminated in the example below:

Image for post
Image for post

We first started to notice how much the application had slowed down when testing locally became cumbersome. Every refresh of the page or firing a request took far too many seconds, almost to the point of affecting our development time. On top of that, in production we were getting user reports of the application getting hung up on particular actions involving asset groups, but in testing that query locally, everything seemed okay. Seeing this latency only in production made this difficult to track down as every time we wanted to try a fix, we had to push and trigger a build to test.

Image for post
Image for post

After a few tests with no out of the ordinary results, we decided to throttle the network and it was then we were able to get a better grasp on what was causing the largest hold up. We discovered our TTFB (time to first bit) for a few endpoints was abnormally large, as seen above. Some of these requests were taking 3 full seconds just waiting for the response, and typically anything greater than 1 second can be optimized. This is when I started to question if Sequelize could be our issue.

Digging deeper, I discovered other queries with issues, and they all also used the include parameter to get data from other associated tables.

Image for post
Image for post

When using this parameter, Sequelize combines associated table queries with join statements. Although easier to work with, since the associated data would be nested together in one response, this came at a cost. When using the include parameter to combine queries from different tables, and even nesting other queries inside those, this essentially created one very large query all stringed together by joins. In addition to this, adding tables with has-many associations to our include resulted in duplicate rows which would have to be de-duplicated and then parsed into the models.

The Fix

To solve this challenge, Sequelize has another parameter called separate. Sequelize actually has little to no documentation about what exactly the setting does. It was only from scrolling through articles and similar issues, I noticed several mentions of this obscure parameter. It ended up however, that this separate parameter was crucial in optimizing complex queries where you want to include associated nested data.

Image for post
Image for post

It’s only available for many-to-many associations, but as its name suggests, it takes those previously nested queries and performs them individually or separately. As a bonus, the results from each query are joined together later in memory, so we were able to maintain the same response and not have to alter how we were setting the data.

What this meant for our situation: we were able to decouple our queries, perform them separate from one another and get a huge boost in efficiency. Measuring the before and after performance of a few endpoints, we estimated a 10x improvement. One of our GET endpoints went from 2.3 seconds to just 200 milliseconds. We were also able to target other queries with similar methods and associations and gain performance optimizations there as well.

Going Forward

Now this isn’t to say Sequelize was the only culprit in the latency we experienced, or that ORMs in general aren’t a good tool to use. Another section of our application used for admin functionality also saw a huge delay in requests. This section had originally been built using other libraries, Epilogue and Finale. After replacing them with Sequelize directly, we gained a ton of performance improvements. On top of that, using Sequelize allowed for a much easier refactor of an area of our application that hadn’t been maintained in quite a while.

As with all architecture choices, there’s no one size fits all. Decide based on your requirements and see what works best. Just be prepared to look behind the scenes and keep discovering.

We’re always looking for new talent! View jobs.

Follow us: Facebook | Twitter | | Linkedin | Instagram

gumgum-tech

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store