Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.0.5

[b]Mapping documents:
<hibernate-mapping>

<class name="com.vzwcorp.ifd.vh.account.AccountSummary"
table="account_summary" lazy="false" >
<comment>AccountSummary class maps to view account_summary</comment>

<id name="primitiveId" column="account_id" access="field">
<generator class="sequence">
<param name="sequence">account_serial</param>
</generator>
</id>
<property name="name" column="name" />
<property name="accountNumber" column="account_no" />
<property name="currentTotal" column="current_total" />
<property name="serviceCount" column="service_count" />
<property name="investigationId" column="investigation_id" />
<property name="referralSourceDescription" column="referral_source" />
<property name="primitiveInvestigationStateId" column="state" access="field"/>
<property name="primitiveInvestigationClassificationId" column="classification" access="field"/>
<property name="areaName" column="area_name" />
<property name="ssfId" column="ssf_id" />
<property name="ssfOffice" column="ssf_office" />
<property name="ssfRepId" column="ssf_rep_id" />
<property name="login" column="login_id" />
</class>

</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():
Using spring hibernate template:

return getHibernateTemplate().find(
"FROM AccountSummary su " +
"WHERE su.id IN (SELECT a.id" +
" FROM Account a " +
" WHERE (a.address.address1 LIKE ? " +
" OR a.address.address2 LIKE ? " +
" OR a.address.address3 LIKE ?) " +
(valid ? " AND a.address.state = ? " : "") +
" AND a.billingSystem.active = 1 " +
" AND a.dateCreated > SYSDATE - ?) " +
" AND ROWNUM <= " + MAX_RESULT,
args);


Full stack trace of any exception that occurs: n/a

Name and version of the database you are using:Oracle 9i

The generated SQL (show_sql=true):
SELECT accountsum0_.account_id AS account1_, accountsum0_.NAME AS name1_,
accountsum0_.account_no AS account3_1_,
accountsum0_.current_total AS current4_1_,
accountsum0_.service_count AS service5_1_,
accountsum0_.investigation_id AS investig6_1_,
accountsum0_.referral_source AS referral7_1_,
accountsum0_.state AS state1_,
accountsum0_.classification AS classifi9_1_,
accountsum0_.area_name AS area10_1_, accountsum0_.ssf_id AS ssf11_1_,
accountsum0_.ssf_office AS ssf12_1_,
accountsum0_.ssf_rep_id AS ssf13_1_,
accountsum0_.login_id AS login14_1_
FROM account_summary accountsum0_
WHERE (accountsum0_.account_id IN (
SELECT account1_.ID
FROM ACCOUNT account1_,
address address2_,
billing_system billingsys5_
WHERE account1_.billing_system_id = billingsys5_.ID
AND account1_.ID = address2_.account_id
AND ( address2_.address1 LIKE ?
OR address2_.address2 LIKE ?
OR address2_.address3 LIKE ?
)
AND billingsys5_.active = 1
AND account1_.created > SYSDATE - ?)
)
AND ROWNUM <= 100

Debug level Hibernate log excerpt: N/A

When I run this query in hibernate (I cut and paste from log file), with values '26471%PARK%', '26471%PARK%', '26471%PARK%', 90 the query takes about a half hour. When I run the exact query in sqlplus or toad it runs instantly. This query does not return any records, so there should be little overhead in mapping the raw data to objects.

Am I missing something here? How can there be such a difference?

Thanks in advance,
Amir