I did the things you suggested. And as we suspected, it had nothing to do
with Cayenne. I executed a PreparedStatement over JDBC, without Cayenne
getting involved. It was as slow as before. If I created the same query
with direct SQL (using a SQLTemplate), it was much faster. It looks like a
problem in the Oracle 8i JDBC driver, perhaps related to the handling of
PreparedStatements.
So I didn't find a sweet-spot fix (other than refactoring the query into
smaller pieces), but I wanted to close the case by saying that Cayenne
wasn't guilty. :-)
P.S. It was very nice to be able to step through the source in the Eclipse
debugger and see that the time was being spent in DataNode.runSelect()'s
statement: prepStmt.executeQuery(). Let's hear it for open-source.
----- Original Message -----
From: "Andrus Adamchik" <andru..bjectstyle.org>
To: <cayenne-use..bjectstyle.org>
Sent: Saturday, February 12, 2005 12:20 AM
Subject: Re: slow oracle query
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 : Mon Feb 14 2005 - 18:19:57 EST