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 : Fri Feb 11 2005 - 15:05:36 EST