On Tue, Jun 19, 2018 at 6:18 AM, naor volkovich <naor200...@gmail.com> 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.
>
> 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 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.
> 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.
For more options, visit https://groups.google.com/d/optout.
