Enable useBulkCopyForBatchInsert for other database types (not only Azure DW)

[skluyskens]

This is about extending #331 to other database types.
I already tried this with a simple example for an on-premise SQL Server Installation and I managed to get it working (by forcing the isAzureDW property to true).

This would allow this feature to be used much more often. It would also provide a workaround for #1072.

[peterbae]

This item is in our backlog, but it's not as high priority because Azure DW is the database type that benefits the most from this improvement due to the Azure server delay. By setting isAzureDW to true, the batch insert query might work for simple cases but extending this property officially to other databases is not trivial due to different syntaxes accepted by each database. Which database types are you looking to apply this property for?

[skluyskens]

Initially we would like to use it for SQL Server on Windows. In the longer run SQL Server on Azure and maybe on Linux would be nice too.
I understand that SQL Server might support many more syntaxes, but I'm ok with only accepting a subset of the supported syntaxes, as long as it's correctly documented. Only support INSERT INTO (, ,) VALUES (?, ?, ?) e.g. is totally fine. With the restriction that all column values should be parameterized, there are not that many options either as far as I know (maybe only options are quoted and non quoted table/column names).

[peterbae]

Thanks. The team will look into including this feature in the next production release.

[ulvii]

Hi @skluyskens ,
Could you give us numbers to understand the performance difference you are seeing when enabling useBulkCopyForBatchInsert against on-premise SQL Server?

[skluyskens]

Hi @ulvii,

I don't have test results for our application, but in .NET, for other applications I think we easily got 5 times better performance. https://stackoverflow.com/questions/40471004/can-i-get-bulk-insert-like-speeds-when-inserting-from-java-into-sql-server is talking about a factor 10 even.

[ulvii]

#1465 merged.