Elixir-dbvisor/sql Needs a SOTA pool and I want to hear your ideas

Questions / Help
,
9

So we now have a SOTA pool not only on the BEAM but across all languages (AFAIK), some of the things I listed didn’t make it, mostly becuase they ended up being completely unnecessary when you lean into the BEAM.

import Ecto.Query
defmodule SQL.Repo do
  use Ecto.Repo, otp_app: :sql, adapter: Ecto.Adapters.Postgres
  use SQL, adapter: SQL.Adapters.Postgres
  import Ecto.Query
  def sql(type \\ :transaction)
  def sql(:statement) do
    Enum.to_list(~SQL"SELECT 1")
  end
  def sql(:empty) do
    SQL.transaction do
      :ok
    end
  end
  def sql(:transaction) do
    SQL.transaction do
      Enum.to_list(~SQL"SELECT 1")
    end
  end
  def sql(:savepoint) do
    SQL.transaction do
      SQL.transaction do
        Enum.to_list(~SQL"SELECT 1")
      end
    end
  end
  def sql(:cursor) do
    SQL.transaction do
      Stream.run(~SQL"SELECT g, repeat(md5(g::text), 4) FROM generate_series(1, 5000000) AS g")
    end
  end

  def ecto(type \\ :transaction)
  def ecto(:statement) do
    SQL.Repo.all(select(from("users"), [1]))
  end
  def ecto(:empty) do
    SQL.Repo.transaction(fn ->
      :ok
    end)
  end
  def ecto(:transaction) do
    SQL.Repo.transaction(fn ->
      SQL.Repo.all(select(from("users"), [1]))
    end)
  end
  def ecto(:savepoint) do
    SQL.Repo.transaction(fn ->
      SQL.Repo.transaction(fn ->
        SQL.Repo.all(select(from("users"), [1]))
      end)
    end)
  end
  def ecto(:cursor) do
    SQL.Repo.transaction(fn ->
      from(row in fragment("SELECT g, repeat(md5(g::text), 4) FROM generate_series(1, ?) AS g", 5000000), select: {fragment("?::int", row.g), fragment("?::text", row.repeat)})
      |> SQL.Repo.stream()
      |> Stream.run()
    end)
  end
end
Application.put_env(:sql, :ecto_repos, [SQL.Repo])
Application.put_env(:sql, SQL.Repo, log: false, username: "postgres", password: "postgres", hostname: "localhost", database: "sql_test#{System.get_env("MIX_TEST_PARTITION")}", pool_size: :erlang.system_info(:schedulers_online), ssl: false)
SQL.Repo.__adapter__().storage_up(SQL.Repo.config())
SQL.Repo.start_link()

Benchee.run(
  %{
  "sql" => fn -> SQL.Repo.sql(:transaction) end,
  "ecto" => fn -> SQL.Repo.ecto(:transaction) end,
  },
  parallel: 500,
  warmup: 10,
  memory_time: 2,
  reduction_time: 2,
  unit_scaling: :smallest,
  measure_function_call_overhead: true
)

➜ sql git:(main) ✗ mix sql.bench
Operating System: macOS
CPU Information: Apple M1 Max
Number of Available Cores: 10
Available memory: 64 GB
Elixir 1.20.0-dev
Erlang 28.1
JIT enabled: true

Benchmark suite executing with the following configuration:
warmup: 10 s
time: 5 s
memory time: 2 s
reduction time: 2 s
parallel: 500
inputs: none specified
Estimated total run time: 38 s

Measured function call overhead as: 0 ns
Benchmarking ecto …
Benchmarking sql …
Calculating statistics…
Formatting results…

Name ips average deviation median 99th %
sql 5507.15 181.58 μs ±1576.97% 177.13 μs 324.92 μs
ecto 56.22 17788.30 μs ±8.30% 17331.33 μs 22480.54 μs

Comparison:
sql 5507.15
ecto 56.22 - 97.96x slower +17606.72 μs

Memory usage statistics:

Name average deviation median 99th %
sql 984.00 B ±0.02% 984 B 984 B
ecto 17952.31 B ±0.03% 17952 B 17952 B

Comparison:
sql 984 B
ecto 17952.31 B - 18.24x memory usage +16968.31 B

Reduction count statistics:

Name average deviation median 99th %
sql 0.0920 K ±0.03% 0.0920 K 0.0920 K
ecto 1.56 K ±0.30% 1.56 K 1.56 K

Comparison:
sql 0.0920 K
ecto 1.56 K - 16.91x reduction count +1.46 K