it's up: https://github.com/oracle/python-cx_Oracle/issues/189
On Tue, Jun 19, 2018 at 8:24 PM, Mike Bayer <[email protected]> wrote: > On Tue, Jun 19, 2018 at 8:01 PM, Mike Bayer <[email protected]> wrote: >> On Tue, Jun 19, 2018 at 6:18 AM, naor volkovich <[email protected]> wrote: >>> I'm getting 1000x slower inserts with sqlalchemy in comparison with plain >>> inserts with cx_oracle. >>> The issue seems to be only when using the Insert object rather than a plain >>> insert sql as a string. >>> Inside the function _execute_context, it calls context.pre_exec() which >>> inside cx_oracle's pre_exec function, you have the call for the >>> self.set_input_sizes that causes the issue... >>> Without that set_input_sizes command, the code runs in seconds instead of >>> minutes. >>> In my example, all the data is from the type Text which in turn become CLOB >>> in Oracle. >> >> so the first suggestion is, use a lengthed String type instead, >> because if you are using Text(), that means you want to be able to >> store textual data of unlimited size. the cx_Oracle driver makes >> decisions based on sending that CLOB which I believe include being >> able to handle a very huge amount of text, though the current unit >> test suite in SQLAlchemy does not seem to exercise this. It does >> produce a failure which is that an empty string value will fail to >> persist correctly, it will store it as NULL instead, if you don't use >> setinputsizes. >> >> If you want to use CLOB for real and you want to skip the setinputizes >> and hope cx_Oracle does what you want you can do this: >> >> from sqlalchemy.dialects import oracle >> >> class OracleText(oracle.CLOB): >> def dialect_impl(self, dialect): >> return self >> >> class A(Base): >> __tablename__ = 'a' >> >> id = Column(Integer, primary_key=True) >> data = Column(Text().with_variant(OracleText(), "oracle")) >> >> I would recommend opening an upstream issue at >> https://github.com/oracle/python-cx_Oracle/ that binding CLOB is very >> slow but that might just be the way it is. Note that I communicate >> with Anthony several times a month about changing behaviors of >> setinputsizes() as the usage patterns keep getting adjusted so it >> might end up there as well. > > nevermind I'll put it up > > >> >> >> >> >> >> >>> >>> I'm using sqlalchemy 1.2.7 (Not the latest but doesn't seem like there's any >>> change in that code in 1.2.8 or in source) and cx_oracle 6.3.1 (latest) >>> My Python version is 3.6.x and if it matters, the code that calls the >>> sqlalchemy code is pandas 0.23.0 (0.23.1 is latest) but the issue seems to >>> be sqlalchemy-related. >>> I would love any help with it! For now I copied the execute function without >>> that line just to make things faster for now but it would be much better if >>> there was a real solution. >>> >>> -- >>> 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 [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
