时间戳

首页 > 解决方案 > 需要优化查询以插入记录

java - 需要优化查询以插入记录

问题描述

我写了一段 JDBC 模板代码，它在表中插入记录，但问题是我的执行卡在这个特定的片段上，似乎某种挂断。我没有弄清楚原因，因为查询在 sqldeveloper 中正常运行

List<SalaryDetailReport> reports = salaryDetailReportDAO.findAll(tableSuffix, regionId, circleId);
// the above line find the required data, if data is found then it proceeds

if (reports != null && reports.size() > 0) {
    for (SalaryDetailReport salaryDetail : reports) {
        try {
            SalaryDetail sd = new SalaryDetail();
            sd.setDetailReport(salaryDetail);
            salaryDetailDAO.save(sd, tableSuffix);
        } catch (Exception e) {
            log.error("Error occured", e);
            e.printStackTrace();
            throw new MyExceptionHandler(" Error :" + e.getMessage());
        }
    }

    System.out.println("data found");

} else {
    log.error("Salary Record Not Found.");
    throw new MyExceptionHandler("No record Found.");
}

你们看到了 try-catch ，我的执行卡在 try 和 catch 中，这是我的实现类中的插入代码。当我评论上面的代码时，我的应用程序工作正常，但是为什么我的应用程序卡在这里，我无法弄清楚，请帮助我

@Override
public void save(SalaryDetail details, String tableSuffix) {
    String tabName = "SALARY_DETAIL_" + tableSuffix;
//  String q = "INSERT INTO " + tabName + "(ID "
    String q = "INSERT INTO SALARY_DETAIL_TBL "
            + "                  (ID "
            + "                  ,EMP_NAME "
            + "                  ,EMP_CODE "
            + "                  ,NET_SALARY "
            + "                  ,YYYYMM "
            + "                  ,PAY_CODE "
            + "                  ,EMP_ID "
            + "                  ,PAY_CODE_DESC "
            + "                  ,REMARK "
            + "                  ,PAY_MODE ) "
            + "          (SELECT (sd.SALARY_REPORT_ID) ID "
            + "                  ,(sd.emp_name) emp_name "
            + "                  ,(sd.EMP_CODE) EMP_CODE "
            + "                  ,(sd.amount) NET_SALARY "
            + "                  ,(sd.YYYYMM) YYYYMM "
            + "                  ,(sd.pay_code) pay_code "
            + "                  ,(sd.emp_id) emp_id "
            + "                  ,(sd.PAY_CODE_DESC) PAY_CODE_DESC "
            + "                  ,(sd.REMARK) REMARK "
            + "                  ,(sd.PAY_MODE)PAY_MODE "
//          + "            FROM SALARY_DETAIL_REPORT_" + tableSuffix + " sd "
            + "            FROM SALARY_DETAIL_REPORT_TBL sd "
            + "            WHERE sd.PAY_CODE = 999 "
            + "            AND sd.EMP_ID IS NOT NULL "
//          + "            AND sd.EMP_ID NOT IN  (SELECT EMP_ID FROM SALARY_DETAIL_" + tableSuffix + ") "
            + "            AND sd.EMP_ID NOT IN  (SELECT EMP_ID FROM SALARY_DETAIL_TBL) "
            + "            ) ";

    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("id", details.getId());
    param.addValue("EMP_NAME", details.getEmpName());
    param.addValue("EMP_CODE", details.getEmpCode());
    param.addValue("NET_SALARY", details.getNetSalary());
    param.addValue("GROSS_EARNING", details.getGrossEarning());
    param.addValue("GROSS_DEDUCTION", details.getGrossDeduction());
    param.addValue("YYYYMM", details.getYyyymm());
    param.addValue("EMP_ID", details.getEmployee() != null ? details.getEmployee().getEmpId() : null);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    getNamedParameterJdbcTemplate().update(q, param);
//  details.setId(((BigDecimal) keyHolder.getKeys().get("ID")).longValue());
}

标签： javasqljdbctemplate

解决方案

主要问题是您的查询是 Not In 条件。它会降低你的表现。尝试在单独的查询中获取“SELECT EMP_ID FROM SALARY_DETAIL_TB”，并在主查询中传入 Not in 块。这将提高查询的性能。每次执行保存时，每次都会触发选择查询。

推荐阅读