The Query Interface and the N+1 Problem

The previous lesson showed that a validation error is read only in the request that wrote it, and that a relationship is nothing more than an id field in an order record. The order table was filtered to read the orders of a product. How many times does a request go to the database to fill a page? Reading one product is one query; reading the product of each of a hundred orders separately is a hundred queries. The number of queries is one of the numbers that decide how long it takes to draw a page.

The query interface is the sum of the methods that read records by condition, by id or by relationship. This lesson measures not the interface itself but how many queries run behind it. The measure is the same question as always in the course: how many requests does this value stay for? The value measured here is the query cache, which prevents the same query from being asked a second time. Because its name says cache, it may be taken for persistent. The lesson measures this.

The Measurement Core

The first file is the request loop and the stay classifier, identical in every lesson of the course. This block is the source; the file is written to disk and pulled in with require_relative.

# stay.rb — a small request loop and a helper that classifies how many requests a value stays for

# Flash: a value written in this request is carried to the next request only.
class Flash
  def initialize(incoming)
    @incoming = incoming
    @fresh = {}
  end

  def [](key) = @fresh.fetch(key) { @incoming[key] }

  def []=(key, value)
    @fresh[key] = value
  end

  def passed_on = @fresh
end

# Every request gets a new context: params, flash and the request store start empty each time.
class Context
  attr_reader :app, :params, :session, :flash, :request_store

  def initialize(app, session)
    @app = app
    @params = {}
    @session = session
    @flash = Flash.new(session.delete(:flash) || {})
    @request_store = {}
  end
end

# The application store is the same across all requests and all clients;
# the session is chosen per client.
class App
  attr_reader :store

  def initialize
    @sessions = Hash.new { |h, client| h[client] = {} }
    @store = { products: {}, schema: { products: %i[name price] }, templates: {},
               queue: [], cache: {}, counter: 0 }
  end

  def request(client)
    context = Context.new(self, @sessions[client])
    result = yield context
    context.session[:flash] = context.flash.passed_on unless context.flash.passed_on.empty?
    result
  end
end

# Rule: request 1 (client A) writes the value; requests 2 and 3 (A) and request 4 (B) read it.
# If B sees it, persistent; if A's third request sees it, session; if only the second
# request sees it, next; if none sees it, this_request.
def stay(write, read)
  app = App.new
  first = app.request("A") { |b| write.call(b); read.call(b) }
  raise ArgumentError, "value could not be read in the request that wrote it" if first.nil?

  seen = [["A", 2], ["A", 3], ["B", 4]].map do |client, order|
    app.request(client) { |b| read.call(b) }.nil? ? "--" : "#{client}#{order}"
  end
  kind = if seen[2] != "--" then "persistent"
         elsif seen[1] != "--" then "session"
         elsif seen[0] != "--" then "next"
         else "this_request"
         end
  [kind, ["A1", *seen].join(" "), first]
end

The core is not a framework but a model of a framework. The second file carries the product model and the lookup method that reads by id. lookup first looks at the query cache in the request store; if the id is not there, it increments the counter, reads the table and writes the result into the cache.

# query.rb — product model: lookup runs the same query once in the same request
require_relative "stay"

module Product
  def self.insert(b, attributes)
    errors = []
    errors << "name cannot be blank" if attributes[:name].to_s.empty?
    errors << "price cannot be negative" if attributes[:price].to_i.negative?
    return { errors: } unless errors.empty?

    table = b.app.store[:products]
    id = table.size + 1
    table[id] = attributes.merge(id:)
    { id:, errors: }
  end

  def self.lookup(b, id)
    queries = (b.request_store[:queries] ||= {})
    # The same query does not go to the database twice in the same request.
    queries.fetch([:products, id]) do
      b.request_store[:query_count] = b.request_store.fetch(:query_count, 0) + 1
      queries[[:products, id]] = b.app.store[:products][id]
    end
  end
end

This block is source as well. The cache key is the pair [:products, id]: which table and which id. The cache and the counter live in request_store, so they start anew for every request.

The Stay of the Cache

The observation of the lesson is the query cache. The writing lambda creates a product and reads it by id; the reading lambda looks at the name of the record in the cache.

# measure.rb — stay of the query cache observation
require_relative "query"

pen = ->(b) { Product.insert(b, name: "pen", price: 12) }
write = ->(b) { pen.call(b); Product.lookup(b, 1) }
read = ->(b) { b.request_store.dig(:queries, [:products, 1], :name) }
p stay(write, read)

["this_request", "A1 -- -- --", "pen"]

The stay is this request. The evidence column is A1 -- -- --: the record in the cache was read only in the request that wrote it; it is absent in the later requests of A and in the request of B. The name cache gives the impression that the value lives long, but the measured value is in the request store and goes away when the request ends. What is persistent is the product itself, not the copy in the cache. DL13. In this measurement the cache is in request_store. A mechanism that kept the cache in the store of the app would give another stay; the claim of this lesson concerns only the stay of a cache in the request store.

