Atrocious speed with MSSQL over ODBC

Question

I am using Alteryx Desktop 2023.1.1.123, but I have made the same observation when running a workflow on Alteryx Server 2023.1.1.460.

Alteryx appears to have some issue when using ODBC to connect to MSSQL server. The raw speed (as seen in task manager) when retrieving results from a large table is less than 4Mbps.

A few things I tried:

* Interestingly, when I select "Fetch single rows", it is slightly faster (around 5.5Mbps).
* "Do not show % complete" was always enabled.
* The speed I get is very consistent. I have retried multiple times.
* When I run the exact same query in Alteryx with the OLEDB driver, the speed is more than 70Mbps.
* When I run the exact same query with the same ODBC DSN in a SQL editor, I also reach speeds above 70Mbps.

I am using "ODBC Driver 18 for SQL Server". The query is a simple "SELECT * FROM" and everything is run from the same PC connecting to the same DB.

Does anyone have similar issues? I have found this thread which seems similar.

It is not a big issue, only a slight annoyance because the OLEDB driver is not supported by DCM.

cjaneczko · Answer

Have you tried it with "SET NOCOUNT ON" in the PreSQL statement?

apathetichell · Answer

Hey --- not a huge MSSQL dude --- but this seems buggy. I see in the thread you linked there may have been an issue between what query execution and result execution (ie when querying it takes  5 second locally, in Alteryx it takes 10 minutes to retrieve the results --- because the 5 seconds was execution only--- and time to retrieve the results was not calculated). The original thread seemed to say that caching the query results (ie executing it in Powershell first) sped up Alteryx execution. That doesn't seem to be your issue because your data is quite small.

My recommendations would be to throw a limit on your query (ie limit of 100000 rows)... I have seen some DBs where the issue was estimating the full size of the dataset when retrieving it. Otherwise - I'm not really sure here.

And sorry - my initial reading of this was a speed conflict between a Server and a worker node --- which could be a machine/driver config issue.

Lorenz0 · Answer

Thanks for your reply.

As mentioned, I am able to make the same query via the same ODBC connection (same DSN) with a SQL editor on the same machine, and the network speed is normal. This implies that this is not an issue with the network (including VPN), nor the machine.