Understanding the difference between where and select in Rails

In my previous post, I talked about how to solve the N+1 query problem in Rails to improve the performance of the application. There is another thing you should be aware of when filtering data.

When you want to filter a Model in Rails, you have 2 options: .where and .select. The bottom line is that we should always use .where rather than .select to improve the speed and save the memory of the application. Here is the explaination.

Difference in speed

How do .where and .select work?

.where

Active Record takes care of building the query to avoid injection attacks, and will convert from the ruby type to the database type where needed.

.select

This will build an array of objects from the database for the scope, converting them into an array and iterating through them using Array#select.

This means .select fetches everything from the database, converts them to Ruby objects, and then runs the given block to filter them, while .where puts together a SQL query, lets the database handle the filtering, and then fetches the filtered records. Because of the number of records transported from the database server to the web server, .where works faster than .select.

I experimented in a Rails application where I have 10,000 users. There is a great module in Ruby, Benchmark, which can measure the time used to execute code.

Let's retrieve users who are active by using .select.

# consoleBenchmark.measure{ User.select { |user| user.active } }       user        system    total (user + system)      real#=>   0.033274    0.120817          0.154092        ( 0.167964 )

These numbers displayed as output are the time taken by code to execute in seconds in four different ways,
the user CPU time, system CPU time, the sum of the user and system CPU times, and the elapsed real time. The unit of time is seconds.

Now, let's see what happened when I used .where. The result is quite surprising.

# consoleBenchmark.measure{ User.where( active: true ) }       user        system    total (user + system)      real#=>   0.001249    0.008157          0.009407        ( 0.022937 )

Difference in memory

The difference between .where and .select affects memory consumption as well. When you filter an array using .where, the actual query will not be executed till it is called for the object. If you use .select, it will instantiate everything right away. This is why .where takes less memory than .select.

There is another module in Ruby, ObjectSpace, that can show the consuming memory size of an object.

# consoleusers_with_name_select = User.select { |user| user.active }

The memsize_of method of ObjectSpace helps to get the size of an object in bytes.

# consoleObjectSpace.memsize_of(users_with_name_select) => 89712 

Now, let's try the .where method. As you can imagine, the difference in memory is significant too.

# consoleusers_with_name_where = User.where( active: true )

# consoleObjectSpace.memsize_of(users_with_name_where) => 136

Small differences in code make big differences in the performance of the application. It is good to know tools like ObjectSpace and Benchmark so that we can always keep track of how our applications perform.