The Number of Queries

To see that the cache works, look at the counter. Three products are created in one request; in the next request lookup is called with three different ids, and then again with the same three ids.

# counter.rb — the same id is not queried a second time
require_relative "query"

app = App.new
app.request("A") do |b|
  %w[pen notebook eraser].each_with_index { |name, i| Product.insert(b, name:, price: 10 + i) }
end

counts = app.request("A") do |b|
  [1, 2, 3].each { |id| Product.lookup(b, id) }
  first = b.request_store[:query_count]
  [1, 2, 3].each { |id| Product.lookup(b, id) }
  [first, b.request_store[:query_count]]
end
p counts
p app.request("A") { |b| b.request_store[:query_count] }

[3, 3]
nil

Three different ids counted three queries. The second round with the same ids did not move the counter: it stayed 3. In the next request the counter is nil: because the request store started empty with the new request, both the counter and the cache start from zero. The same query runs twice in two separate requests.

An earlier lesson of the course made “how many times did it run” the measure, and a number is printed in this lesson too. The difference is this: here the number is not the thing measured by itself but the proof that the cache works. The value the lesson classifies is still the stay: the query cache stays in this request.

Whether the cache recognizes the same query depends on the key. The key is the pair [:products, id], and the pair is compared together with the type of the id. An id that comes from a form or from the address line often arrives as text; the measurement below asks for the same id as an integer and as text.

# key.rb — the same id, two rounds
require_relative "query"

app = App.new
app.request("A") { |b| Product.insert(b, name: "pen", price: 12) }

app.request("A") do |b|
  p Product.lookup(b, 1)[:name]
  p Product.lookup(b, "1")
  p Product.lookup(b, "1".to_i)[:name]
  p b.request_store[:query_count]
  p b.request_store[:queries].keys
end

"pen"
nil
"pen"
2
[[:products, 1], [:products, "1"]]

1 and "1" became two separate keys: the counter showed 2, so the cache did not count the second call as the same query. The query made with the text id also returned nil, because the keys of the table are integers. "1".to_i, on the other hand, coincided with the first key: the third call returned from the cache and the counter did not rise. This is a property of the core model: converting the id to an integer is the caller’s job. Whether real mappers convert a text id by themselves has not been measured here; what is measured is that the cache depends on key equality and that equality covers the type. Code that wants to keep the number of queries low must convert the id to a single type before it enters the cache.

The N+1 Problem

Let a page show the orders and the product of each order. First the orders are read: one query. Then reading the product for each order with a separate query means as many more queries as there are orders. If the number of orders is N, the total is N+1 queries. This is called the N+1 problem, and the approach that fetches a related record only when it is accessed is called lazy loading; when each record is accessed once, this means a separate query for each record. The fix is eager loading, where the related records are fetched together with a single query.

This lesson builds a small query helper that does not depend on the cache and is measured only by its counter. Each method increments the counter when it runs a query.

# order_queries.rb — helpers that count queries
require_relative "query"

module OrderQueries
  def self.tick(b)
    b.request_store[:query_count] = b.request_store.fetch(:query_count, 0) + 1
  end

  def self.orders(b)
    tick(b)
    b.app.store[:orders].values
  end

  def self.product(b, id)
    tick(b)
    b.app.store[:products][id]
  end

  def self.products(b, ids)
    tick(b)
    b.app.store[:products].values_at(*ids)
  end
end

This block is source as well. orders reads all the orders with one query, product reads one product with one query, and products reads several products, again with one query. The measurement below reads five orders in three ways: lazy, eager, and through the Product.lookup cache.

# n_plus_one.rb — the same page, three ways, three query counts
require_relative "order_queries"

app = App.new
app.request("A") do |b|
  %w[pen notebook eraser].each_with_index { |name, i| Product.insert(b, name:, price: 10 + i) }
  records = [1, 1, 2, 3, 2].each_with_index.to_h do |product_id, i|
    [i + 1, { id: i + 1, product_id:, quantity: i + 1 }]
  end
  b.app.store[:orders] = records
end

lazy = app.request("A") do |b|
  names = OrderQueries.orders(b).map { |o| OrderQueries.product(b, o[:product_id])[:name] }
  [names, b.request_store[:query_count]]
end

eager = app.request("A") do |b|
  records = OrderQueries.orders(b)
  products = OrderQueries.products(b, records.map { |o| o[:product_id] }.uniq).to_h { |pr| [pr[:id], pr] }
  names = records.map { |o| products[o[:product_id]][:name] }
  [names, b.request_store[:query_count]]
end

cached = app.request("A") do |b|
  names = OrderQueries.orders(b).map { |o| Product.lookup(b, o[:product_id])[:name] }
  [names, b.request_store[:query_count]]
end

p lazy
p eager
p cached
p lazy[0] == eager[0] && eager[0] == cached[0]

[["pen", "pen", "notebook", "eraser", "notebook"], 6]
[["pen", "pen", "notebook", "eraser", "notebook"], 2]
[["pen", "pen", "notebook", "eraser", "notebook"], 4]
true

