elixir-sqlite / exqlite

An SQLite3 driver for Elixir
https://hexdocs.pm/exqlite
MIT License
217 stars 48 forks source link

Return out_of_memory error tuple if row alloc fails #304

Open ruslandoga opened 1 month ago

ruslandoga commented 1 month ago

Attempt at https://github.com/elixir-sqlite/exqlite/pull/301#discussion_r1794894228

This PR makes multi_step allocate cells array once, and return an error tuple if that allocation fails.

Some benchmarks (the "10 rows" one might need to be re-run, it seems to be an outlier):

This approach ```elixir Mix.install [{:exqlite, github: "ruslandoga/exqlite", branch: "make-row"}, :benchee], force: true sql = """ with recursive cte(i) as ( values(0) union all select i + 1 from cte where i < ? ) select i, i / 1.0, null, x'000000', 'hello' || i from cte """ alias Exqlite.Sqlite3 Benchee.run( %{"fetch_all" => fn %{conn: conn, stmt: stmt} -> Sqlite3.fetch_all(conn, stmt) end}, inputs: %{ "10 rows" => 10, "100 rows" => 100, "1000 rows" => 1000, "10000 rows" => 10000 }, before_scenario: fn rows -> {:ok, conn} = Sqlite3.open(":memory:", [:readonly, :nomutex]) {:ok, stmt} = Sqlite3.prepare(conn, sql) Sqlite3.bind(conn, stmt, [rows]) %{conn: conn, stmt: stmt} end, after_scenario: fn %{conn: conn, stmt: stmt} -> Sqlite3.release(conn, stmt) Sqlite3.close(conn) end ) ``` Results: ``` ##### With input 10 rows ##### Name ips average deviation median 99th % fetch_all 87.22 K 11.47 μs ±71.28% 9.83 μs 47.25 μs ##### With input 100 rows ##### Name ips average deviation median 99th % fetch_all 11.47 K 87.22 μs ±14.59% 86.13 μs 128.13 μs ##### With input 1000 rows ##### Name ips average deviation median 99th % fetch_all 1.24 K 809.13 μs ±1.90% 808.29 μs 850.19 μs ##### With input 10000 rows ##### Name ips average deviation median 99th % fetch_all 139.13 7.19 ms ±9.00% 7.45 ms 8.45 ms ```
Master ```elixir Mix.install [:exqlite, :benchee], force: true sql = """ with recursive cte(i) as ( values(0) union all select i + 1 from cte where i < ? ) select i, i / 1.0, null, x'000000', 'hello' || i from cte """ alias Exqlite.Sqlite3 Benchee.run( %{"fetch_all" => fn %{conn: conn, stmt: stmt} -> Sqlite3.fetch_all(conn, stmt) end}, inputs: %{ "10 rows" => 10, "100 rows" => 100, "1000 rows" => 1000, "10000 rows" => 10000 }, before_scenario: fn rows -> {:ok, conn} = Sqlite3.open(":memory:", [:readonly, :nomutex]) {:ok, stmt} = Sqlite3.prepare(conn, sql) Sqlite3.bind(conn, stmt, [rows]) %{conn: conn, stmt: stmt} end, after_scenario: fn %{conn: conn, stmt: stmt} -> Sqlite3.release(conn, stmt) Sqlite3.close(conn) end ) ``` Results: ``` ##### With input 10 rows ##### Name ips average deviation median 99th % fetch_all 50.65 K 19.74 μs ±39.01% 18.42 μs 59.86 μs ##### With input 100 rows ##### Name ips average deviation median 99th % fetch_all 10.91 K 91.70 μs ±13.32% 90.00 μs 129.14 μs ##### With input 1000 rows ##### Name ips average deviation median 99th % fetch_all 1.19 K 840.99 μs ±1.63% 840.01 μs 880.36 μs ##### With input 10000 rows ##### Name ips average deviation median 99th % fetch_all 130.85 7.64 ms ±8.54% 7.88 ms 8.82 ms ```
warmwaffles commented 1 month ago

This needs to be rebased.