Overcome java heap space error when trying to retrieve data from a large table and insert into another table?

Question

I have a table called "snomed_conceptdata" from which iam trying to retrieve a column called "id" which has around 454772 rows present in it.And iam using this 'id' to get only certain rows from another table called snomed_descriptiondata where the conceptid column value equals this "id" and then inserting those rows to a another table called snomedinfo_data.

My current code:

package Snomed.Snomed;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import catalog.Root;

public class Snomedinfo {
    public void snomedinfoinsert()
    {
    Root oRoot = null;
    ResultSet oRsSelect = null;
    PreparedStatement oPrStmt = null;
    PreparedStatement oPrStmt2 = null;
    PreparedStatement oPrStmtSelect = null;
    String strSql = null;
    String strSql2 = null;
    String snomedcode=null;
    ResultSet oRs = null;
    String refid = null;
    String id = null;
    String effectivetime = null;
    String active = null;
    String moduleid = null;
    String conceptid = null;
    String languagecode = null;
    String typeid = null;
    String term = null;
    String caseSignificanceid = null;
    int count = 0;
    final int batchSize = 1000;
    try{
    oRoot = Root.createDbConnection(null);
    strSql = "SELECT  id FROM snomed_conceptdata WHERE active=1 ";
    oPrStmt2 = oRoot.con.prepareStatement(strSql);
    oRsSelect = oPrStmt2.executeQuery();
    while (oRsSelect.next()) {
        snomedcode = Root.TrimString(oRsSelect.getString("id"));
        String sql = "INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid)SELECT refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid from snomed_descriptiondata WHERE conceptid =? AND active=1" ; 
        oPrStmtSelect = oRoot.con.prepareStatement(sql);
        oPrStmtSelect.setString(1,snomedcode);

        oPrStmtSelect.executeUpdate();

        }

    //oPrStmtSelect.executeBatch();






    System.out.println("done");
    }

    catch (Exception e) {
        e.printStackTrace();

    }

    finally {

        oRsSelect = Root.EcwCloseResultSet(oRsSelect);
        oRs = Root.EcwCloseResultSet(oRs);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt2);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmtSelect);
        oRoot = Root.closeDbConnection(null, oRoot);
    }
}
    public static void main(String args[] ) throws Exception 
      { 


      Snomedinfo a = new Snomedinfo();
      a .snomedinfoinsert();

      }


}

everything is working fine ie ,currently data(records) are getting inserted into the table 'snomedinfo_data' but after some time during insertion i suddenly get a java out of memory heap space error. please help!!

You could try to use a `insert into .. select` that way, you never even need to load the data from MySQL to your Java application just to send it back to MySQL again. — Mark Rotteveel, Aug 13 '18 at 09:22
Instead of preparing a new copy of that insert statement for every single row, try only preparing it once and reusing it for all the inserts? At the very least make sure you close it. — Shawn, Aug 13 '18 at 09:24
String sql = "INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid)SELECT refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid from snomed_descriptiondata WHERE conceptid =? AND active=1" ; — david mathew, Aug 13 '18 at 09:37
Does column `snomed_descriptiondata.conceptid` have an index? — Mick Mnemonic, Aug 13 '18 at 10:10
can this be done without using insert into .. select ?? ie i want seprate query for select and seperate for insert?? — david mathew, Aug 13 '18 at 10:52
To improve performance, you could pass a [connection string parameter](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html) (`useServerPrepStmts=false&rewriteBatchedStatements=true`) to enable re-writing batch statements and also use [JDBC batching](https://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc) with your prepared statement. — Mick Mnemonic, Aug 13 '18 at 10:58

Overcome java heap space error when trying to retrieve data from a large table and insert into another table?

0 Answers0