jdbc 批量插入greenplum测试

一次一次的插入 十万条数据

总耗时316543毫秒

		try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(url, user, passWord);
            ps = connection.prepareStatement(insertSql);
            long oddTime = System.currentTimeMillis();
            System.out.println(oddTime);
            for (int i = 1; i <= 100000; i++) {
                ps.setInt(1, i);
                ps.executeUpdate();
            }
            long newTime = System.currentTimeMillis();
            System.out.println(newTime-oddTime);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }

加上事务，当所有执行完后再提交

这次速度快一点 258293毫秒

        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(url, user, passWord);
            ps = connection.prepareStatement(insertSql);
            long oddTime = System.currentTimeMillis();
            System.out.println(oddTime);
            connection.setAutoCommit(false);
            for (int i = 1; i <= 100000; i++) {
                ps.setInt(1, i);
                ps.executeUpdate();
            }
            long midTime = System.currentTimeMillis();
            System.out.println("执行for时间：" + (midTime - oddTime));
            connection.commit();
            long newTime = System.currentTimeMillis();
            System.out.println("插入的时间是："+(newTime-midTime));
            System.out.println(newTime - oddTime);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }

事务+加上批量

十万条数据1100毫秒

        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(url, user, passWord);
            ps = connection.prepareStatement(insertSql);
            long oddTime = System.currentTimeMillis();
            System.out.println(oddTime);
            connection.setAutoCommit(false);
            for (int i = 0; i < 100000; i++) {
                ps.setInt(1, i);
                ps.addBatch();
            }
            long midTime = System.currentTimeMillis();
            System.out.println("执行for时间：" + (midTime - oddTime));
            ps.executeBatch();
            connection.commit();
            long newTime = System.currentTimeMillis();
            System.out.println("插入的时间是：" + (newTime - midTime));
            System.out.println("总耗时：" + (newTime - oddTime));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();
            }
        }

分批次批量插入

        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection(url, user, passWord);
            ps = connection.prepareStatement(insertSql);
            long oddTime = System.currentTimeMillis();
            System.out.println(oddTime);
            connection.setAutoCommit(false);
            for (int i = 1; i <= 100000; i++) {
                ps.setInt(1, i);
                ps.addBatch();
                if (i%500==0){
                    ps.executeBatch();
                    connection.commit();
                    System.out.println("插入"+i);
                }
            }
            //如果最后无法取模尽插入，就将那部分提交
            ps.executeBatch();
            connection.commit();
            long newTime = System.currentTimeMillis();
            System.out.println("总耗时：" + (newTime - oddTime));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (connection != null) {
                connection.close();