Skip to content

Commit cf6872d

Browse files
zzzeekzzzeek
committed
add deterministic imv returning ordering using sentinel columns
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: sqlalchemy#9618 References: sqlalchemy#9603 Change-Id: I1d79353f5f19638f752936ba1c35e4dc235a8b7c
1 parent 63f5149 commit cf6872d

71 files changed

Lines changed: 5266 additions & 660 deletions

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎doc/build/changelog/unreleased_20/9618.rst‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
.. change::
2+
:tags: bug, engine
3+
:tickets: 9618, 9603
4+
5+
Repaired a major shortcoming which was identified in the
6+
:ref:`engine_insertmanyvalues` performance optimization feature first
7+
introduced in the 2.0 series. This was a continuation of the change in
8+
2.0.9 which disabled the SQL Server version of the feature due to a
9+
reliance in the ORM on apparent row ordering that is not guaranteed to take
10+
place. The fix applies new logic to all "insertmanyvalues" operations,
11+
which takes effect when a new parameter
12+
:paramref:`_dml.Insert.returning.sort_by_parameter_order` on the
13+
:meth:`_dml.Insert.returning` or :meth:`_dml.UpdateBase.return_defaults`
14+
methods, that through a combination of alternate SQL forms, direct
15+
correspondence of client side parameters, and in some cases downgrading to
16+
running row-at-a-time, will apply sorting to each batch of returned rows
17+
using correspondence to primary key or other unique values in each row
18+
which can be correlated to the input data.
19+
20+
Performance impact is expected to be minimal as nearly all common primary
21+
key scenarios are suitable for parameter-ordered batching to be
22+
achieved for all backends other than SQLite, while "row-at-a-time"
23+
mode operates with a bare minimum of Python overhead compared to the very
24+
heavyweight approaches used in the 1.x series. For SQLite, there is no
25+
difference in performance when "row-at-a-time" mode is used.
26+
27+
It's anticipated that with an efficient "row-at-a-time" INSERT with
28+
RETURNING batching capability, the "insertmanyvalues" feature can be later
29+
be more easily generalized to third party backends that include RETURNING
30+
support but not necessarily easy ways to guarantee a correspondence
31+
with parameter order.
32+
33+
.. seealso::
34+
35+
:ref:`engine_insertmanyvalues_returning_order`
36+
37+
38+
.. change::
39+
:tags: bug, mssql
40+
:tickets: 9618, 9603
41+
42+
Restored the :term:`insertmanyvalues` feature for Microsoft SQL Server.
43+
This feature was disabled in version 2.0.9 due to an apparent reliance
44+
on the ordering of RETURNING that is not guaranteed. The architecture of
45+
the "insertmanyvalues" feature has been reworked to accommodate for
46+
specific organizations of INSERT statements and result row handling that
47+
can guarantee the correspondence of returned rows to input records.
48+
49+
.. seealso::
50+
51+
:ref:`engine_insertmanyvalues_returning_order`
52+

‎doc/build/changelog/unreleased_20/oracle_uuid.rst‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
.. change::
2+
:tags: bug, oracle
3+
4+
Fixed issue where the :class:`_sqltypes.Uuid` datatype could not be used in
5+
an INSERT..RETURNING clause with the Oracle dialect.

‎doc/build/changelog/whatsnew_20.rst‎

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -974,21 +974,20 @@ Driver SQLA 1.4 Time (secs) SQLA 2.0 Time (secs)
974974
sqlite+pysqlite2 (memory) 6.204843 3.554856
975975
postgresql+asyncpg (network) 88.292285 4.561492
976976
postgresql+psycopg (network) N/A (psycopg3) 4.861368
977+
mssql+pyodbc (network) 158.396667 4.825139
977978
oracle+cx_Oracle (network) 92.603953 4.809520
978979
mariadb+mysqldb (network) 71.705197 4.075377
979980
============================ ==================== ====================
980981

981-
.. mssql+pyodbc (network) .. 158.396667 .. 4.825139
982982

983983

984984
.. note::
985985

986-
.. [#] The feature is disabled for SQL Server as of SQLAlchemy 2.0.9 due
987-
to incompatibilities in how table-valued expressions are handled by
988-
SQL Server regarding the ORM unit of work. An upcoming release will
989-
re-enable it with unit-of-work oriented adjustments.
990-
See https://github.com/sqlalchemy/sqlalchemy/issues/9603 and
991-
https://github.com/sqlalchemy/sqlalchemy/issues/9618.
986+
.. [#] The feature is was temporarily disabled for SQL Server in
987+
SQLAlchemy 2.0.9 due to issues with row ordering when RETURNING is used.
988+
In SQLAlchemy 2.0.10, the feature is re-enabled, with special
989+
case handling for the unit of work's requirement for RETURNING to be
990+
ordered.
992991
993992
Two additional drivers have no change in performance; the psycopg2 drivers,
994993
for which fast executemany was already implemented in SQLAlchemy 1.4,

0 commit comments

Comments
 (0)