Insert speed into Azure SQL Server from remote

[rad-pat]

Just wondering what sort of throughput people get for inserting data into an Azure SQL Server via ODBC from remote? It's loading for me about 1000 rows every 3 seconds which is painfully slow when I have 6M rows to load. It seems to be using fast_executemany when I've traced through. Does anybody have any ideas to help speed this up?

I know the BCP APIs are missing at present, but I was wondering if I might have better success with those?

[rad-pat]

I built an ODBC app in Delphi to test out how quick the BCP API would be vs the fast_executemany for the same table in an azure database. Ran from the same machine, I get 330 rows/second for fast_executemany and close to 5000 rows/second using the BCP API. I will see if I can attempt to add those BCP extensions to pyODBC

[gordthompson]

You could try the OPENJSON trick described here:

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#passing-row-oriented-parameter-data-as-a-json-string

If you are inserting a pandas DataFrame there is a pre-baked method here:

https://gist.github.com/gordthompson/1fb0f1c3f5edbf6192e596de8350f205

[rad-pat]

Thanks, that's interesting. We don't use pandas Dataframe currently, but could possibly....

[rad-pat]

Unfortunately we cannot use this method so I looked into adding the BCP methods. I tried to get AI to write it as an experiment, but I don't think it understood the whole task. I don't have the time right now to plough into it, but if someone else did, this might be a starting point PlaidCloud@0b115ac

[gordthompson]

When you say "we cannot use this method", are you referring specifically to the pandas .to_sql() method, or are you talking about the OPENJSON approach in general?

[rad-pat]

Apologies, when I say cannot, I actually meant choose not. We prefer not to have additional code path for mssql to export data via OPENJSON approach. However, that could change if we cannot speed up by any other means.