java - 如何优化这个嵌套的for循环？

Question

我创建了一个在我的计算机上运行得足够好的方法(大约需要 4 秒才能完成)。然而，最终用户将在远程桌面环境中使用该方法，其中相同的请求需要 25-50 秒才能完成。我该如何优化这个程序？

private void compareAndPopulateArrays(List<String> listOfGenIdsFromXml,
        List<String> listOfGenIdsFromDB, String dburl)
        throws ClassNotFoundException, SQLException {
    mdbAccessor = new MDBAccessor();
    for (int x = 0; x < listOfGenIdsFromXml.size(); x++) {
        Boolean matching_id_found = false;
        for (int y = 0; y < listOfGenIdsFromDB.size(); y++) {
            if (listOfGenIdsFromXml.get(x)
                    .equals(listOfGenIdsFromDB.get(y)) || equalsLanguageCodeIgnore(listOfGenIdsFromXml.get(x),listOfGenIdsFromDB.get(y))) {
                addNewMatchingRecognition(listOfGenIdsFromXml,
                        listOfGenIdsFromDB, dburl, x, y);
                matching_id_found = true;
            }
        }
        if (!(matching_id_found == true)) {
            newRecognitions.add(new NewRecognition(listOfGenIdsFromXml
                    .get(x)));
        }
    }
}
    private void addNewMatchingRecognition(List<String> listOfGenIdsFromXml,
        List<String> listOfGenIdsFromDB, String dburl, int x, int y)
        throws ClassNotFoundException, SQLException {
    String gen_id_Xml = listOfGenIdsFromXml.get(x);
    String gen_id_DB = listOfGenIdsFromDB.get(y);
    int issue_id = mdbAccessor.getIssueId(gen_id_DB, dburl);
    String issue_expression = mdbAccessor.getIssueExpression(gen_id_DB,
            dburl);
    String issue_detail = mdbAccessor.getIssueDetails(gen_id_DB, dburl);
    matchingRecognitions.add(new MatchingRecognition(gen_id_Xml, gen_id_DB,
            issue_id, issue_detail, issue_expression));
}

所有 mdbAccessor 方法看起来都类似于以下内容:

public int getIssueId(String gen_id, String dburl) throws ClassNotFoundException,
        SQLException {
    Connection connection = setupConnection(dburl);
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement
            .executeQuery("SELECT issue_id FROM es_it WHERE gen_id='&&"
                    + gen_id + "' OR gen_id='&" + gen_id + "'");

    if (resultSet.next()){
        int getint = resultSet.getInt(1);
        resultSet.close();
        connection.close();
        return getint;
    }else{
        resultSet.close();
        connection.close();
        return -1;
    }
}

等于语言代码忽略:

    public boolean equalsLanguageCodeIgnore(String gen_id, String gen_id_DB) {
    if (genIdsAreEqualMinusLanguageCode(gen_id, gen_id_DB)) {
        return true;
    } else {
        return false;
    }
}

private boolean genIdsAreEqualMinusLanguageCode(String gen_id,
        String gen_id_DB) {
    return gen_id_DB.contains("P-XX-")
            && gen_id.substring(5).equals(gen_id_DB.substring(5));
}

新的和改进的 MDBAccessor 类:

public class MDBAccessor {
private Connection connection;
private Statement statement;

public void setupConnection(String dburl)
        throws ClassNotFoundException, SQLException {
        connection = DriverManager
            .getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq="
                    + dburl);
        statement = connection.createStatement();
}

public void closeConnection() throws SQLException{
    connection.close();
}
////
public int getIssueId(String gen_id) throws ClassNotFoundException,
            SQLException {
        ResultSet resultSet = statement
                .executeQuery("SELECT issue_id FROM es_it WHERE gen_id='&&"
                        + gen_id + "' OR gen_id='&" + gen_id + "'");

        if (resultSet.next()){
            int getint = resultSet.getInt(1);
            resultSet.close();
            return getint;
        }else{
            resultSet.close();
            return -1;
        }
    }

Answer 1

1. 获取一次项目，然后传递这些项目，而不是列表和索引。这将限制列表中执行的查找次数，我预计列表中的查找次数会很大。根据您的 Java 版本，您可能需要使用 for-each构建可读性。

2. 我希望您可以将数据库 Access 合并到单个查询中，这样可以节省时间。

   int issues_id = mdbAccessor.getIssueId(gen_id_DB, dburl); 字符串 Issue_Expression = mdbAccessor.getIssueExpression(gen_id_DB, 德 boolean )； String issues_detail = mdbAccessor.getIssueDetails(gen_id_DB, dburl);

3. 您似乎正在为每个查询打开和关闭数据库。打开它一次，并在函数结束时关闭它，因为打开和关闭数据库连接的成本很高(尤其是针对 Access IIRC)。您可能希望使连接对象成为 MDBAccessor 类的成员。请记住使用 try finally构造以确保其关闭。

---

建议重构以提高可读性

private void compareAndPopulateArrays(List<String> xmlGenIds,
        List<String> dbGenIds, String dbUrl)
        throws ClassNotFoundException, SQLException {
    //Better yet move it into an init method or the class constructor
    mdbAccessor = new MDBAccessor(dbUrl);
    for (String currXmlId : xmlGenIds) {
        Boolean matchingIdFound = false;
        for (String currDbId : dbGenIds) {
            if (currXmlId.equals(currDbId) || 
                    equalsLanguageCodeIgnore(currXmlId,currDbId)) {
                addNewMatchingRecognition(currDbId, currXmlId);
                matchingIdFound = true;
            }
        }
        if (!matchingIdFound) {
            newRecognitions.add(new NewRecognition(currDbId));
        }
    }
}