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

by

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.

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

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

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.

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.

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

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.

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

More Related Contents:

  1. JDBC connection failed, error: TCP/IP connection to host failed
  2. Can I connect to SQL Server using Windows Authentication from Java EE webapp?
  3. java.sql.SQLException: No suitable driver found for jdbc:microsoft:sqlserver
  4. How do I connect to a SQL Server 2008 database using JDBC?
  5. SQL Server JDBC Error on Java 8: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption
  6. JPA SQL Server No Dialect mapping for JDBC type: -9
  7. Native Library sqljdbc_auth.dll already loaded in another classloader
  8. Driver.getConnection hangs using SQLServer driver and Java 1.6.0_29
  9. JDBC SQLServerException: “This driver is not configured for integrated authentication.”
  10. How to pass Table-Valued parameters from java to sql server stored procedure?
  11. Date columns in SQL-Server (MSSQL-JDBC 3.0) running under Java 1.7.0 retrieved as 2 days in the past
  12. How to get *everything* back from a stored procedure using JDBC
  13. Getting the Return Value from JDBC MSSQL
  14. Connect To SQL Server With Windows Authentication From A Linux Machine Through JDBC
  15. The TCP/IP connection to the host localhost, port 1433 has failed
  16. Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR
  17. SQL syntax error in "insert into 'keys'(a,b,c,d,e) values(?,?,?,?,?)" [closed]
  18. Hours and seconds are lost when storing a date in the database [closed]
  19. Connection pooling options with JDBC: DBCP vs C3P0 [closed]
  20. ClassNotFoundException com.mysql.jdbc.Driver [duplicate]
  21. Java: Insert multiple rows into MySQL with PreparedStatement
  22. Closing JDBC Connections in Pool
  23. How to enable logging for SQL statements when using JDBC
  24. Writing a large resultset to an Excel file using POI
  25. UNIX socket implementation for Java? [closed]
  26. Can I execute multiple queries separated by semicolon with MySQL Connector/J? [duplicate]
  27. mysql prepared statement error: MySQLSyntaxErrorException
  28. SQL Error: 0, SQLState: 08S01 Communications link failure [duplicate]
  29. How to correctly write UTF-8 strings into MySQL through JDBC interface
  30. Postgres UUID JDBC not working

Leave a Comment