Slow insert with cx_oracle through sqlalchemy

[naor volkovich]

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.

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.

[Mike Bayer]

On Tue, Jun 19, 2018 at 6:18 AM, naor volkovich <naor2...@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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

nevermind I'll put it up

[Mike Bayer]

it's up: https://github.com/oracle/python-cx_Oracle/issues/189

[naor volkovich]

Thanks for opening an issue in cx_oracle, I hope the issue will be solved in their side.
You suggested using a lengthed string but I'm using sqlalchemy to create my table as well and when it sees text, it uses clob automatically... Is there any way around it like maybe say that small strings will be of another type of even make it the default and handle errors with big texts myself?

[Mike Bayer]

the feature at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/795/
will be part of SQLAlchemy 1.2.9 and will allow the following program
to work:


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event

Base = declarative_base()

class A(Base):
__tablename__ = 'a'

id = Column(Integer, primary_key=True)

data = Column(Text)


e = create_engine("oracle://scott:tiger@oracle1120", echo=True)

# 200x speedup
@event.listens_for(e, "do_setinputsizes")
def _remove_type(inputsizes, cursor, statement, parameters, context):
print(inputsizes)
for param, dbapitype in list(inputsizes.items()):
if dbapitype is e.dialect.dbapi.CLOB:
del inputsizes[param]


Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)
s.add_all([A(id=i, data="foo%d" % i) for i in range(1000)])
s.commit()

On Wed, Jun 20, 2018 at 2:06 AM, naor volkovich <naor2...@gmail.com> wrote:
> Thanks for opening an issue in cx_oracle, I hope the issue will be solved in their side.
> You suggested using a lengthed string but I'm using sqlalchemy to create my table as well and when it sees text, it uses clob automatically... Is there any way around it like maybe say that small strings will be of another type of even make it the default and handle errors with big texts myself?
>

[naor volkovich]

Thanks for adding this feature!
Since I don't know SQLAlchemy that good yet, can you explain exactly what's going on?
Do I have to do all the Base related lines? What do they do?
If I add this event for an engine in one file/class, does it affect that engine when passed around to other files/classes too?

בתאריך יום רביעי, 27 ביוני 2018 בשעה 21:15:22 UTC+3, מאת Mike Bayer:

[Mike Bayer]

As you know SQLAlchemy has to call setinputsizes.  This is an event hook that let's you change what it will do.   You use event.listen along with your create_engine call and that's it.  The rest of that program is just an example.