Scenario: I am considering Hibernate to solve data access problems. As an example, the code below is in the constructor of the ArAccountCollection class. the first query "stmt" takes ~40 minutes to return a resultset of 5500 records in production.

Question: How can I utilize Hibernate to make this a more efficient DAO class?

Hibernate version: na

Mapping documents: na

Code between sessionFactory.openSession() and session.close(): na

Full stack trace of any exception that occurs: na

Name and version of the database you are using: Name and version of the database you are using: MS SQL Server 2000 w/sp3

The generated SQL (show_sql=true):
package com.wfsc.lsa.harp.ar;

//imports
//creates a collection of HARP Accounts for the Annual Review
public class ArAccountCollection {
private java.util.Date processingTime;
private Vector accounts = new Vector();
public ArAccountCollection() {
}
public ArAccountCollection(java.util.Date processingTime) {
//initialize variables
dbConn = ConnectionManager.getConnection();
stmt = dbConn.createStatement();
//vw_harp_account_input is created from the following query:
// CREATE view vw_harp_account_input as
// SELECT
// harp.account_id, harp.new_flag, hlfico.fico, hlfico.fico_date,
// harp.cltv,
// harp.org_credit_line, harp.hist_high_home_val,
// alltel.occupancyCode, alltel.first_mortgage_principal_balance,
// alltel.special_code alltelSpecial_Code, alltel.delQTable,
// shaw.alltel_account, shaw.shaw_account, shaw.processing_time,
// shaw.first_mortgage_balance, shaw.currentBalance,
// shaw.credit_line,
// shaw.special_code shawSpecial_Code,
// RIGHT(shaw.sweep_flag, 1) ar_flag, shaw.twelveMonthDelinquent30,
// shaw.twelveMonthDelinquent60, shaw.twelveMonthDelinquent90,
// constants.min_fico,
// harp.account_type, harp.recDate,
// shaw.restrict_code, shaw.restrict_reason, shaw.restrict_date,
// harp.nextAnniversaryDate
// FROM constants, harp_accounts harp
// INNER JOIN harp_alltel_input alltel ON alltel.account_id =
// harp.account_id
// INNER JOIN harp_shaw_input shaw ON alltel.account_id =
// shaw.account_id
// LEFT JOIN harp_latest_fico hlfico ON harp.account_id =
// hlfico.account_id
// WHERE
// alltel.processing_time = shaw.processing_time
// and harp.new_flag = 'N'
// and harp.isClosed = 'N'
res = stmt.executeQuery("select * from vw_harp_account_input "
//JOE: changed = to > for testing.
+ " where processing_time > '"
+ Utility.dateToSqlString(processingTime)
+ "' order by account_id");
//loop thru each HARP Account and query additional info
while (res.next()) {
ArAccount ha = new ArAccount();
// do a bunch of other assignments
//use same connection and execute statements 2-5 and psmt
s2 = dbConn.createStatement();
r2 = s2.executeQuery("select "
+ "propertyValue from harp_property_value "
+ "where account_id = " + ha.getAccountID()
+ " and (source = 'MRAC' or source = 'appraisal') "
+ "and processing_time in "
+ "(select max(processing_time) from harp_property_value "
+ "where account_id = " + ha.getAccountID()
+ ") order by propertyValuationDate");
// loop thru r2 and do stuff

s3 = dbConn.createStatement();
r3 = s3.executeQuery("select "
+ "propertyValue from harp_property_value "
+ "where account_id = " + ha.getAccountID()
+ " and source = 'Initial value'");
// loop thru r3 and do stuff

s4 = dbConn.createStatement();
r4 = s4
.executeQuery("select max(processing_time) from harp_increase_action "
+ "where increased_amount > 0 and account_id = "
+ ha.getAccountID());
// loop thru r4 and do stuff
//processing_time is not indexed but needs to allow for nulls
strQuery = "select propertyValue "
+ "from harp_property_value " + "where account_id = "
+ ha.getAccountID() + " and processing_time < ? "
+ "order by processing_time desc";
}
pStmt = dbConn.prepareStatement(strQuery);
rs = pStmt.executeQuery();
// loop thru rs and do stuff

s5 = dbConn.createStatement();
r5 = s5.executeQuery("select flag_value "
+ "from harp_flag_history " + "where account_id = "
+ ha.getAccountID() + " and flag_type = 'he' "
+ " and date_changed = "
+ "(select max(date_changed) from harp_flag_history "
+ "where account_id = " + ha.getAccountID()
+ " and flag_type = 'he')");
// loop thru r5 and do stuff
// add to vector "accounts"
accounts.add(ha);
}
}
}


Debug level Hibernate log excerpt: na


Thx
Joe Gaber