On Tue, May 28, 2019, at 5:29 AM, Sebastian Meinhardt wrote: > Thank you for the pointers! After digging into this some more, I found that > this is definitely related to something in cartain versions of SQLAlchemy. I > also tried different versions of cx_Oracle (6.4.1, 7.1.2, and 7.1.3) but that > had no effect on the performance of the statement. > > I did not try all possible combinations of SQLAlchemy and cx_Oracle but I > tried to do a little bisection on the SQLAlchemy version while keeping > cx_Oracle fixed at 7.1.3. What I found: > * 1.3.0 and above: Fine! > * 1.2.19*: *slow > * 1.2.12: slow > * 1.2.6: slow > * 1.2.3: slow > * 1.2.2: slow > * 1.2.1: Fine > * 1.2.0: Fine > * 1.1,18: Fine
yes so that is definitely this change: https://docs.sqlalchemy.org/en/13/changelog/changelog_12.html#change-858d90ed4ee9cbb340eff5b764f025ab and the issue that probably fixed it is https://docs.sqlalchemy.org/en/13/changelog/changelog_13.html#change-54fc6a60b98ea236b117f1a22bb56ac7 where the issue reported had to do with inserts but it ended up being a total rewrite (by no means the first) of how the cx_Oracle dialect handles string values. The performance-related change there is that it no longer uses cx_Oracle DBAPI's unicode conversion procedures and instead uses the very performant ones that come with SQLAlchemy. Python has a huge performance gotcha in the area of unicode coercion that has to do with how you invoke the decoding codec, PyMySQL was also affected by this for a long time. > So, the culprint is SQLAlchemy 1.2.2 and 1.3.0 fixes whatever was wrong. > While that solves my problem in an elegant way (I wanted to upgrade to 1.3+ > anyway) I will still try to find the root cause by profiling my application > the way you suggested. Maybe something can be learned from that to avoid > future regressions. However, that will take some time. I will be back with > the results, later! > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/1a2c2fdb-9de4-4679-b7a0-940974f6e507%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/1a2c2fdb-9de4-4679-b7a0-940974f6e507%40googlegroups.com?utm_medium=email&utm_source=footer>. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dc880ef5-4c89-431e-b09d-b2d4aec4a972%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.