Statement.setFetchSize(nSize) 方法在 SQL Server JDBC 驱动程序中的真正作用是什么? [英] What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?
问题描述
我有一张非常大的表，每天有数百万条记录，每天结束时我都会提取前一天的所有记录.我这样做:
I have this really big table with some millions of records every day and in the end of every day I am extracting all the records of the previous day. I am doing this like:
String SQL = "select col1, col2, coln from mytable where timecol = yesterday";
Statement.executeQuery(SQL);
问题是这个程序需要 2GB 的内存，因为它需要内存中的所有结果然后处理它.
The problem is that this program takes like 2GB of memory because it takes all the results in memory then it processes it.
我尝试设置 Statement.setFetchSize(10) 但它从操作系统中占用完全相同的内存，它没有任何区别.为此，我正在使用 Microsoft SQL Server 2005 JDBC 驱动程序.
I tried setting the Statement.setFetchSize(10) but it takes exactly the same memory from OS it does not make any difference. I am using Microsoft SQL Server 2005 JDBC Driver for this.
有没有什么方法可以像 Oracle 数据库驱动程序那样，在执行查询时只显示几行并在向下滚动时显示更多结果时，以小块形式读取结果?
Is there any way to read the results in small chunks like the Oracle database driver does when the query is executed to show only a few rows and as you scroll down more results are shown?
推荐答案
在 JDBC 中，setFetchSize(int) 方法对于 JVM 中的性能和内存管理非常重要，因为它控制着数量从 JVM 到数据库的网络调用数量以及相应的用于 ResultSet 处理的 RAM 量.
In JDBC, the setFetchSize(int) method is very important to performance and memory-management within the JVM as it controls the number of network calls from the JVM to the database and correspondingly the amount of RAM used for ResultSet processing.
本质上，如果 setFetchSize(10) 被调用而驱动程序忽略它，那么可能只有两种选择:
Inherently if setFetchSize(10) is being called and the driver is ignoring it, there are probably only two options:
- 尝试使用不同的 JDBC 驱动程序以支持 fetch-size 提示.
- 查看 Connection 上特定于驱动程序的属性(创建 Connection 实例时的 URL 和/或属性映射).
RESULT-SET 是为响应查询而在 DB 上编组的行数.ROW-SET 是每次从 JVM 调用到 DB 时从 RESULT-SET 中取出的行块.这些调用的数量和处理所需的最终 RAM 取决于提取大小设置.
The RESULT-SET is the number of rows marshalled on the DB in response to the query. The ROW-SET is the chunk of rows that are fetched out of the RESULT-SET per call from the JVM to the DB. The number of these calls and resulting RAM required for processing is dependent on the fetch-size setting.
所以如果 RESULT-SET 有 100 行并且 fetch-size 是 10，将有 10 次网络调用来检索所有数据，在任何给定时间使用大约 10*{row-content-size} RAM.
So if the RESULT-SET has 100 rows and the fetch-size is 10, there will be 10 network calls to retrieve all of the data, using roughly 10*{row-content-size} RAM at any given time.
默认的 fetch-size 是 10，这是相当小的.在发布的案例中，驱动程序似乎忽略了 fetch-size 设置，在一次调用中检索所有数据(大 RAM 要求，最佳最小网络调用).
The default fetch-size is 10, which is rather small. In the case posted, it would appear the driver is ignoring the fetch-size setting, retrieving all data in one call (large RAM requirement, optimum minimal network calls).
ResultSet.next() 下面发生的事情是它实际上并没有一次从 RESULT-SET 中获取一行.它从(本地)ROW-SET 中获取，并在本地客户端耗尽时从服务器获取下一个 ROW-SET(不可见).
What happens underneath ResultSet.next() is that it doesn't actually fetch one row at a time from the RESULT-SET. It fetches that from the (local) ROW-SET and fetches the next ROW-SET (invisibly) from the server as it becomes exhausted on the local client.
所有这一切都取决于驱动程序，因为设置只是一个提示"，但在实践中我发现这就是它适用于许多驱动程序和数据库的方式(在许多版本的 Oracle、DB2 和 MySQL 中得到验证).
All of this depends on the driver as the setting is just a 'hint' but in practice I have found this is how it works for many drivers and databases (verified in many versions of Oracle, DB2 and MySQL).
这篇关于Statement.setFetchSize(nSize) 方法在 SQL Server JDBC 驱动程序中的真正作用是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
