Hi Michael, We already tried to explain the Oracle query execution plan with our DBA. The result is that the Oracle server prefers to make a full table scan instead of using the index; the reason is still indeterminate (the database statistics are up to date), we checked arguments data type and as previously said the equivalent SQL request executed without prepared statement is OK. Of course I could give a raw SQL to SA but that is in the opposite philosophy of what an ORM should be used, no? I think you talked somewhere else of an "hidden" feature which allows SA to generate "literal SQL queries". Even if only a small set or arguments data types are supported, how could I use it?
Best regards, Thierry 2014-05-13 0:37 GMT+02:00 Michael Bayer <[email protected]>: > > On May 12, 2014, at 6:12 PM, Thierry Florac <[email protected]> wrote: > > > Hi, > > > > I use Python and SQLAlchemy to access an Oracle 11 database. As far as I > think, SQLAlchemy always use prepared statements. > > SQLAlchemy has no control over this as the DBAPI has no prepared statement > system exposed publicly. This is entirely dependent on the choices that > cx_Oracle makes internally. SQLAlchemy's interaction with the DBAPI is > exactly: > > cursor = connection.cursor() > cursor.set_input_sizes(< some params> ) # we normally don't use this but > cx_oracle needs this for some datatypes, see > http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes > cursor.execute("your SQL statement", { your parameters }) > > everything else is how cx_Oracle choices to deal with these inputs at the > OCI level and there's a lot that can go wrong, most of which is not > something SQLAlchemy can anticipate or have control over. > > > > > On a huge table (4 millions records), correctly indexed, SQLAlchemy > filters queries doesn't use the index, so doing a full table scan is very > slow ; using the same SQL code in a "raw" SQL editor (SQLplus) make Oracle > use the index with good performances. > > SQLAlchemy's job here is to send a string plus parameters to the cx_Oracle > execute() method. SQLAlchemy also calls cx_oracle's setinputsizes() as > well in order to deal with some parameter conversion issues we've observed > with cx_Oracle; these may be involved with the choices that cx_Oracle makes > which could affect query planning. > > Turn on echo=True on your create_engine() and you will see the full > conversation with the DBAPI. The issue here is with the choices that > cx_Oracle makes, not that of SQLAlchemy so you should distill your queries > to a raw cx_Oracle case, using a usage as illustrated above and possibly in > conjunction with "EXPLAIN", to further determine the cause of the issue. > > > -- > 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 http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- http://www.imagesdusport.com -- http://www.ztfy.org -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
