让ExecuteBatch更快地执行 [英] Getting ExecuteBatch to execute faster

问题描述

我正在尝试从sybase服务器读取表，处理行，并将结果输出到另一个表。 （下面是我的代码）

I'm trying to read a table from a sybase server, process the rows, and output the results to another table. (Below is my code)

代码检索表非常快，并且处理速度相同（在30秒内得到它发送的部分）。但是当我运行执行批处理时，它会在那里停留20分钟（fyi，我有一张表，我正在测试8400行）。

The code retrieves the table pretty fast and processes equally fast (get's to the part where it sends within 30 seconds). But When I run execute batch it sits there for 20 minutes before finish (fyi, I have a table which I'm testing with 8400 rows).

还有更多有效的方法吗？我很乐意接受或发送查询（我可以创建一个新表，更新表等） - 我只是不知道为什么这么慢（我确定数据< 1 MB，我确信它不需要SQL服务器20分钟来解析8400行）。任何想法？

Is there a more efficient way to do this? I'm amenable as to how I can recieve or send the queries (I can create a new table, update a table, etc) -- I just don't know why this is so slow (I'm sure the data < 1 MB and I'm sure it doesn't take the SQL server 20 minutes to parse 8400 rows). Any ideas?

注意：这对我来说真的很糟糕的原因是我必须解析一个1.2 MM行的表（这个表我现在正在使用是一个8400行的测试表）

Note: The reason this is really bad for me is that I have to parse a table with 1.2 MM rows (this table I'm working with right now is a test table with 8400 rows)

    Connection conn = DriverManager.getConnection(conString, user, pass);


    String sql = "SELECT id,dateid,attr from user.fromtable";
    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery(sql);

    String sqlOut = "INSERT INTO user.mytabletest (id,attr,date,estEndtime) values (?,?,?,?)";
    PreparedStatement ps = conn.prepareStatement(sqlOut);

    int i=1;

    while(rs.next())
    {
        int date = rs.getInt("dateid");
        String attr = rs.getString("attr");
        String id = rs.getString("id");

        Time tt = getTime(date,attr);
        Timestamp ts = new Timestamp(tt.getTime());

        ps.setString(1, id);
        ps.setString(2, attr);
        ps.setInt(3, date);
        ps.setTimestamp(4, ts);
        ps.addBatch();

        if(i % 10000 == 0)
        {
            System.out.println(i);
            ps.executeBatch();
            conn.commit();
            ps.clearBatch();                
        }

        i++;
    }
    System.out.println("sending "+(new Date()));
    int[] results = ps.executeBatch();
    System.out.println("committing "+(new Date()));
    conn.commit();
    System.out.println("done "+(new Date()));

推荐答案

要有效地使用批次，您应该关闭AutoCommit选项并在执行批处理后打开它（或者使用connection.commit（）方法）

To work with batches effectively you should turn AutoCommit option off and turn it on after executing the batch (or alternatively use connection.commit() method)

connection.setAutoCommit(false);
while(rs.next())
    {
     .....
     ps.addBatch();     
    }
int[] results = ps.executeBatch();
connection.setAutoCommit(true);

这篇关于让ExecuteBatch更快地执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！