java - 使用 java 比较和更新数据时如何解决巨大的性能问题？

Question

我想将两个列表与具有大约 30 个值的相同类型的对象进行比较。当存在差异时，应将新值更新到数据库中。
我的代码变得非常慢的部分，比如每分钟更新一次，是什么时候应该更新数据。

从 db 和 Excel 读取所有数据集速度很快。但是当涉及到下面的代码时，它已经开始在 200 个数据集之后几乎停止。

我无法为您提供完整的代码，因为 getLists() 方法调用了很多函数，在我看来这些代码太多了。但也许你仍然可以帮助我解决我的问题，因为性能变低的相关部分发生在给定的代码中。

也许以某种方式创建了很多对象或类似的东西？

编辑：我用 IntelliJ 运行程序。当我关闭正在运行的应用程序并重新启动它时，它会非常快地加载上一次已经缓慢加载的数据集，但随后又会变慢。也许缓存有问题？

这是我的代码：

public class MainWindow_Controller {

List<Pensioner> pensionerDB = new ArrayList();
List<Pensioner> pensionerExcel = new ArrayList();

Boolean checkStatusChange = false;
String pathDB = "";

@FXML
private TextField tfDBPath;

/**
 * This method compares the pensioners from database with the ones from the Excel head file.
 * Based on matching pensionInsuranceNumbers the method checks if the status is different.
 * If it is different, the status gets updated.
 *
 * @param event
 */
@FXML
void getStatusChange(ActionEvent event) throws FileNotFoundException, IllegalAccessException, SQLException {
    String type = "Statuswechsel";
    if (pensionerDB.isEmpty() || pensionerExcel.isEmpty()) {
        getLists();
    }

    pathDB = tfDBPath.getText();
    //load pensioners from database in first list
    pensionerDB = array[0];

    //load pensioners from excel head file in second list
    pensionerExcel = array[1];

    //compare pensionInsuranceNumber from first list with pensionInsuranceNumber from second list and move missing datasets to third list
    List<Pensioner> result = new ArrayList();

    Database.connect(pathDB);

    //iterates over the two lists with pensioners in database and pensioners in Excel files and adds the dataset with matching pensionInsuranceNumbers into the result list.
    for (int i = 0; i < pensionerExcel.size(); i++) {
        System.out.println("Processing Excelrow Number: " + i);
        for (int j = 0; j < pensionerDB.size(); j++) {
            updatePensionerData(pensionerExcel, pensionerDB, pathDB, i, j);
            if (pensionerExcel.get(i).getPensionInsuranceNumber() == pensionerDB.get(j).getPensionInsuranceNumber() && pensionerExcel.get(i).getStatusOld() != pensionerDB.get(j).getStatusOld()) {
                checkStatusChange = true;
                pensionerDB.get(j).setStatusNew(pensionerDB.get(j).getStatusOld());
                pensionerDB.get(j).setStatusOld(pensionerExcel.get(i).getStatusOld());
                result.add(pensionerDB.get(j));
                break;
            } else if (pensionerExcel.get(i).getPensionInsuranceNumber() == pensionerDB.get(j).getPensionInsuranceNumber() && pensionerExcel.get(i).getStatusOld() == pensionerDB.get(j).getStatusOld()) {
                break;
            }
        }
    }
    Database.close();

}

public void updatePensionerData(List<Pensioner> pensionerExcel, List<Pensioner> pensionerDB, String pathDB, int i, int j) {
    if (pensionerExcel.get(i).getPensionInsuranceNumber() == pensionerDB.get(j).getPensionInsuranceNumber()) {
        if (pensionerExcel.get(i).getIdkz() != pensionerDB.get(j).getIdkz()) {
            Database.updateIdkz(pensionerExcel.get(i), pathDB);
        }
        if (!pensionerExcel.get(i).getCompany().equals(pensionerDB.get(j).getCompany())) {
            Database.updateCompany(pensionerExcel.get(i), pathDB);
        }
        if (pensionerExcel.get(i).getPersonelId() != pensionerDB.get(j).getPersonelId()) {
            Database.updatePersonelId(pensionerExcel.get(i), pathDB);
        }

        if (!pensionerExcel.get(i).getBirthDate().isEqual(pensionerDB.get(j).getBirthDate())) {
            Database.updateBirthDate(pensionerExcel.get(i), pathDB);
        }
        //...
        //26 more if statements for the other values
        //...
    }
}

}

