Probably has nothing to do with Cayenne, but still worth checking... I
suggest writing a small JDBC app with a PreparedStatement running this
query with preset parameters. If you have a few hours to spend, here
are some fun thing you can do:
* Try timing on the first run and on subsequent runs of the same
statement (it should become cached, giving some performance
benefits)...
* Time two pieces independently - the actual query execution and
iterating over the ResultSet reading the data.
* Cast your Connection to OracleConnection and try various
optimizations from the Oracle JDBC driver guide.
If you find a sweet spot, let us know :-)
Andrus
On Feb 11, 2005, at 3:05 PM, Bryan Lewis wrote:
> I don't _think_ the following�is a Cayenne issue, but maybe someone
> can shed some light on it.� Our app is generating�a relatively normal
> Oracle query, one that takes well under a second when manually entered
> in a SQL tool.� But when the app generates the same SQL, it takes 17
> to 22 seconds!
> �
> Maybe this is some kind of buffer limitation in the Oracle JDBC
> driver?��I don't think Cayenne's the culprit�because�the delay occurs
> between�the submission of the query�and the returning of the rows...
> no other log messages in between.� All the other queries in the app
> are working fine.
> �
> �
> P.S.� I said it was a relatively normal query, but it is a bit large.�
> 1740 characters.� Here it is, in case it matters.
> (The table is rather large too, with three 4000-character VARCHAR
> fields, but most of it's empty space... the average row length is 196
> bytes.)
> �
> SELECT DISTINCT t0.* ��� // actually a long list of attribute names
> FROM NIC t0, REPORT_NIC t1, BUSINESS t2, DBA_ t3
> WHERE
> t0.NIC_ID = t1.NIC_ID
> AND t0.NIC_ID = t2.NIC_ID
> AND t0.NIC_ID = t3.NIC_ID
> AND (((t0.OPT_OUT_OF_PUBLICATIONS = ?) OR
> ���� (t0.OPT_OUT_OF_PUBLICATIONS IS NULL))
> AND ((RTRIM(t0.COUNTRY_ID_MAIL) = ?) OR
> ���� (RTRIM(t0.COUNTRY_ID_MAIL) = ?) OR
> ���� (RTRIM(t0.COUNTRY_ID_MAIL) IS NULL))
> AND ((RTRIM(t0.COUNTRY_ID_STREET) = ?) OR
> ���� (RTRIM(t0.COUNTRY_ID_STREET) = ?) OR
> ���� (RTRIM(t0.COUNTRY_ID_STREET) IS NULL))
> AND ((RTRIM(t0.DIR_PUB_STATUS) = ?) OR
> ���� (RTRIM(t0.DIR_PUB_STATUS) = ?) OR
> ���� (t0.ISACTIVECLIENT = ?)
> ���� OR (t1.BASEDATE >= ?))
> AND (t0.NIC_ID IS NOT NULL)
> AND (t2.BUSINESS_ID IS NOT NULL)
> AND ((t0.COMPANYNAMEUPPER LIKE ?) OR
> ���� (t3.DBANAMEUPPER LIKE ?)))
> �
> [bind:� 0, 'USA', 'CAN', 'USA', 'CAN', 'U', 'U', 1,
> ������� '2002-09-27 00:00:00.0', 'SMITH%', 'SMITH%']
> �
This archive was generated by hypermail 2.0.0 : Sat Feb 12 2005 - 00:20:24 EST