On May 13, 2014, at 4:21 AM, Thierry Florac <tflo...@gmail.com> wrote:
> 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? well there's another thread regarding pyodbc/MSSQL right now regarding the same thing. For whatever reason I forgot the most obvious solution which is literal_column(): q = s.query(Something).filter(Something.foo == literal_column("'my value'")) or text(): q = s.query(Something).filter(Something.foo == text("'my value'")) considering that this is probably a small subset of queries/expressions causing this issue. As i mentioned in the other thread, supporting the feature by which SQLA "literalizes" queries en masse would expose a large security surface which the project cannot support, even though the mechanism for this feature is present in a non-public way. Using explicit "literal_column()" or "text()" on just those queries/values that are problematic is much safer. > > Best regards, > Thierry > > > 2014-05-13 0:37 GMT+02:00 Michael Bayer <mike...@zzzcomputing.com>: > > On May 12, 2014, at 6:12 PM, Thierry Florac <tflo...@gmail.com> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.