我的数据库方法：

public static void updateIdkz(Pensioner p, String pathDB) {
    String update = String.format("UPDATE Pensionär SET idkz = ?");
    try {
        ps = connection.prepareStatement(update);
        ps.setInt(1, p.getIdkz());
        ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static void updatePersonelId(Pensioner p, String pathDB) {
    String update = String.format("UPDATE Pensionär SET PersNr = ? Where pknr = ?");
    try {
        ps = connection.prepareStatement(update);
        ps.setInt(1, p.getPersonelId());
        ps.setInt(2, p.getPensionInsuranceNumber());
        ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static void updateBirthDate(Pensioner p, String pathDB) {
    String update = String.format("UPDATE Pensionär SET Geburtsdatum = ? Where pknr = ?");
    try {
        ps = connection.prepareStatement(update);
        ps.setDate(1, Date.valueOf(p.getBirthDate()));
        ps.setInt(2, p.getPensionInsuranceNumber());
        ps.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

标签： javaperformancems-accessprepared-statement

Answer 1

这样的事情会更有效率。您可以重复使用 PreparedStatements 甚至批量更新以使事务更快。另外，正如其他人所说，请记住关闭您的 PreparedStatements。

class Test implements Closeable {

    // reusable PreparedStatements
    private final PreparedStatement updateIdkz;
    private final PreparedStatement updatePersonelId;
    private final PreparedStatement updateBirthDate;

    public Test(Connection con) throws SQLException {
        this.updateIdkz = con
            .prepareStatement("UPDATE Pensionär SET idkz = ?");
        this.updatePersonelId = con
            .prepareStatement("UPDATE Pensionär SET PersNr = ? Where pknr = ?");
        this.updateBirthDate = con
            .prepareStatement("UPDATE Pensionär SET Geburtsdatum = ? Where pknr = ?");
    }

    /** Closes all the prepared statements. */
    @Override
    public void close() throws IOException {
        try {
            updateIdkz.close();
        } catch (SQLException e) {
        }
        try {
            updatePersonelId.close();
        } catch (SQLException e) {
        }
        try {
            updateBirthDate.close();
        } catch (SQLException e) {
        }
    }

    public void addBatchUpdateIdkz(Pensioner p) {
        try {
            updateIdkz.clearParameters();
            updateIdkz.setInt(1, p.getIdkz());
            updateIdkz.addBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void addBatchUpdatePersonelId(Pensioner p) {
        try {
            updatePersonelId.clearParameters();
            updatePersonelId.setInt(1, p.getPersonelId());
            updatePersonelId.setInt(2, p.getPensionInsuranceNumber());
            updatePersonelId.addBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void addBatchUpdateBirthDate(Pensioner p) {
        try {
            updateBirthDate.clearParameters();
            updateBirthDate.setDate(1, Date.valueOf(p.getBirthDate()));
            updateBirthDate.setInt(2, p.getPensionInsuranceNumber());
            updateBirthDate.addBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void executeBatchUpdateIdkz() throws SQLException {
        updateIdkz.executeBatch();
    }

    public void executeBatchUpdatePersonelId() throws SQLException {
        updatePersonelId.executeBatch();
    }

    public void executeBatchUpdateBirthDate() throws SQLException {
        updateBirthDate.executeBatch();
    }

    public static void main(String[] args) {

        Pensioner p1 = null, p2 = null, p3 = null, p4 = null;
        Connection con = null;

        // try with resources to ensure you close the prepared statements
        try (Test t = new Test(con);) {
            // Update multiple Idkz
            t.addBatchUpdateIdkz(p1);
            t.addBatchUpdateIdkz(p2);
            t.addBatchUpdateIdkz(p3);
            t.addBatchUpdateIdkz(p4);
            t.executeBatchUpdateIdkz();

            // Update multile PersonelId
            t.addBatchUpdatePersonelId(p1);
            t.addBatchUpdatePersonelId(p2);
            t.addBatchUpdatePersonelId(p3);
            t.addBatchUpdatePersonelId(p4);
            t.executeBatchUpdatePersonelId();

            // etc...
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
}