The three ways produced the same five names, but the query counts differ. The lazy way ran 6 queries: one for the orders and one more for each order, that is 1+5. The eager way ran 2 queries: one for the orders and one for the distinct products. Even if the number of orders grows, the count of the eager way stays two; the count of the lazy way grows with the orders. The third way is between the two: the cache queries repeated ids once, so 4 queries ran, that is 1 plus three different products. The cache does not remove N+1; it lowers it to the number of different ids, and if the different ids are as many as the orders, the count grows again.

DL14. This measurement counts queries and does not measure time. The cost per query is close to zero in this in-memory model; that every query in a real database has a round-trip cost has not been measured in this lesson, and the claim of the lesson is only the difference between the counts.

An Id That Does Not Exist in Eager Loading

Eager loading lowers the query for related records to one, but it also changes the shape of the result. In this model, if one of the ids read together is not in the table, its place in the result list stays nil; what real mappers do with a missing record has not been measured here. The measurement looks at what the eager way does when the product of an order has been deleted.

# missing_product.rb — an id that does not exist in eager loading
require_relative "order_queries"

app = App.new
app.request("A") do |b|
  Product.insert(b, name: "pen", price: 12)
  b.app.store[:orders] = {
    1 => { id: 1, product_id: 1, quantity: 2 },
    2 => { id: 2, product_id: 99, quantity: 1 }
  }
end

app.request("A") do |b|
  records = OrderQueries.orders(b)
  products = OrderQueries.products(b, records.map { |o| o[:product_id] }.uniq)
  p products.map { |pr| pr&.fetch(:name) }
  p b.request_store[:query_count]
  begin
    products.to_h { |pr| [pr[:id], pr] }
  rescue => e
    p e.class
  end
end

["pen", nil]
2
NoMethodError

One query for the orders and one for the products ran, and the counter showed 2; but in the result list the place of the id that does not exist is nil. Calling pr[:id] on this nil raised NoMethodError. The hash built in bulk could not be built at all because of a single missing record. Eager loading keeps the number of queries constant, but it leaves the responsibility of handling a missing related record to the calling code. The orphan order of the previous lesson appears here as NoMethodError.

The Bounding Measurement

Does the cache store only the records that are found? The first query with an id that does not exist returns nil. If the query cache stores this nil too, the second query for the missing id does not go to the database. This has a side effect: if the record is added later in the same request, the cache still returns nil.

# boundary.rb — the cache stores nil too
require_relative "query"

app = App.new
app.request("A") do |b|
  %w[pen notebook eraser].each_with_index { |name, i| Product.insert(b, name:, price: 10 + i) }
end

result = app.request("A") do |b|
  [1, 2, 3].each { |id| Product.lookup(b, id) }
  Product.lookup(b, 99)
  Product.lookup(b, 99)
  count = b.request_store[:query_count]
  before = Product.lookup(b, 4)
  Product.insert(b, name: "ruler", price: 8)
  after = Product.lookup(b, 4)
  [count, before, after, b.app.store[:products][4][:name]]
end
p result
p app.request("A") { |b| Product.lookup(b, 4)[:name] }
p app.request("A") { |b| b.request_store[:query_count] }

[4, nil, nil, "ruler"]
"ruler"
nil

The three different ids and the missing id 99 counted four queries; the second call for 99 did not move the counter. Because the cache stores a nil result too, it remembered it as an empty record. The same happened with the fourth id: nil entered the cache while the product did not yet exist; after the product was created, lookup(4) returned nil again in the same request, although the product was in the table as "ruler". The cache stayed stale in this request. Because the next request started the cache from zero, it read the same product correctly, and the counter was again nil in the new request.

This is the flip side of the stay of the cache. Because the cache stays only in this request, its staleness is limited to this request as well; the next request starts clean. Code that reads and then writes within one request can read the cache as it was before the write. DL15. Here the cache is not invalidated on a write. A mechanism that emptied the cache on a write would have prevented this staleness; no such mechanism was built or measured in this lesson.

Summary

• The query interface reads records by id, by condition or by relationship; how many queries run in a request is measured with a counter.
• The query cache, the observation of this lesson, is a value whose stay is this request: the record in the cache was read only in the request that wrote it.
• The same id is not queried a second time in the same request; in a new request the counter and the cache start from zero.
• The N+1 problem is fetching the related record with a separate query for each record; eager loading lowers the queries to two, and the cache only removes repeated ids.
• The cache key includes the type of the id; a "1" that arrives without being converted to a type counts as a separate query. In eager loading an id that is not in the table leaves nil in the list in this model, and the code that builds the result must handle this separately.
• The cache stores a nil result too; a record added later in the same request is not seen, but this staleness is limited to that request.

Next Step

The data layer is complete: record, schema, relationship, validation and query count have been measured. How is a user who signs in recognized on the next page? The next topic measures the answer to this question in authentication.