On May 13, 2014, at 4:21 AM, Thierry Florac <[email protected]> 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 <[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.
--
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.
