written by PrimeHammer

Playing with “joins” in ActiveRecord

On many occasions we need to load multiple records with associations from a database. The easiest way is to access this is via iteration (.each) written in Ruby. This is not the best approach as it can result in issuing 1+n queries (complexity O(n)) instead of single query (O(1)).

Using iteration isn’t optimal because as the count of records increases, the rendering of the page gets slower and slower, and can easily result in 30 second to 5 minute rendering times.

Our Example Models

To show the code in action, I’ve created a database of items and item sets from World of Warcraft.

ishael_bloodlight_by_alexgarner-d54fso0

preload()

preload() executes new SQL statement for each association we reference.

However you cannot use the WHERE condition in the association.

The biggest advantage of loading all associations with a single query is there is no need to execute SQL when we iterate over the associated records. This applies to the following methods as well.

eager_load()

eager_load() uses always LEFT OUTER JOIN to load the association. The same query works here.

joins()

joins() can take two types of parameter. Joins() with symbol as a parameter does always INNER JOIN:

The second type of parameter is a custom string. You should use this only when absolutely necessary. You can perform LEFT OUTER JOIN for example.

includes()

The easiest way to preload association(s) is to use the includes() method.

In the next example we add constraints to ItemSet. This works well as expected, loading the associated Item as well.

Each argument in includes() is an association, this means it’s easy to include multiple associations. In this example, ActiveRecord issues three queries (one for the model and one per each association).

includes() uses internally preload() or eager_load(). It depends on whether you use references() or not (see the next section). You can ask – why not to use LEFT JOIN every time? Using the WHERE…IN statement is faster than “joins.”

references()

Perhaps you’ve seen the references() method used along with includes(). Our goal is to get items from The Gladiator set with armor greater than 22.

OH NO! ;-( We need to tell ActiveRecord that we reference included table which changes the query from two SELECTs to LEFT JOIN.

Now ActiveRecord did some crazy LEFT JOIN to prefetch the data. It does LEFT JOIN for every argument in includes().

Conclusion

I’ve shown you the most efficient ways to use preloading data from associations in Ruby instead of writing SQL statements manually. The presented methods are more elegant, maintainable and less error prone. However, I urge you to employ the methods in your application for yourself to see what works best for you.

References: http://blog.arkency.com/2013/12/rails4-preloading/ http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html Ishael Bloodlight from DeviantArt