waza
waza

Reputation: 526

Does Rails Really Cache Database Query?

Does Rails actually cache the query result? The documentation says that same query will be never executed twice on the same request:

1.7 SQL Caching

The second time the same query is run against the database, it's not actually going to hit the database. The first time the result is returned from the query it is stored in the query cache (in memory) and the second time it's pulled from memory.

I did an experiment to proof that Rails actually cache the query:

  def test
    data = ""
    User.find(1).update(first_name: 'Suwir Suwirr')
    data << User.find(1).first_name
    data << "\n"

    User.find(1).update(first_name: 'Pengguna')
    data << User.find(1).first_name
    data << "\n"

    render plain: data
  end

If the result is cached, i would get the same result for each User.find(1). However, the result was Rails does not actually cache the query; i was expecting the update does not reflected on the result since it was "cached":

Suwir Suwirr
Pengguna

But the console says that it was cached: (Please highlight the CACHE word)

Started GET "/diag/test" for 10.0.2.2 at 2017-02-21 10:30:16 +0700
Processing by DiagController#test as HTML
  User Load (0.7ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 4], ["LIMIT", 1]]
  User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
   (0.1ms)  BEGIN
  SQL (0.4ms)  UPDATE "users" SET "first_name" = $1, "updated_at" = $2 WHERE "users"."id" = $3  [["first_name", "Suwir Suwirr"], ["updated_at", 2017-02-21 03:30:16 UTC], ["id", 1]]
   (16.5ms)  COMMIT
  User Load (0.3ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  CACHE (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
   (0.1ms)  BEGIN
  SQL (0.3ms)  UPDATE "users" SET "first_name" = $1, "updated_at" = $2 WHERE "users"."id" = $3  [["first_name", "Pengguna"], ["updated_at", 2017-02-21 03:30:16 UTC], ["id", 1]]
   (0.9ms)  COMMIT
  User Load (0.5ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Rendering text template
  Rendered text template (0.0ms)
Completed 200 OK in 380ms (Views: 3.5ms | ActiveRecord: 21.9ms)

So my question, does Rails actually cache the query result? Or, only several query result on some request?

Update: Using Batch #update_all

I made another experiment to "fool" the query logic. Now Rails does not "cache" the query. Why this behaviour can happen?

# Controller
  def test
    data = ""
    User.where(id: 1).update_all(first_name: 'Suwir Suwirr')
    data << User.find(1).first_name
    data << "\n"

    User.where(id: 1).update_all(first_name: 'Pengguna')
    data << User.find(1).first_name
    data << "\n"

    logger.info 'hi'

    render plain: data
  end

# Console
Started GET "/diag/test" for 10.0.2.2 at 2017-02-21 10:45:43 +0700
Processing by DiagController#test as HTML
  User Load (0.3ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 4], ["LIMIT", 1]]
  SQL (13.8ms)  UPDATE "users" SET "first_name" = 'Suwir Suwirr' WHERE "users"."id" = $1  [["id", 1]]
  User Load (0.4ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  SQL (2.9ms)  UPDATE "users" SET "first_name" = 'Pengguna' WHERE "users"."id" = $1  [["id", 1]]
  User Load (0.3ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
hi
  Rendering text template
  Rendered text template (0.0ms)
Completed 200 OK in 28ms (Views: 0.8ms | ActiveRecord: 17.8ms)

# Browser result
Suwir Suwirr
Pengguna

Upvotes: 4

Views: 3923

Answers (1)

waza
waza

Reputation: 526

I was stupid.

Yes, Rails does actually cache the query, but update and destroy will invalidate its query cache. update_all is basically iterating each record with update.

I tried the experiment by really "fooling" the ActiveRecord query mechanism. And yes, it works.

# Controller
  def test
    data = ""
    ActiveRecord::Base.connection.execute('UPDATE "users" SET "first_name" = \'Suwir Suwirr\' WHERE "users"."id" = 1')
    data << User.find(1).first_name
    data << "\n"

    ActiveRecord::Base.connection.execute('UPDATE "users" SET "first_name" = \'Pengguna\' WHERE "users"."id" = 1')
    data << User.find(1).first_name
    data << "\n"

    render plain: data
  end

# Browser
Suwir Suwirr
Suwir Suwirr

Upvotes: 4

Related Questions