JDBC批处理执行速度极慢 [英] JDBC Batch executing extremely slow

问题描述

任何人都可以告诉我我做错了什么我在mysql中执行350个插件并且它花了40秒。

Can anyone tell me what I'm doing wrong I'm executing 350 inserts in a mysql and it's taking like 40 secs.

这是代码

long t0 = System.currentTimeMillis();
        Connection con = connectionProvider.getConnection();
        PreparedStatement s = con.prepareStatement("insert into domkee.friends(idFriends,friend1Id,friend2Id,friend2Name) values(?,?,?,?)");
        con.setAutoCommit(false);
        for (Friend f : friends) {
            s.setLong(1, 0);
            s.setLong(2, f.getFriend1Id());
            s.setLong(3, f.getFriend2Id());
            s.setString(4, f.getFriend2Name());
            s.addBatch();

        }
        long t1 = System.currentTimeMillis() - t0;
        s.executeBatch();
        long t2 = System.currentTimeMillis()-t0;
        con.commit();
        long t3 = System.currentTimeMillis()-t0;
        s.close();
        con.close();
        long t4 = System.currentTimeMillis()-t0;
        System.out.println(((double)t1/1000) + ";" + ((double)t2/1000) + ";" + ((double)t3/1000) + ";" + ((double)t4/1000));

这是控制台：

0.156;39.251;39.376;39.486

所以 .executeBatch（）花了40秒，可能是什么问题？

So the .executeBatch() is taking like 40 secs, what could be the problem?

推荐答案

将？rewriteBatchedStatements = true 添加到JDBC网址的末尾。它会给你带来严重的性能提升。请注意，这是特定于MySql的，不会对任何其他JDBC驱动程序产生任何影响。

Add ?rewriteBatchedStatements=true to the end of your JDBC url. It'll give you a serious performance improvement. Note that this is specific to MySql, won't have any effect with any other JDBC drivers.

这篇关于JDBC批处理执行速度极慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！