postgresql Java JDBC 忽略 setFetchSize？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1468036/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java JDBC ignores setFetchSize?
提问by kresjer
I'm using the following code
我正在使用以下代码
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");
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.
查询返回大量 (800k) 行，并且在打印“开始查询”和“完成查询”之间需要很长时间（~2m）。当我在查询中手动设置“限制 10000”时，“开始”和“完成”之间没有时间。处理结果需要时间，所以我想如果它只从数据库中获取 1k 行，处理这些行，并且当它用完行时，它可以在后台获取新行，那么它总体上会更快。
The ResultsSet.CONCUR_READ_ONLY etc where my last guess; am I missing something?
ResultsSet.CONCUR_READ_ONLY 等我最后的猜测；我错过了什么吗？
(it's a postgresql 8.3 server)
（这是一个 postgresql 8.3 服务器）
回答by dogbane
回答by Wishper
I noticed that your use of the API is different from what expressed by Javadoc:
我注意到您对 API 的使用与 Javadoc 表达的不同：
Try passing parameters in this order
尝试按此顺序传递参数
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.FETCH_FORWARD
回答by Jon Skeet
This will depend on your driver. From the docs:
这将取决于您的驱动程序。从文档：
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. The number of rows specified affects only result sets created using this statement. If the value specified is zero, then the hint is ignored. The default value is zero.
向 JDBC 驱动程序提供有关在需要更多行时应从数据库中提取的行数的提示。指定的行数仅影响使用此语句创建的结果集。如果指定的值为零，则忽略提示。默认值为零。
Note that it says "a hint" - I would take that to mean that a driver can ignore the hint if it really wants to... and it sounds like that's what's happening.
请注意，它说的是“提示”——我认为这意味着如果司机真的想要的话，它可以忽略这个提示……听起来这就是正在发生的事情。
回答by Henning
The two queries do entirely different things.
这两个查询做完全不同的事情。
Using the LIMITclause limits the size of the result set to 10000, while setting the fetch size does not, it instead gives a hint to the driver saying how many rows to fetch at a time when iterating through the result set- which includes all 800k rows.
使用该LIMIT子句将结果集的大小限制为 10000，而设置提取大小则不会，而是向驱动程序提供一个提示，说明在迭代结果集时一次要提取多少行- 其中包括所有 800k 行.
So when using setFetchSize, the database creates the full result set, that's why it's taking so long.
所以在使用时setFetchSize，数据库会创建完整的结果集，这就是为什么它需要这么长时间。
Edit for clarity:Setting the fetch size does nothing unless you iterate through the result (see Jon's comment), but creating a much smaller result set via LIMIT makes a great difference.
为清楚起见进行编辑：除非您遍历结果（请参阅 Jon 的评论），否则设置提取大小没有任何作用，但通过 LIMIT 创建一个更小的结果集会产生很大的不同。
