Java JDBC 忽略 setFetchSize? [英] Java JDBC ignores setFetchSize?

问题描述

我正在使用以下代码

 st = connection.createStatement(
            ResultSet.CONCUR_READ_ONLY,
            ResultSet.FETCH_FORWARD,
            ResultSet.TYPE_FORWARD_ONLY
             );
 st.setFetchSize(1000);
 System.out.println("start query ");
 rs = st.executeQuery(queryString);
 System.out.println("done query");

查询返回大量 (800k) 行，并且在打印开始查询"和完成查询"之间需要很长时间 (~2m).当我在查询中手动设置限制 10000"时，开始"和完成"之间没有时间.处理结果需要时间，所以我想如果它只是从数据库中获取 1k 行，处理这些行，并且当它用完行时，它可以在后台获取新行，那么它总体上会更快.

The query return a lot of (800k) rows and it take a large time (~2m) between printing "start query" and "done query". When I manually put an "limit 10000" in my query there's no time between "start" and "done". Processing the results takes time so I guess it's overall faster if it just fetches 1k rows from the database, processes those and when it's running out of rows it can get new ones in the background.

我最后猜测的 ResultsSet.CONCUR_READ_ONLY 等；我错过了什么吗?

The ResultsSet.CONCUR_READ_ONLY etc where my last guess; am I missing something?

(这是一个 postgresql 8.3 服务器)

(it's a postgresql 8.3 server)

推荐答案

尝试关闭自动提交:

// make sure autocommit is off
connection.setAutoCommit(false);

 st = connection.createStatement();
 st.setFetchSize(1000);
 System.out.println("start query ");
 rs = st.executeQuery(queryString);
 System.out.println("done query");

参考

这篇关于Java JDBC 忽略 setFetchSize?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！