Very slow dynamic SQL

[joegaber]

Scenario: I am considering Springto 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 Spring, or Spring + O/R mapping tool, to make this a more efficient DAO class?

Spring version: na

Full stack trace of any exception that occurs: na

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

The generated SQL :
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&#40;&#41; + " and processing_time < ? "
                        + "order by processing_time desc";
            &#125;
            pStmt = dbConn.prepareStatement&#40;strQuery&#41;;
            rs = pStmt.executeQuery&#40;&#41;;
            // loop thru rs and do stuff

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

Thx

[Colin Sampaleanu]

You can certainly use Spring to make access to your DataSource and Connection easier and safer, but this is not really about Spring, but rather about optimizing your SQL access, i.e. your db data structures and queries.