Re: [cx-oracle-users] Strange problem with query execution...

[Doug H. <djh...@te...>]

On 2012-04-05 01:08, Thierry Florac wrote:
> Hi,
>
> I'm using cx_Oracle (actually in release 5.1.1, but with the same
> symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
> database (release 10.2.0.4).
>
> I'm building a web application querying a quite big table (nearly 3.5
> millions records), which is indexed correctly.
>
> After activating SQLAlchemy "echo SQL" feature, I noticed that :
>  - an SQLAlchemy request takes between 10 and 15 seconds
>  - the same request executed from an SQL worksheet (in Tora) is nearly
>    immediate.
>
> So I made a few tests using cx_Oracle without SQLAlchemy and the
> results are that :
>  - if I execute the same query with parameters (via
>    "cursor.execute("select...", param1=value1,...)", which is the way
>    SQLAlchemy works), it still takes between 10 and 15 seconds;
>  - but if I execute the same request with "hard-coded" parameters, the
>    result is immediate.
>
> Any idea (perhaps on the DB side) ?
>
> Thanks for any help,
> Thierry
Look for implicit type conversions in the where clause. If columns are
converted to match the parameter type, then an index on the column will
not be used. You may need to include explicit type conversion around the
parameters to prevent implicit conversion of indexed columns. This is a
subtle problem that can be very hard to detect, but cause horrible query
performance.

For example, a clause like "numeric_column = :param1" may need to be
coded as "numeric_column = to_number(:param1)".

-- 
Doug Henderson, Calgary, Alberta, Canada