to_sql_bench.exs

# -----------------------------------Goal--------------------------------------
# Compare the implementation of parsing Ecto.Query objects into SQL queries by
# the different database adapters

# -------------------------------Description-----------------------------------
# Repo.to_sql/2 is an important step of a database query.
# This benchmark tracks performance of parsing Ecto.Query structures into
# "raw" SQL query strings.
# Different Ecto.Query objects has multiple combinations and some different attributes
# depending on the query type. In this tests we benchmark against different
# query types and complexity.

# ----------------------------Factors(don't change)---------------------------
# Different adapters supported by Ecto, each one has its own implementation that
# is tested against different query inputs

# ----------------------------Parameters(change)-------------------------------
# Different query objects (select, delete, update) to be translated into pure SQL
# strings.

Code.require_file("../../support/setup.exs", __DIR__)

import Ecto.Query

alias Ecto.Bench.{User, Game}

inputs = %{
  "1. Ordinary Select All" => {:all, from(User)},
  "2. Ordinary Delete All" => {:delete_all, from(User)},
  "3. Ordinary Update All" => {:update_all, from(User, update: [set: [name: "Thor"]])},
  "4. Ordinary Where" => {:all, from(User, where: [name: "Thanos", email: "blah@blah"])},
  "5. Fetch First Registry" => {:all, first(User)},
  "6. Fetch Last Registry" => {:all, last(User)},
  "7. Ordinary Order By" => {:all, order_by(User, desc: :name)},
  "8. Complex Query 2 Joins" =>
    {:all,
     from(User, where: [name: "Thanos"])
     |> join(:left, [u], ux in User, on: u.id == ux.id)
     |> join(:right, [j], uj in User, on: j.id == 1 and j.email == "email@email")
     |> select([u, ux], {u.name, ux.email})},
  "9. Complex Query 4 Joins" =>
    {:all,
     from(User)
     |> join(:left, [u], g in Game, on: g.name == u.name)
     |> join(:right, [g], u in User, on: g.id == 1 and u.email == "email@email")
     |> join(:inner, [u], g in fragment("SELECT * from games where game.id = ?", u.id), on: true)
     |> join(:left, [g], u in fragment("SELECT * from users = ?", g.id), on: true)
     |> select([u, g], {u.name, g.price})}
}

jobs = %{
  "Pg Query Builder" => fn {type, query} -> Ecto.Bench.PgRepo.to_sql(type, query) end,
  "MyXQL Query Builder" => fn {type, query} -> Ecto.Bench.MyXQLRepo.to_sql(type, query) end
}

Benchee.run(
  jobs,
  inputs: inputs,
  formatters: Ecto.Bench.Helper.formatters("to_sql")
)