Hey @janhendrik-rust welcome! I’m trying to understand what databases you have set up. Are you connecting to the MSSQL 2019 database via the postgres adapter? If so, how? I was not aware that MSSQL had a postgres compatible interface.

Wha , is this a thing?

Pretty sure it doesn’t; unless its third party… and even then not sure how (or why) you’d do it. I’m just about to start a gig helping with a MS SQL Server migration to PostgreSQL which includes a temporary integration piece. Connectivity mismatches is one of the issues which led to them brining me in. Lots of differences from authentication techniques, to data types, etc. not sure it would be feasible to make it work, let alone any reason/benefit at that level.

While the person you’re responding to would need to answer to be sure (naturally), I read their comment as, “2000x longer with Tds connecting to MSSQL server than the PostgreSQL adapter connecting to PostgreSQL.” (with against the same data implied).

If I get some time, and since I just happen to have a MSSQL Server setup on my workstation now due to this other project, I might try the repo the questioner posted to see if I can help; weekend stuff though, I’m afraid.

Couple of things to consider:

1.) Are the MSSQL DB and the Postgres DB both on the same machine? If the Postgres is local and the MSSQL DB isn’t that’d explain the speed difference. Try setting them both up on the same machine and see if that impacts your performance at all.

2.) I’d check the DB parameters mentioned here: Ecto.Adapters.Tds — Ecto SQL v3.11.1 to insure you’re matching everything.

3.) There’s a built-in adapter for TDS in ecto_sql. What happens if you take out the seperate Tds hex package? I’d definitely try the ecto_sql version because if there were any changes made to support better performance they’re likely to have been made to that driver.

It’s hard to imagine a case where you could be assured of an “apples to apples” comparison across DB’s. I mean there are so many things which would factor in to performance. Are all the tables indexed the same? Are all the field types the same? etc. etc.

Oh sure… but sometimes something simple can be informative, even if all you get a vague hints or suggestions of what kinds of problems might be present in the actual scenario. The key is to understand the limits of what it is your looking at, if you aren’t looking at the “real thing”.

Can you show the results using a schema instead of a raw query?

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:

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:

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.

Great, this is the bit that was missing then. The indication that there is an issue is that other queries by other clients are fast, not that the queries against postgres are fast.

The TDS response time does look a bit N^2, so perhaps there is a bug inside its implementation? If you turn debug logging on what is the log output you get from Ecto? It should tell you how long the query took vs how long parsing the responses took. That may not be a smoking gun depending on where the bug is but it might help.

First of all I would ensure that you are using the latest versions of the dependencies. Check the mix.lock and cross-reference with the releases or do a mix deps.update --all to upgrade all of the dependencies to their latest versions.