Hi Michael, I just tried using "literal_column" function and now performances are "as good as they can" :-/ As my problem is only limited to a single use case, using this method, even if not perfect (arguments must be carefully verified!), if far better than a global option.
Many thanks, Thierry 2014-05-13 12:20 GMT+02:00 Michael Bayer <mike...@zzzcomputing.com>: > > 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. > -- 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.
