Thanks for everyone’s interrest.
I’m really not trying to do an “apples to apples” comparrison - this is not a database showdown :).
Up until recently I’ve exclusively used Ecto with the Ecto.Adapters.Postgres adapter connecting to a local Postgres 15 container. The query performace has never been an issue. Then I switched the database over to a local MSSQL 2019 Express container and the Ecto.Adapters.Tds adapter.
When I perform the exact same query using the ecto_sql Tds {:tds, “~> 2.3”} adapter the query is up to 2000 times slower than when using the ecto_sql Postgres {:postgrex, "~> 0.17}, adapter on the seeded dataset. Have a look at the repo I posted for the migration and seed script I used.
At first I thought I must be doing something wrong, or there must be something misconfigured on the MSSQL side, or my code - and there still might be. But performing the same queries in DBeaver or sqlcmd/psql utilities had no decernable speed difference between the two local databases.
If I do small queries - returning les than 1000 rows the Tds adapter performs admirably. As the result set increases the time the query takes grows exponentially
Tds Adapter to local MSSQL2019 Express:
| Result Set size |
Query Time in ms |
| 10 |
3.861 |
| 100 |
5.083 |
| 1_000 |
53.342 |
| 2_000 |
178.746 |
| 4_000 |
3_467.661 |
| 8_000 |
20_511.550 |
| 16_000 |
88_519.569 |
The iex code snippet I used to query the MSSQL Ecto Repo
{time, result} = :timer.tc(Ecto.Adapters.SQL, :query!, [MyApp.Repo, "Select top 10000 float_one, float_two, float_three, float_four, float_five, float_six, float_seven, float_eight, float_nine, int_one, int_two, int_three from floats_test", []])
Postgrex Adapter to local Postgres 15:
| Result Set size |
Query Time in ms |
| 10 |
2.902 |
| 100 |
1.699 |
| 1_000 |
5.838 |
| 2_000 |
8.308 |
| 4_000 |
10.226 |
| 8_000 |
28.109 |
| 16_000 |
78.704 |
The iex code snippet I used to query the Postgres Ecto Repo
{time, result} = :timer.tc(Ecto.Adapters.SQL, :query!, [MyApp.Repo, "Select float_one, float_two, float_three, float_four, float_five, float_six, float_seven, float_eight, float_nine, int_one, int_two, int_three from floats_test limit 10000", []])
This gives me the impression that the serialization of the dataset might be to blame, but I am completely out of my depth as to how to troubleshoot this issue.
I get similar perfomance when using Ecto.Query queries - so the issue seems to be in the Tds adapter implementation.
, is this a thing?
