Understanding the difference between where and select in Rails
Tags: rails, sql, performance, beginners
Published: 2021-03-01 16:10:47

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/xjgvunp7s146ww4nb081.png)

In [my previous post](https://dev.to/junko911/rails-n-1-queries-and-eager-loading-10eh), 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 two 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`](https://apidock.com/rails/ActiveRecord/QueryMethods/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`](https://apidock.com/rails/ActiveRecord/QueryMethods/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](https://ruby-doc.org/stdlib-2.5.0/libdoc/benchmark/rdoc/Benchmark.html#method-c-measure),  which can measure the time used to execute code.

Let's retrieve `users` who are active by using `.select`.
```ruby
# console
Benchmark.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.
```ruby
# console
Benchmark.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](https://ruby-doc.org/stdlib-2.6.3/libdoc/objspace/rdoc/ObjectSpace.html#method-c-memsize_of), that can show the consuming memory size of an object.
```ruby
# console
users_with_name_select = User.select { |user| user.active }
```
The `memsize_of` method of ObjectSpace helps to get the size of an object in bytes.
```ruby
# console
ObjectSpace.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.
```ruby
# console
users_with_name_where = User.where( active: true )
```
```ruby
# console
ObjectSpace.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.