时间戳

首页 > 解决方案 > 使用 Oracle SQL 存储过程和 Java Spring 通过多行插入和提取提高性能

java - 使用 Oracle SQL 存储过程和 Java Spring 通过多行插入和提取提高性能

问题描述

我目前有 Oracle SQL 版本 18c 的存储过程，用于从我的 Java Spring Boot 应用程序调用的一个父表和一个子表中插入和获取多行数据。一切正常，但速度非常慢，只有几行数据。

仅在两者之间插入 70 条记录时，最多需要 267 秒才能插入空表。取回相同的数据大约需要 40 秒。

任何帮助将不胜感激，或者如果我需要任何其他信息。

下面是我的父表和子表的存储过程的缩减和重命名版本，实际父表有 32 列，子表有 11 列。

PROCEDURE processParentData(
      i_field_one varchar2,
      v_parent_id OUT number) is
      v_new PARENT%ROWTYPE;
    BEGIN
      
    v_new.id := ROW_SEQUENCE.nextval;                                           
    v_new.insert_time := systimestamp;
    v_new.field_one := i_field_one;

    insert into PARENT values v_new;

    v_parent_id := v_new.id;

    END;
    
    PROCEDURE readParentData(
      i_field_one IN varchar2,
      v_parent OUT SYS_REFCURSOR) AS
    BEGIN
      OPEN v_parent FOR select h.* from PARENT h
      where h.field_one = i_field_one;
    END;
    
    PROCEDURE processChild(
      i_field_one varchar2,
      i_parent_id number) is
      v_new CHILD%ROWTYPE;
    BEGIN
      
    v_new.id := ROW_SEQUENCE.nextval;                                           
    v_new.insert_time := systimestamp;

    v_new.field_one := i_field_one;
    v_new.parent_id := i_parent_id;

    insert into CHILD values v_new;

    END;
    
    PROCEDURE readChild(
      i_parent_id IN number,
      v_child OUT SYS_REFCURSOR)  AS
    BEGIN
      OPEN v_child FOR select h.* from CHILD h
      where h.parent_id = i_parent_id;
    END;

对于我的 Java 代码，我使用的是 Spring JDBC。获得父数据后，我通过循环遍历父数据并使用每个子数据的父 ID 调用 readChild 来获取每个子数据。

var simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withCatalogName("PARENT_PACKAGE")
    .withProcedureName("processParentData");

SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
    .addValue("i_field_one", locationId)
    .addValue("v_parent_id", null);
            
Map<String, Object> out = simpleJdbcCall.execute(sqlParameterSource);
var stopId = (BigDecimal) out.get("v_parent_id");
return stopId.longValue();

var simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withCatalogName("PARENT_PACKAGE")
    .withProcedureName("readParentData")
    .returningResultSet("v_parent", BeanPropertyRowMapper.newInstance(Parent.class));

SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
    .addValue("i_field_one", location.getId());
            
Map<String, Object> out = simpleJdbcCall.execute(sqlParameterSource);
return (List<Parent>) out.get("v_parent");

更新 1：据我所知并测试过，使用相同的数据和表，如果我使用纯 JDBC 或 JPA/Hibernate 直接插入和获取表并避免使用存储过程，那么插入和获取的整个过程只需要几秒钟。

问题是，在我工作的公司，他们制定了一项政策，即不允许所有应用程序对数据库进行直接读/写访问，并且一切都必须通过存储过程完成，他们说出于安全原因。这意味着我需要锻炼如何通过直接读/写访问来做我们多年来一直在做的事情，现在只使用 Oracle 存储过程。

更新 2：添加我当前的 Java 代码以获取子数据。

for (Parent parent : parents) {
    parent.setChilds(childRepository.readChildByParentId(parent.getId()));
}

public List<Child> readChildByParentId(long parentId) {
        var simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withCatalogName("CHILD_PACKAGE")
            .withProcedureName("readChild")
            .returningResultSet("v_child", BeanPropertyRowMapper.newInstance(Child.class));

        SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
            .addValue("i_parent_id ", parentId);
                    
        Map<String, Object> out = simpleJdbcCall.execute(sqlParameterSource);
        return (List<Child>) out.get("v_child");
}

标签： javaoraclespring-bootstored-procedures

解决方案

您的性能问题可能与对数据库执行的操作数量有关：您在 Java 中迭代您的集合，并在每次迭代中与数据库交互。您需要尽量减少执行的操作次数。

一种可能的解决方案是使用标准类型STRUCT和ARRAYOracle 类型。请考虑以下示例：

