0

I am passing 500 records to procedure using oracle.sql.ARRAY as --.

private Map<String, Object> saveRecords(Object[][] outputTypeArray) {
    Map<String, Object> output = null;
    Object[] outputArray;
    Map<String, Object> spInputs = new HashMap<String, Object>();
    Connection connection = null;
    Connection con = null;
    ArrayDescriptor arrayDescriptor = null;

try {
        outputArray = new Object[outputTypeArray.length];
        LOGGER.info("outputTypeArray.length " + outputTypeArray.length);
        connection = this.getJdbcTemplate().getDataSource().getConnection();
        con = DBUtils.getProcedureConnection(connection);
        LOGGER.info("Connection details : " + con);
        for (int i = 0; i < outputTypeArray.length; i++) {
            outputArray[i] = new STRUCT(StructDescriptor.createDescriptor(
                    DBConstants.SP_LC_OBJ, con), con, outputTypeArray[i]);
        }
        LOGGER.info("connection out " + connection);
        arrayDescriptor = ArrayDescriptor.createDescriptor(
                DBConstants.SP_LC_TBL, con);
        LOGGER.info("output array out " + outputArray.length);
        ARRAY mDataArray = new ARRAY(arrayDescriptor, con, outputArray);//problem is here
        LOGGER.info("marray size " + mDataArray.length());
        spInputs.put(DBConstants.SP_LC_IN_PARAM, mDataArray);
        output = super.execute(spInputs);
    } catch (SQLException e) {
        LOGGER.error();
    } catch (Exception e) {
        LOGGER.error();
    } 
}



public static Connection getProcedureConnection(Connection conn) {

    // For LOCAL


      Connection oracleConnection = conn;

      if (conn instanceof org.apache.commons.dbcp.DelegatingConnection) {
      // This returns a org.apache.commons.dbcp.PoolableConnection
      Connection pc =
      ((org.apache.commons.dbcp.DelegatingConnection)conn).getDelegate();

      // The PoolableConnection is a itself - get the delegate (the Oracleconnection)
       if(null != pc){ oracleConnection =
      ((org.apache.commons.dbcp.DelegatingConnection)pc).getDelegate();
      }else { oracleConnection = conn; }

      }

      return oracleConnection;


}

all working fine but i am facing performance issue for--

ARRAY mDataArray = new ARRAY(arrayDescriptor, con, outputArray);

this particular line takes approximately 4.5 minute to execute,its seems to much of time so please suggest some solution to reduce this execution time

Thanks

5
  • How large are your entities? Are they REALLY big? Commented Mar 19, 2014 at 11:35
  • Please, could you tell us what does con = DBUtils.getProcedureConnection(connection); do?
    – Jorge_B
    Commented Mar 19, 2014 at 11:35
  • slugmandrew is right, is there any BLOB or CLOB in the StructDescriptor definition?
    – Jorge_B
    Commented Mar 19, 2014 at 11:36
  • @ slugmandrew there are just 20 columns of varchar2/double
    – Gan
    Commented Mar 20, 2014 at 2:42
  • @ Jorge_B i have provided getProcedureConnection() method definition.
    – Gan
    Commented Mar 20, 2014 at 2:49

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.