Repaired a major shortcoming which was identified in the :ref:`engine_insertmanyvalues` performance optimization feature first introduced in the 2.0 series. This was a continuation of the change in 2.0.9 which disabled the SQL Server version of the feature due to a reliance in the ORM on apparent row ordering that is not guaranteed to take place. The fix applies new logic to all "insertmanyvalues" operations, which takes effect when a new parameter :paramref:`_dml.Insert.returning.sort_by_parameter_order` on the :meth:`_dml.Insert.returning` or :meth:`_dml.UpdateBase.return_defaults` methods, that through a combination of alternate SQL forms, direct correspondence of client side parameters, and in some cases downgrading to running row-at-a-time, will apply sorting to each batch of returned rows using correspondence to primary key or other unique values in each row which can be correlated to the input data. Performance impact is expected to be minimal as nearly all common primary key scenarios are suitable for parameter-ordered batching to be achieved for all backends other than SQLite, while "row-at-a-time" mode operates with a bare minimum of Python overhead compared to the very heavyweight approaches used in the 1.x series. For SQLite, there is no difference in performance when "row-at-a-time" mode is used. It's anticipated that with an efficient "row-at-a-time" INSERT with RETURNING batching capability, the "insertmanyvalues" feature can be later be more easily generalized to third party backends that include RETURNING support but not necessarily easy ways to guarantee a correspondence with parameter order. Fixes: #9618 References: #9603 Change-Id: I1d79353f5f19638f752936ba1c35e4dc235a8b7c
Removed versionadded and versionchanged for version prior to 1.2 since they are no longer useful. Change-Id: I5c53d1188bc5fec3ab4be39ef761650ed8fa6d3e
In #9618 we both can look to re-enable insertmanyvalues for SQL Server, and also likely *disable* its use for the ORM unit of work specifically, since that's really where the only problem is, and it will likely be for all dialects, not just SQL Server. An approach using sentinel columns will be rolled out for the unit of work use case. Change-Id: I3358e30839491769db95b4ac042a661271df3929 References: #9618 References: #9603
we will keep trying to find workarounds, however this patch is the "turn it off" patch Due to a critical bug identified in SQL Server, the SQLAlchemy "insertmanyvalues" feature which allows fast INSERT of many rows while also supporting RETURNING unfortunately needs to be disabled for SQL Server. SQL Server is apparently unable to guarantee that the order of rows inserted matches the order in which they are sent back by OUTPUT inserted when table-valued rows are used with INSERT in conjunction with OUTPUT inserted. We are trying to see if Microsoft is able to confirm this undocumented behavior however there is no known workaround, other than it's not safe to use table-valued expressions with OUTPUT inserted for now. Fixes: #9603 Change-Id: I4b932fb8774390bbdf4e870a1f6cfe9a78c4b105
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
the verbiage here was ambiguous previously. Change-Id: I452ae85bd8b5469d4103970e99cfac752b508274
pymssql seems to be maintained again and seems to be working completely, so let's try re-enabling it. Fixed issue in the new :class:`.Uuid` datatype which prevented it from working with the pymssql driver. As pymssql seems to be maintained again, restored testing support for pymssql. Tweaked the pymssql dialect to take better advantage of RETURNING for INSERT statements in order to retrieve last inserted primary key values, in the same way as occurs for the mssql+pyodbc dialect right now. Identified that the ``sqlite`` and ``mssql+pyodbc`` dialects are now compatible with the SQLAlchemy ORM's "versioned rows" feature, since SQLAlchemy now computes rowcount for a RETURNING statement in this specific case by counting the rows returned, rather than relying upon ``cursor.rowcount``. In particular, the ORM versioned rows use case (documented at :ref:`mapper_version_counter`) should now be fully supported with the SQL Server pyodbc dialect. Change-Id: I38a0666587212327aecf8f98e86031ab25d1f14d References: #5321 Fixes: #9414
The newly added comment reflection and rendering capability of the MSSQL dialect, added in :ticket:`7844`, will now be disabled by default if it cannot be determined that an unsupported backend such as Azure Synapse may be in use; this backend does not support table and column comments and does not support the SQL Server routines in use to generate them as well as to reflect them. A new parameter ``supports_comments`` is added to the dialect which defaults to ``None``, indicating that comment support should be auto-detected. When set to ``True`` or ``False``, the comment support is either enabled or disabled unconditionally. Fixes: #9142 Change-Id: Ib5cac31806185e7353e15b3d83b580652d304b3b
Fixed bug where a schema name given with brackets, but no dots inside the name, for parameters such as :paramref:`_schema.Table.schema` would not be interpreted within the context of the SQL Server dialect's documented behavior of interpreting explicit brackets as token delimiters, first added in 1.2 for #2626, when referring to the schema name in reflection operations. The original assumption for #2626's behavior was that the special interpretation of brackets was only significant if dots were present, however in practice, the brackets are not included as part of the identifier name for all SQL rendering operations since these are not valid characters within regular or delimited identifiers. Pull request courtesy Shan. Fixes: #9133 Closes: #9134 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/9134 Pull-request-sha: 5dac87c82cd3063dd8e50f0075c7c00330be6439 Change-Id: I7a507bc38d75a04ffcb7e920298775baae22c6d1
Follow up of I07b72e6620bb64e329d6b641afa27631e91c4f16 Change-Id: I1f61974bf9cdc3da5317e546d4f9b649c2029e4d