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.
