Slow queries compared to SQLite3 CLI or other clients
Closed this issue · 4 comments
SebastianSzturo commented
I've noticed that large queries are significantly slower via ecto_sqlite3
than through the sqlite3
CLI.
Example:
defmodule Datadoor.Transaction do
use Ecto.Schema
schema "transactions" do
field :transaction_amount, :integer
field :transaction_date, :naive_datetime
timestamps(inserted_at: :created_at)
end
end
Here is the example data of ~650k rows: dev.db.zip
Via Ecto: 22504.3ms
Transaction
|> select([:transaction_date, :transaction_amount])
# |> limit(100_000)
|> Repo.all()
[debug] QUERY OK source="transactions" db=22504.3ms decode=0.8ms queue=0.1ms idle=112.7ms
SELECT t0."transaction_date", t0."transaction_amount" FROM "transactions" AS t0 []
Via Ecto (Raw SQL): 21520.2ms
Ecto.Adapters.SQL.query(
Repo,
"SELECT transaction_date, transaction_amount FROM transactions"
)
[debug] QUERY OK db=21520.2ms decode=1.4ms idle=106.7ms
SELECT transaction_date, transaction_amount FROM transactions []
Via SQLite3 CLI or any other SQLite3 GUI:
sqlite> .timer on
sqlite> SELECT transaction_date, transaction_amount FROM transactions;
Run Time: real 2.680 user 0.423684 sys 0.323269
sqlite>
Is there any good explanation for this performance difference or some obvious steps to improve the performance?
SebastianSzturo commented
Here are the results running the query with Exqlite directly:
{:ok, conn} = Exqlite.Sqlite3.open("./dev.db")
:ok =
Exqlite.Sqlite3.execute(
conn,
"SELECT transaction_date, transaction_amount FROM transactions"
)
Benchmarking exqlite_query ...
Name ips average deviation median 99th %
exqlite_query 11.36 88.02 ms ±0.82% 88.12 ms 89.67 ms
SebastianSzturo commented
Looks like the issue happens when using Exqlite.Connection
:
{:ok, db} =
DBConnection.start_link(Exqlite.Connection,
database: "./dev.db",
journal_mode: :wal,
timeout: 25_000
)
{:ok, _, result} =
DBConnection.execute(
db,
%Exqlite.Query{
statement: "SELECT transaction_date, transaction_amount FROM transactions"
},
[]
)
I'll open an issue in https://github.com/elixir-sqlite/exqlite
SebastianSzturo commented
For reference: elixir-sqlite/exqlite#199
warmwaffles commented
Thanks, will take a look into this.