CODEWITHSUNDEEP
javasqlhibernate
l0r3nz4cc10
l0r3nz4cc10

Reputation: 1283

How to insert about 500.000 data rows in table efficiently

I have about 500.000 rows of data to insert into one table.

I am currently inserting them one at a time (I know it's bad) like this :

Dao method :

public static final String SET_DATA = "insert into TABLE (D_ID, N_ID, VALUE, RUN_ID) " + "values (?, ?, ?, ?)";

public void setData(String dId, String nId, BigDecimal value, Run run) throws HibernateException {
    if (session == null) {
        session = sessionFactory.openSession();
    }

    SQLQuery select = session.createSQLQuery(SET_DATA);
    select.setString(0, dId);
    select.setString(1, nId);
    select.setBigDecimal(2, value);
    select.setLong(3, run.getRunId());

    select.executeUpdate();
}

How can I do this more efficiently ?

Upvotes: 0

Views: 820

Answers (5)

ElifÖlmez
ElifÖlmez

Reputation: 49

if record count great then 1000

             StringBuilder sb = new StringBuilder();

        foreach (var item in req)
        {
            sb.AppendLine("insert into Table_Name(column1, column1, column1 , column1) values ");
            sb.AppendFormat("({0},{1},{2},'{3}')  ;",
            item.val1, item.val2, item.val3, item.val4);
        }
        sb = sb.Remove(sb.Length - 1, 1);
        ExecuteNonQuery(sb.ToString());
        return true;

Upvotes: 0

ElifÖlmez
ElifÖlmez

Reputation: 49

1.Solution 

StringBuilder sb = new StringBuilder();
        sb.AppendLine("insert into Table_Name (column1, column1, column1 , column1 ) values ");
        foreach (var item in req)
        {
            sb.AppendFormat("({0},{1},{2},'{3}'),",
                item.val1, item.val2, item.val3, item.val4);
        }
        sb = sb.Remove(sb.Length - 1, 1);
        ExecuteNonQuery(sb.ToString());
        return true;

Upvotes: 0

Leo
Leo

Reputation: 6580

The fastest way is always using the native bulk import tool of your DBMS.

Do not use hibernate or java for that.

Dump the data into some format your DB understands (most probably in the same file system of your DB) and use your DBMS native import tool.

Upvotes: 1

Suresh Atta
Suresh Atta

Reputation: 122026

Why you went for hand written SQL query ?? If you are writing sql in such a way , you are definitely not getting the fruits of hibernate.

Learn Batch Insert Example code for Batch Insert, 

    Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

for ( int i=0; i<100000; i++ ) {
    Customer customer = new Customer(.....);
    session.save(customer);
    if ( i % 20 == 0 ) { //20, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}

tx.commit();
session.close();

Upvotes: 5

Dark Knight
Dark Knight

Reputation: 8357

Ideally you should use batch Insert. Refer to example provided here. This inserts multiple records in DB in a single go.

    dbConnection.setAutoCommit(false);//commit trasaction manually

    String insertTableSQL = "INSERT INTO DBUSER"
                + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
                + "(?,?,?,?)";              
    PreparedStatement = dbConnection.prepareStatement(insertTableSQL);

for(int i=0;i<500000;i++){ 
    preparedStatement.setInt(1, 101);
    preparedStatement.setString(2, "mkyong101");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4, getCurrentTimeStamp());
    preparedStatement.addBatch();
 }    
    preparedStatement.executeBatch();

    dbConnection.commit();

Upvotes: 0

Related Questions