Java Statement.setFetchSize(nSize) 方法在 SQL Server JDBC 驱动程序中的真正作用是什么？

What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?

提问by

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);

The problem is that this program takes like 2GB of memory because it takes all the results in memory then it processes it.

问题是这个程序需要 2GB 的内存，因为它需要内存中的所有结果然后处理它。

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 Driverfor this.

我尝试设置，Statement.setFetchSize(10)但它从操作系统中占用了完全相同的内存，它没有任何区别。我为此使用Microsoft SQL Server 2005 JDBC 驱动程序。

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?

当执行查询以仅显示几行并且向下滚动时会显示更多结果时，是否有任何方法可以像 Oracle 数据库驱动程序那样以小块读取结果？

回答by adatapost

Statement interface Doc

报表接口Doc

SUMMARY: void setFetchSize(int rows)Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.

总结：void setFetchSize(int rows)当需要更多行时，向 JDBC 驱动程序提供有关应从数据库中提取的行数的提示。

Read this ebook J2EE and beyond By Art Taylor

阅读这本电子书J2EE 及更高版本 作者：Art Taylor

回答by Brian Agnew

It sounds to me that you reallywant to limit the rows being returned in your query and page through the results. If so, you can do something like:

在我看来，您确实想限制查询和翻页结果中返回的行。如果是这样，您可以执行以下操作：

select * from (select rownum myrow, a.* from TEST1 a )
where myrow between 5 and 10 ;

You just have to determine your boundaries.

你只需要确定你的界限。

回答by jwaddell

You need to ensure that auto-commit on the Connection is turned off, or setFetchSize will have no effect.

您需要确保连接上的自动提交已关闭，否则 setFetchSize 将不起作用。

dbConnection.setAutoCommit(false);

Edit: Remembered that when I used this fix it was Postgres-specific, but hopefully it will still work for SQL Server.

编辑：请记住，当我使用此修复程序时，它是特定于 Postgres 的，但希望它仍然适用于 SQL Server。

回答by Ron

Sounds like mssql jdbc is buffering the entire resultset for you. You can add a connect string parameter saying selectMode=cursor or responseBuffering=adaptive. If you are on version 2.0+ of the 2005 mssql jdbc driver then response buffering should default to adaptive.

听起来 mssql jdbc 正在为您缓冲整个结果集。您可以添加一个连接字符串参数，说明 selectMode=cursor 或 responseBuffering=adaptive。如果您使用的是 2005 mssql jdbc 驱动程序的 2.0+ 版本，则响应缓冲应默认为自适应。

http://msdn.microsoft.com/en-us/library/bb879937.aspx

http://msdn.microsoft.com/en-us/library/bb879937.aspx

回答by skaffman

The fetchSizeparameter is a hintto the JDBC driver as to many rows to fetch in one go from the database. But the driver is free to ignore this and do what it sees fit. Some drivers, like the Oracle one, fetch rows in chunks, so you can read very large result sets without needing lots of memory. Other drivers just read in the whole result set in one go, and I'm guessing that's what your driver is doing.

该fetchSize参数是对 JDBC 驱动程序的一个提示，即一次从数据库中获取多行。但是司机可以随意忽略这一点并做它认为合适的事情。一些驱动程序，如 Oracle 驱动程序，以块的形式获取行，因此您可以读取非常大的结果集而无需大量内存。其他驱动程序只是一口气读入整个结果集，我猜这就是您的驱动程序正在做的事情。

You can try upgrading your driver to the SQL Server 2008 version (which might be better), or the open-source jTDS driver.

您可以尝试将驱动程序升级到 SQL Server 2008 版本（可能更好）或开源 jTDS 驱动程序。

回答by Darrell Teague

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.

在 JDBC 中，该setFetchSize(int)方法对于 JVM 中的性能和内存管理非常重要，因为它控制从 JVM 到数据库的网络调用数量以及相应的用于 ResultSet 处理的 RAM 量。

Inherently if setFetchSize(10) is being called and the driver is ignoring it, there are probably only two options:

本质上，如果 setFetchSize(10) 被调用并且驱动程序忽略它，则可能只有两个选项：

1. Try a different JDBC driver that will honor the fetch-size hint.
2. Look at driver-specific properties on the Connection (URL and/or property map when creating the Connection instance).

1. 尝试使用不同的 JDBC 驱动程序，以支持 fetch-size 提示。
2. 查看连接上特定于驱动程序的属性（创建连接实例时的 URL 和/或属性映射）。

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

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.

因此，如果 RESULT-SET 有 100 行且 fetch-size 为 10，则在任何给定时间将有 10 次网络调用来检索所有数据，使用大约 10*{row-content-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).

默认的 fetch-size 是 10，这是相当小的。在发布的案例中，驱动程序似乎忽略了 fetch-size 设置，在一次调用中检索所有数据（大 RAM 要求，最佳最小网络调用）。

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.

下面发生的事情ResultSet.next()是它实际上并没有一次从 RESULT-SET 中获取一行。它从（本地）ROW-SET 获取，并在本地客户端耗尽时从服务器获取下一个 ROW-SET（不可见）。

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).

所有这一切都取决于驱动程序，因为设置只是一个“提示”，但在实践中我发现这就是它对许多驱动程序和数据库的工作方式（在许多版本的 Oracle、DB2 和 MySQL 中得到验证）。

回答by Sergio Sierra

Try this:

尝试这个：

String SQL = "select col1, col2, coln from mytable where timecol = yesterday";

connection.setAutoCommit(false);
PreparedStatement stmt = connection.prepareStatement(SQL, SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(2000);

stmt.set....

stmt.execute();
ResultSet rset = stmt.getResultSet();

while (rset.next()) {
    // ......

回答by ChaudPain

I had the exact same problem in a project. The issue is that even though the fetch size might be small enough, the JDBCTemplate reads all the result of your query and maps it out in a huge list which might blow your memory. I ended up extending NamedParameterJdbcTemplate to create a function which returns a Stream of Object. That Stream is based on the ResultSet normally returned by JDBC but will pull data from the ResultSet only as the Stream requires it. This will work if you don't keep a reference of all the Object this Stream spits. I did inspire myself a lot on the implementation of org.springframework.jdbc.core.JdbcTemplate#execute(org.springframework.jdbc.core.ConnectionCallback). The only real difference has to do with what to do with the ResultSet. I ended up writing this function to wrap up the ResultSet:

我在一个项目中遇到了完全相同的问题。问题是，即使提取大小可能足够小，JDBCTemplate 也会读取您查询的所有结果并将其映射到一个可能会占用您内存的巨大列表中。我最终扩展了 NamedParameterJdbcTemplate 来创建一个返回对象流的函数。该 Stream 基于通常由 JDBC 返回的 ResultSet，但仅在 Stream 需要时才从 ResultSet 中提取数据。如果您不保留此 Stream 吐出的所有对象的引用，这将起作用。我确实在 org.springframework.jdbc.core.JdbcTemplate#execute(org.springframework.jdbc.core.ConnectionCallback) 的实现上给了自己很多启发。唯一真正的区别在于如何处理 ResultSet。我最终编写了这个函数来包装 ResultSet：

private <T> Stream<T> wrapIntoStream(ResultSet rs, RowMapper<T> mapper) {
    CustomSpliterator<T> spliterator = new CustomSpliterator<T>(rs, mapper, Long.MAX_VALUE, NON-NULL | IMMUTABLE | ORDERED);
    Stream<T> stream = StreamSupport.stream(spliterator, false);
    return stream;
}
private static class CustomSpliterator<T> extends Spliterators.AbstractSpliterator<T> {
    // won't put code for constructor or properties here
    // the idea is to pull for the ResultSet and set into the Stream
    @Override
    public boolean tryAdvance(Consumer<? super T> action) {
        try {
            // you can add some logic to close the stream/Resultset automatically
            if(rs.next()) {
                T mapped = mapper.mapRow(rs, rowNumber++);
                action.accept(mapped);
                return true;
            } else {
                return false;
            }
        } catch (SQLException) {
            // do something with this Exception
        }
    }
}

you can add some logic to make that Stream "auto closable", otherwise don't forget to close it when you are done.

您可以添加一些逻辑来使该流“自动关闭”，否则不要忘记在完成后关闭它。