Output data to MS Azure SQL Server incredibly slow

Question

I have a workflow that outputs data to a MS Azure SQL Server database using ODBC Driver 17 for SQL Server and output seems to be abnormally slow. I have the Update: Insert if New option enabled and I'm only outputting about 16-17k rows of data each time it runs, but it takes about 20 min to execute the workflow and according to the profiler ~98% of that time is from writing to the Azure SQL db. If I switch the output to a local SQL Server db using the same ODBC driver then the workflow completes within a few seconds. Should I be using a different ODBC driver to output to MS Azure, or do I have the output tool configured incorrectly? I appreciate any pointers or guidance.

JonathanAllenby · Answer

Replying to what I believe to be a dead account but hey-ho.

Did you ever make any further progress with this?

SWood · Answer

Thanks for the guidance, @ArnavS. I tried switching to ODBC driver 13 but it didn't seem to make any difference relative to ODBC driver 17. I noticed the transaction size was set to 10,000 by default so I changed that to 0 to write all records at once and it cut the execution time by about half, now it only takes ~9 min instead of 20-ish. I'm running Alteryx version 2020.1.2.24185, I'll try updating to 2020.2.3 and see if that helps any.

ArnavS · Answer

Hi @SWood

Please see our help documentation for connecting to MS Azure databases. It is recommended to use OBDC Driver 11, 13, 13.1.

If a different driver version does not speed up your workflow, could you verify your version of Alteryx?