Slow queries compared to SQLite3 CLI or other clients

[SebastianSzturo]

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]

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]

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]

For reference: https://github.com/elixir-sqlite/exqlite/issues/199

[warmwaffles]

Thanks, will take a look into this.