Performance comparison between SQL cache and variables in Rails, 7000 times difference

Overview

It is much faster to use variables than to use SQL cache.
For example, in the display of one user's information, we don't think the performance is at a level to be concerned about.
If you want to display a lot of the same data when displaying data like a table, it is better to put it in a variable properly.

First things first

There is a useful mechanism in Rails ActiveRecord that caches the same SQL over and over again, so you don't have to access the DB each time.

This feature is quite useful and the performance doesn't degrade even when programming poorly. It's hard to drop, but it seems to have an effect when high performance is required and a lot of data is displayed and created.

So I benchmarked using SQL cache and using values stored in variables as usual.

Check logs to make sure SQL cache is working

If the cache is not working, the log is issuing SQL like this

User Load (2.0ms) SELECT "users". * FROM "users" WHERE "users"." id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]]

The log when the cache is active has a CACHE in the head like this

CACHE User Load (0.0ms) SELECT "users". * FROM "users" WHERE "users"." id" = $1 LIMIT $2 [["id", 1], ["LIMIT", 1]]

Prerequisites.

Rails 5.1.4
Ruby2.5.0

Measurements

Call the User model that is linked to DB and measure it 10 times or 1000 times with the benchmark function.

When using the SQL cache

@user = User.find(1)

User.benchmark("SQL cache") { 10.times { User.find(1) }
SQL cache (7.9ms)
User.benchmark("SQL cache") { 1000.times { User.find(1) }
SQL cache (705.2ms)

To use a value stored in a variable

@user = User.find(1)

# If you just want to pass a reference
User.benchmark("plane val") { 10.times { user = @user }
plan val (0.0ms)
User.benchmark("plane val") { 1000.times { user = @user }
plan val (0.1ms)

# If the value is duplicated and passed (to a different object)
User.benchmark("plane clone val") { 10.times { user = @user.clone }
plane clone val (0.1ms)
User.benchmark("plane clone val") { 1000.times { user = @user.clone }
plane clone val (1.6ms)

Conclusion.

The results show that it is overwhelmingly faster to use variables
I measured it crudely.

• 7052 times faster if you use it by reference.
• 440 times faster in the case of duplication.

It turns out that

As a piece of advice to junior-level programmers, I often tell them to program with memory, CPU, file loading, database, and network in mind, and I get half laughed at, but this result shows that if you want to do something at a level that requires performance, the aforementioned line is important. Isn't it supposed to?

Aside

The cache probably works with the cache settings in the configuration file, but changing the settings between the file cache and the memory cache didn't make a difference in performance. If you know anything about this area, please comment!

config.cache_store = :file_store

config.cache_store = :mem_cache_store

Reference.

By the way, it is said that it is 200 times faster when it measures the case where SQL Cache is not used and the case where it is used.
https://gist.github.com/ololobus/785f1005da29850d6a85

Rails is easy to benchmark.
https://qiita.com/akishin/items/cf74559875cf475bbf66
Translated with www.DeepL.com/Translator (free version)