public static void insertData() throws SQLException {
  DriverManagerDataSource dataSource = ...
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  jdbcTemplate.setResultsMapCaseInsensitive(true);
  SimpleJdbcCall insertDataCall = new SimpleJdbcCall(jdbcTemplate)
      .withCatalogName("parent_child_pkg")
      .withProcedureName("insert_data")
      .withoutProcedureColumnMetaDataAccess()
      .useInParameterNames("p_parents")
      .declareParameters(
          new SqlParameter("p_parents", OracleTypes.ARRAY, "PARENT_ARRAY")
      );

  OracleConnection connection = null;

  try {
    connection = insertDataCall
        .getJdbcTemplate()
        .getDataSource()
        .getConnection()
        .unwrap(OracleConnection.class)
    ;

    List<Parent> parents = new ArrayList<>(100);
    Parent parent = null;
    List<Child> chilren = null;
    Child child = null;
    for (int i = 0; i < 100; i++) {
      parent = new Parent();
      parents.add(parent);
      parent.setId((long) i);
      parent.setName("parent-" + i);
      chilren = new ArrayList<>(1000);
      parent.setChildren(chilren);
      for (int j = 0; j < 1000; j++) {
        child = new Child();
        chilren.add(child);
        child.setId((long) j);
        child.setName("parent-" + j);
      }
    }

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

    StopWatch stopWatch = new StopWatch();
    stopWatch.start("insert-data");
    StructDescriptor parentTypeStructDescriptor = StructDescriptor.createDescriptor("PARENT_TYPE", connection);
    ArrayDescriptor parentArrayDescriptor = ArrayDescriptor.createDescriptor("PARENT_ARRAY", connection);
    StructDescriptor childTypeStructDescriptor = StructDescriptor.createDescriptor("CHILD_TYPE", connection);
    ArrayDescriptor childArrayDescriptor = ArrayDescriptor.createDescriptor("CHILD_ARRAY", connection);

    Object[] parentArray = new Object[parents.size()];
    int pi = 0;
    for (Parent p : parents) {
      List<Child> children = p.getChildren();
      Object[] childArray = new Object[children.size()];
      int ci = 0;
      for (Child c : children) {
        Object[] childrenObj = new Object[2];
        childrenObj[0] = c.getId();
        childrenObj[1] = c.getName();
        STRUCT childStruct = new STRUCT(childTypeStructDescriptor, connection, childrenObj);
        childArray[ci++] = childStruct;
      }

      ARRAY childrenARRAY = new ARRAY(childArrayDescriptor, connection, childArray);

      Object[] parentObj = new Object[3];
      parentObj[0] = p.getId();
      parentObj[1] = p.getName();
      parentObj[2] = childrenARRAY;
      STRUCT parentStruct = new STRUCT(parentTypeStructDescriptor, connection, parentObj);
      parentArray[pi++] = parentStruct;
    }

    ARRAY parentARRAY = new ARRAY(parentArrayDescriptor, connection, parentArray);

    Map in = Collections.singletonMap("p_parents", parentARRAY);

    insertDataCall.execute(in);
    connection.commit();

    stopWatch.stop();

    System.out.println(stopWatch.prettyPrint());
  } catch (Throwable t) {
    t.printStackTrace();
    connection.rollback();
  } finally {
    if (connection != null) {
      try {
        connection.close();
      } catch (Throwable nested) {
        nested.printStackTrace();
      }
    }
  }
}

在哪里：

CREATE OR REPLACE TYPE child_type AS OBJECT (
  id NUMBER,
  name VARCHAR2(512)
);

CREATE OR REPLACE TYPE child_array 
    AS TABLE OF child_type;

CREATE OR REPLACE TYPE parent_type AS OBJECT (
  id NUMBER,
  name VARCHAR2(512),
  children child_array
);

CREATE OR REPLACE TYPE parent_array 
    AS TABLE OF parent_type;

CREATE SEQUENCE PARENT_SEQ INCREMENT BY 1 MINVALUE 1;

CREATE SEQUENCE CHILD_SEQ INCREMENT BY 1 MINVALUE 1;

CREATE TABLE parent_table (
  id NUMBER,
  name VARCHAR2(512)
);

CREATE TABLE child_table (
  id NUMBER,
  name VARCHAR2(512),
  parent_id NUMBER
);

CREATE OR REPLACE PACKAGE parent_child_pkg AS

  PROCEDURE insert_data(p_parents PARENT_ARRAY);

END;

CREATE OR REPLACE PACKAGE BODY parent_child_pkg AS

  PROCEDURE insert_data(p_parents PARENT_ARRAY) IS
    l_parent_id NUMBER;
    l_child_id NUMBER;
  BEGIN
    FOR i IN 1..p_parents.COUNT LOOP
      SELECT parent_seq.nextval INTO l_parent_id FROM dual;
      
      INSERT INTO parent_table(id, name)
      VALUES(l_parent_id, p_parents(i).name);
      
      FOR j IN 1..p_parents(i).children.COUNT LOOP
        SELECT child_seq.nextval INTO l_child_id FROM dual;
        
        INSERT INTO child_table(id, name, parent_id)
        VALUES(l_child_id, p_parents(i).name, l_parent_id);
        
      END LOOP;
    END LOOP;
  END;

END;

并且是简单Parent的ChildPOJO：

import java.util.ArrayList;
import java.util.List;

public class Parent {
  private Long id;
  private String name;
  private List<Child> children = new ArrayList<>();

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public List<Child> getChildren() {
    return children;
  }

  public void setChildren(List<Child> children) {
    this.children = children;
  }
}

public class Child {
  private Long id;
  private String name;

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }
}

请原谅代码的易读性和不正确的错误处理，稍后我将改进答案，包括一些有关获取数据的信息。

推荐阅读