CODEWITHSUNDEEP
sqlms-accessoptimizationquery-optimization
Soroush Hakami
Soroush Hakami

Reputation: 5396

MS Access SQL Query optimization

Got this ugly piece of code that I'd like to make run faster. It worked alright until I had to add the setMotherTableValues() method aswell. As it is now I'm doing that mothertable-lookup about 48 000 times, and it's killing the program. Is there any way to improve this? I don't have authority to change any columns in the databases. 

public void populateMdbRecognitions(ArrayList<MdbRecognition> recognitions, String genid,
        String issueid, String issueexpression, String issuedetails,
        String tablename) throws SQLException {
    ResultSet rs = statement.executeQuery("SELECT `" + genid + "`, `" + issueid + "`, `"
            + issueexpression + "`, `" + issuedetails + "` FROM " + tablename);
    while (rs.next()) {
        String gen_id = removeAmpsFromGenId(rs.getString(genid));
        String issue_id = rs.getString(issueid);
        String issue_details = rs.getString(issuedetails);
        String issue_expression = rs.getString(issueexpression);
        MdbRecognition recognition = new MdbRecognition(gen_id, issue_id,
                issue_details, issue_expression, tablename);
        recognitions.add(recognition);
    }
    for (MdbRecognition mdbRecognition : recognitions){
        setMotherTableValues(mdbRecognition);
    }

}

public void setMotherTableValues(MdbRecognition mdbRecognition) throws SQLException {
    ResultSet rs = statement.executeQuery("SELECT `mandatory?`, category, subcategory FROM __MOTHER_ISSUE_TABLE_7 WHERE issue_id ='" + mdbRecognition.getIssueId()+"'");
    while (rs.next()){
        mdbRecognition.setMandatory(rs.getString("mandatory?").trim());
        mdbRecognition.setCategory(rs.getString("category").trim());
        mdbRecognition.setSubcategory(rs.getString("subcategory").trim());
    }
}

Upvotes: 0

Views: 418

Answers (2)

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95582

Is this what you're doing?

  • Select all the rows from an arbitrary table.
  • Remove ampersands from gen_id.
  • Save every row from that arbitrary table in a collection.

Then, for each item in that collection,

  • Select three columns from __MOTHER_ISSUE_TABLE_7 by issue_id.
  • Update values in that collection with values from those three columns.

Since you say you're just storing the strings to update a GUI, then you almost certainly need to change this:

ResultSet rs = statement.executeQuery("SELECT `" + genid + "`, `" + issueid + "`, `"
        + issueexpression + "`, `" + issuedetails + "` FROM " + tablename);

to return exactly what your mdbrecognition object needs in order to do its job. You should see a breathtaking speedup if you do that.

Upvotes: 1

Tim
Tim

Reputation: 5421

Better to rewrite the first query, joining the mother issue table there.

Upvotes: 1

Related Questions