LevelUp 6: Calculates mostly everything in the database, knows why

gistfile1.txt

1. Explain why we prefer to calculate everything in the database.

It is common to summarize data from the database by collecting all the objects and performing the summary in Ruby code. But this leads to poor performance as needless data and objects are manipulated. Building ActiveRecord objects are expensive, and by writing the direct SQL, you'll get back plain Ruby Arrays, which is much less expensive. In addition, you are in control of the SQL you write, instead of letting ActiveRecord automatically generating it for you.

2. Give an example of a common operation in Rails that might be nonperformant if calculated at the app level, and then show how to calculate it properly using the database.

- Example 1: Let's say we want to get all the ids from an ActiveRecord model. The non-performance way would be to use #select from the Model:

User.select(:id)

This will construct an ActiveRecord:Relation object, which is relatively expensive because the objects need to be built.

We can instead use #find_by_sql and write make the query directly in SQL:

SELECT "users"."id" FROM "users"

Instead of ActiveRecord objects, you will get an array, which is much less expensive in terms of performance.

- Example 2: Returning too much data (User.all.map(&:id)) is a big thing that happens. We can fix it with Rails through User.pluck(:id). A bigger issue is usually stuff like User.loans.length, which can be replaced with User.loans.count. In cases like this, we can still leverage the power of Rails, but we need to shift the summing, counting, plucking etc to the database, rather than returning large numbers of entire records.