favor fast_executemany over insertmanyvalues when set

Changed the bulk INSERT strategy used for SQL Server "executemany" with
pyodbc when ``fast_executemany`` is set to ``True`` by using
``fast_executemany`` / ``cursor.executemany()`` for bulk INSERT that does
not include RETURNING, restoring the same behavior as was used in
SQLAlchemy 1.4 when this parameter is set. For INSERT statements that use
RETURNING, the "insertmanyvalues" strategy continues to be used as it is
the only current strategy that supports RETURNING with bulk INSERT.

Previously, SQLAlchemy 2.0 would use "insertmanyvalues" for all INSERT
statements when ``use_insertmanyvalues`` was left at its default of
``False``, ignoring if ``fast_executemany`` was set.

New performance details from end users have shown that ``fast_executemany``
is still much faster for very large datasets as it uses ODBC commands that
can receive all rows in a single round trip, allowing for much larger
datasizes than the batches that can be sent by the current
"insertmanyvalues" strategy.

Fixes: #9586
Change-Id: I85955a10ba77c26cdc0c22e362a827d7aaef2852

2 files changed, 13 insertions, 5 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index b970f6c0a..31745d600 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -3019,6 +3019,8 @@ class MSDialect(default.DefaultDialect):
 
     use_insertmanyvalues = True
 
+    # note pyodbc will set this to False if fast_executemany is set,
+    # as of SQLAlchemy 2.0.9
     use_insertmanyvalues_wo_returning = True
 
     # "The incoming request has too many parameters. The server supports a "
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 978c501c4..f9f2479ad 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -291,9 +291,10 @@ Fast Executemany Mode
 ---------------------
 
 .. note:: SQLAlchemy 2.0 now includes an equivalent "fast executemany"
-   handler for INSERT statements that is more robust than the PyODBC feature;
+   handler for INSERT statements that is more robust than the PyODBC feature
+   (but is not quite as performant particularly for very large datasets);
    the feature is called :ref:`insertmanyvalues <engine_insertmanyvalues>`
-   and is enabled by default for all INSERT statements used by SQL Server.
+   and is enabled for all INSERT statements by default.
    SQLAlchemy's feature integrates with the PyODBC ``setinputsizes()`` method
    which allows for more accurate specification of datatypes, and additionally
    uses a dynamically sized, batched approach that scales to any number of
@@ -301,9 +302,12 @@ Fast Executemany Mode
 
    The SQL Server ``fast_executemany`` parameter may be used at the same time
    as ``insertmanyvalues`` is enabled; however, the parameter will not be used
-   in as many cases as INSERT statements that are invoked using Core
-   :class:`_dml.Insert` constructs as well as all ORM use no longer use the
-   ``.executemany()`` DBAPI cursor method.
+   for INSERT statements that include RETURNING.
+
+.. versionchanged:: 2.0.9 - ``fast_executemany`` executions will be used
+   for INSERT statements that don't include RETURNING, when
+   ``fast_executemany`` is set. Previously, ``use_insertmanyvalues`` would
+   cause ``fast_executemany`` to not be used in most cases.
 
 The PyODBC driver includes support for a "fast executemany" mode of execution
 which greatly reduces round trips for a DBAPI ``executemany()`` call when using
@@ -662,6 +666,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
             8,
         )
         self.fast_executemany = fast_executemany
+        if fast_executemany:
+            self.use_insertmanyvalues_wo_returning = False
 
     def _get_server_version_info(self, connection):
         try: