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是n为响应查询而在数据库上编组的行数。
ROW-SET是从JVM到DB的每次调用从RESULT-SET中取出的行块。
处理所需的这些调用和产生的RAM的数量取决于fetch-size设置。
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屋！
