SQL Fetch Size

JDBC performance tuning with optimal fetch size

February 1, Comments

Tuning performance using fetch size is a old technique some of you might already are using this configuration; Some may know is not having implemented. Recently I has implemented in my current project, would like to share my experience. In a typical production environment database and application would be running on different physical server. Even if you have high-end server class machine, the network traffic between application and database server is one of the K EY factor of your application performance.

Most of the JDBC drivers ' default fetch size is 10. In normal JDBC programming if want to retrieve the It requires network round trips between your application and database server to transfer all data. Definitely this would impact your application response time. The reason is JDBC drivers was designed to fetch small number of rows from database to avoid any out of memory issues. For example if your query retrieves 1 million rows, the JVM heap memory is not being good enough to hold that large amount O F Data Hence JDBC drivers is designed to retrieve small number (in rows) of rows at a time that's the it can support any Nu Mber of rows as long as you had better design to handle large the row set at your application coding. IF you configure fetch size as, number of the network trips to database would become 10. This would dramatically improve performance of your application.

Mysql

When I am working on MySQL I felt its performance are always better than SQL Server and Oracle. The reason is by default, ResultSets was completely retrieved from database server.

Reference:http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html

Oracle

The default fetch size is 10. tend to find Oracle retrieval performance are slower than other server. You can observe executing a query in the SQL Plus is faster than JDBC. You can change the default fetch size by setting connection property "Defaultrowprefetch".

Reference:http://download-uk.oracle.com/docs/cd/b25221_04/web.1013/b13593/optimiz011.htm#beebhbbg

SQL Server

By default it retrieves all the rows from database unless to specify cursor type in the JDBC driver. Like MySQL-can observe SQL Server JDBC performance is better than Oracle.

reference:http://technet.microsoft.com/en-us/library/aa342344 (sql.90). aspx

DB2

Seems the default fetch size is 32. Don ' t know much about the this database as I has this database server. You can change the default fetch size by through Connection property "block Size".

Important note to consider when tuning fetch size:

1. Make sure your JDBC driver supports configuring fetch size.
2. The fetch size should is based on your JVM heap memory setting. Since JVM Heap size varies between different environment, don ' t hard-code fetch size Keep it as configurable parameters.
3. If your fetch size is large, your might encounter out of memory issue. For example a less number of the column tables might support large the rows fetch size than more number of columns.
4. You can set the fetch size based certain datatype like blob, image, etc. We follow certain naming convention for columns for example all image and blob column would have suffix "blob". I set high fetch size if the query doesn ' t contain "Blob" of word otherwise set low fetch size.

Sample Code:

Connection Connection = Drivermanager.getconnection (""); Statement Statement = Connection.createstatement (); statement.setfetchsize (1000); Configure the fetch Sizeresultset ResultSet = Statement.executequery ("");

http://webmoli.com/2009/02/01/jdbc-performance-tuning-with-optimal-fetch-size/

SQL Fetch Size