SQL Server Bulk Loader

[benwh56]

Hello all,

I'm currently attempting to write a large table (roughly 15mil rows and 30 some-odd columns) to Azure Synapse from a .yxdb. Initially i was trying with a regular old ODBC connection in the Output tool, but this was fairly slow. I also noticed that I would get a warning saying, "For faster write performance, it is recommended that the Bulk Load Connection be used instead of the ODBC Connection." 

I'm currently using SQL Service Bulk Loader for file format, but it appears to be even slower. 

I'm pretty novice at this so I'm not really sure where I should start in terms of tweaking things to make the write operation perform faster.

A couple of facts:

- I'm running this from Designer 2024.1.1

- I'm still getting the "For faster write performance, it is recommended that the Bulk Load Connection be used instead of the ODBC Connection." warning. Perhaps I'm not actually using the Bulk Loader? (All I did was change file format as denoted on option 2 in the screenshot below).

- My transaction size is 5000.

- I'm attempting this while WFH and using a VPN. I think this may be part of the problem and if I were to attempt this from the office it may run a bit quicker. Not sure if that's wishful thinking or not... 

- Below is my output tool's configuration - should be all default settings.

 

I'd be grateful for any tips/tricks/suggestions/corrections.

Thanks all!

[nikhilesh_kumar]

Hi benwh56,

You can try following things from your side.

1)increase the transaction size to 500k and try it.

2)include Auto field option before output tool(By default some columns will take  2147483647 size By including auto field it will decrease column size) which will improve loading process.

3)If you are attempting to run workflow doing WFH with VPN definitely it will decrease performance because data packet has to travel multiple hops before reaching Azure Synapse.

If designer is in Same network as Azure Synapse performance will improve

[dataguyW]

I have had issues with some SQL server databases and found that sometimes the Native Driver is a better option for bulk inserts vs. the generic ODBC.   You didn't specifically mention version or drivers SQL Server Native Client 11.0 (or probably higher) vs. the Oracle Driver for SQL Server.     It doesn't seem to work on all of them and maybe it is more to do with the age of the SQL Server database and version.    I can't say I figured out why it worked, but it was notably faster.    Might be worth a quick check.    I have never checked with the Alteryx/Simba versions either.