Insert 500K rows as a bulk insert

Support
#1

Hi,

I need to read 500k rows from a file and insert them into a SqlServer DB.
What would be the fastest way?
I do not want the system to do a commit every single row.
Any kind of bulk insert would be great.

Regards
Roland

#2

Try this

void saveAndReturnNothing(List<Customer> entities) {
    SaveContext saveContext = new SaveContext().setDiscardSaved(true);
    for (Customer entity : entities) {
        saveContext.saving(entity);
    }
    dataManager.save(saveContext);
}
#3

Hi,
tried that and also used jdbctemplate.
Using saveContext it takes 5 min to insert 5K rows.
Using jdbcTemplate it takes 20 sec to insert 5K rows.
But, even that is slow from my view… only 250 rows a second.
I would expect many thousand a second.

I also tested multiple tips how to get jdbcTemplate running faster.
But it still looks like, that there is a commit after each row.

→ using saveContext is extremly slow
→ using jdbcTemplate faster, but still not fast enough

if anybody knows how to speed that up, would be great

#4

Did you try batch inserts with JdbcTemplate?

#5

Hi,
yes, but I needed to set autocommit=false.
Using this code, is comes down from 20 sec to 2 sec :slight_smile:
The big question: how to do it with JMIX, instead of coding a singledatasource???

    SingleConnectionDataSource ds = new SingleConnectionDataSource();
    ds.setAutoCommit(false);
    ds.setDriverClassName(driver);
    ds.setUrl(url);
    ds.setUsername(user);
    ds.setPassword(pwd);

    try {
        Connection conn = ds.getConnection();
        JdbcTemplate l0Tmpl = new JdbcTemplate(ds);
        int[] argTypes = { Types.FLOAT, Types.NVARCHAR, Types.DATE };
        l0Tmpl.batchUpdate(sql, batchArgs, argTypes);
        conn.commit();
        conn.close();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
#6

Hi Roland,

Jmix DataManager is not optimized for massive data operations, and will never be. It does too many things behind the scene like checking permissions, sending events and so on.

Use the right tool for the job.

#7

thx… we switched to other tools…
for 100K rows with JMIX in about 10 min down to 2 min with jdbctemplate
it came down to 5 sec for the import itself with another tool