How to avoid N+1 query using SQL views (materialized) in Rails application

#ruby #rails #tutorial #sql

Consider the way to reduce the queries when calculating the average values and find the place where to do it in the Ruby on Rails application. #

In the first part , we considered the solution with extracting logic to the separate class and implementing the Facade pattern. SQL views are another way for solving the N+1 problem when finding the average value in Ruby on Rails application.

The Problem Setup #

There is a Ruby on Rails application with models Film and Review. Every Review has its own rate value.

We can calculate the average rate of each film:

But when we need to show the average rates for the batch of films:

it will produce a lot of individual queries:

The issue is:

How to reduce the queries and where to do it?

Calculate average value on the database level #

Another way to reduce the number of DB queries is by utilizing SQL views. An SQL view is a searchable object in a database that is defined by a query. It has some features:

  • A view doesn’t store data (Some refer to views as “virtual tables”)

  • You can query a view like you can a table

  • A view can combine data from two or more tables, using joins, and also just contain a subset of information

This makes them convenient to abstract, or hide, complicated queries.

Our query is looking like:

Now we can hook it up to our Ruby on Rails application:

  • wrap our query in a CREATE VIEW statement

  • create the migration with raw SQL in it

But it’s not exactly a Rails way. There is a useful tool for dealing with the database views in Ruby on Rails.

*Scenic * gem *Scenic * gem" title="Link to this section">#

This gem gives us the ability to define migrations that create, update, or drop SQL views, just as we’re used to doing with regular tables in Ruby on Rails. By default Scenic gem supports PostgreSQL, but there are adapters for other databases.

Run in the terminal command:

rails g scenic:view film_rating

It will generate two files *db/views/film_ratings_v01.sql *and *db/migrate/[date]_create_film_ratings.rb. *In the first one, we will place the raw SQL query. The second file will contain the migration to migrate/rollback the creation of our view.

materialized: true — makes the view materialized. The difference is that such views save the result of the query to a cached/temporary table. When you query a materialized view, you aren’t querying the source data, rather the cached result.

Now we can create the related model and interact with it as with the usual Ruby on Rails model.

As soon as materialized views cache the underlying query’s result to a temporary table we have to control when the cache is refreshed. A refresh method can be called at any time to update the data.

Now, we will deal with the *FilmRating *in the controller

the query for getting average rates of all films will look like:

The pros of this way #

  • Convenience (all features of Active Record are available).

  • Easy to test.

  • Performance, all heavy lifting is done by the Database.

  • Aggregation values are precalculated in a case when the materialized view is used.

The cons of this way #

  • Control when the cache needs to be refreshed

Sergey Sviridov is a Software Engineer at JetThoughts . Follow him on LinkedIn or GitHub .

If you enjoyed this story, we recommend reading our latest tech stories and trending tech stories .