JDBC performance tuning with optimal fetch size

Tuning performance using fetch size is an old technique some of you might already be using this configuration; some may know about it but may not have implemented. Recently I have implemented in my current project, would like to share my experience. In a typical production environment database and application will 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 key factor of your application performance.

Most of the JDBC drivers’ default fetch size is 10. In normal JDBC programming if you want to retrieve 1000 rows it requires 100 network round trips between your application and database server to transfer all data. Definitely this will impact your application response time. The reason is JDBC drivers are 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 may not be good enough to hold that large amount of data hence JDBC drivers are designed to retrieve small number (10 rows) of rows at a time that way it can support any number of rows as long as you have better design to handle large row set at your application coding. If you configure fetch size as 100, number of network trips to database will become 10. This will dramatically improve performance of your application.

MySQL

When I was working on MySQL I felt its performance is always better than SQL Server and Oracle. The reason is by default, ResultSets are 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. So you tend to find Oracle retrieval performance is slower than other server. You can observe executing a query in SQL Plus is faster than JDBC. You can change 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 you specify cursor type in the JDBC driver. Like MySQL you 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 this database as I don’t have this database server. You can change 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 be 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, you might encounter out of memory issue. For example a less number of column tables might support large rows fetch size than more number of columns.
  4. You can set fetch size based certain datatype like blob, image, etc. We follow certain naming convention for columns for example all image and blob column will have suffix “Blob”. I set high fetch size if the query doesn’t contain “Blob” word otherwise set low fetch size.

Sample Code:

Connection connection = DriverManager.getConnection("");
Statement statement = connection.createStatement();
statement.setFetchSize(1000); // configure the fetch size
ResultSet resultSet = statement.executeQuery("");

About these ads

26 Responses to JDBC performance tuning with optimal fetch size

  1. Sreeram says:

    Venkat,
    I regularly follow your posts and they really very informative.
    Thanks a lot for taking time for other to enlighten.

    Sreeram

  2. Muthu Velappan says:

    Good One Venkat, why can’t we then take this as a practice to set fetch size every time we execute a query.. I don’t think many are not doing that..

    Will there be any performance issues in setting the fetch size for all JDBC fetch Calls…

  3. srinadh says:

    How to set value for fetchsize if we are using oracle procedures and calling the same using spring jdbc??????

    Am already following the same as you mentioned above for queries, But never knew importance of the same.

    Thanks for taking time.

  4. Venkat says:

    If you set defaultRowPrefetch as part of your connection properties then it applies to all calls to oracle database fetches.

  5. Venkat (Mohan's friend) says:

    Venkat,

    I have been intending to call you. I will try this Sunday.

    Can you also post your experiences with loading large data volumes using Hibernate / JPA and the related performance tuning aspects that need to be aware of?

    Thanks,
    Venkat

  6. Venkat says:

    We spoke about this problem already still I want add the same into this comments for other viewers.

    Your question is: can I use Hibernate/JPA for batch data processing? Quick answer is “No”. The reason are:
    a) you don’t require object oriented design.
    b) you don’t require database agnostic solution.
    c) you don’t require any caching feature.

    My suggestion is direct JDBC programming will help lot in achieving better performance on batch processing.

  7. Fawad says:

    Greatly explained. I am using postgreSQL. So what is the default fetch size for that.

    Regards,
    S. Fawad Ali Shah

  8. Great Post.

    Couple of Questions.

    1. How to figure out Optimal Fetch Size for the select query
    2. Understanding the above concept, one becomes aware that setting a lower fetch size, would reduce performance. Say a query which returns 1000 records, fetch size of 5 would reduce performance as opposed to a default 10.

    But would fetch size of 2000 (larger), also hamper performance. I am asking this as the link you have given for Oracle DB mentions, “incorrectly setting a fetch size too large or too small can decrease performance”

    This question is very relevant to me, as i am setting the fetch size at only one place for an entire DAO, and using the same for all select queries/methods in my DAO.
    I am setting the fetch size at DAO class level based on the query/method which returns largest number of rows.

    A quick response would be really appreciated.

    Regards,
    Darshan

  9. Venkat says:

    Finding optimal size can be done through a series of load testing with different parameters.

  10. Sridhar says:

    setfetchSize() API can be applied at Connection, Statement and ResultSet level. Please note that it is only a hint to Oracle and not guaranteed that DB may really at run time set the practical fetch size. At Oracle DB level there is Prefetch size and Prefetch at Db level and fetchsize at JDBC level are mutually exclusve. That means they can not be used simultaneously and only any one should be used.

  11. Surjit says:

    0
    down vote favorite I have a oracle(10.2) PLSQL procedure which fetches 15 records from a table in a sysrefcursor. I then pass this cursor to a java class as a resultset. This java class is loaded to oracle.

    Driver name : Oracle JDBC driver Driver Version : 10.2.0.1.0 Driver Major Version : 10 Driver Minor Version : 2

    Observations:

    1 Inside the java class, when I iterate through the resultset I get only the first 10 records.

    2 If the cursor fetched (20 or more records) or (10 or less) I could get all the records while iterating the resultset.

    3 I found that the default fetchsize for the resultset is 10. If I change the fetchSize to 5, and the cursor fetches 8 records, I could get the first 5 records while iterating the resultset.

    4 If the cursor fetched (10 or more records) or (5 or less) I could get all the records while iterating the resultset.

    5 If I change the resultset fetchSize to 1, I could get all the records in the resultset no matter how many records are fetched by the cursor.

    Why is the resultset behaving weirdly?

  12. Pallavi says:

    Hi Venkat,

    Thanks for such an informative post.

    I have a query can you suggest a good JDBC Sniffer or monitoring tool for setFetchSize method?

    The problem is when we are trying to set the fetch size of our ResultSet as 50 or more we are getting Code 17026 SQL State null Message Numeric Overflow exception. we are suspecting that our component is unable to hold this much data but unable to conclude anything on that.

    Appreciating your help in advance.

    Regards
    Pallavi

  13. Anonymous says:

    To change defaultRowPrefetch in Websphere for oracle jdbc driver you have to create a custom properties with values

    “connectionProperties” “defaultRowPrefetch=100″

    Joan Carles

  14. thuandh says:

    i use a mysql server. how use limit with fetch size for optimize server. have formula for peformance system by resource of server(RAM, core cpu…)?

  15. Pingback: Setting Oracle size of row fetches higher makes my app slower?(Resolved) - Tech Forum Network

  16. Swathi says:

    Hi This is swathi,am using mysql Db.
    my doubt is :::
    executing/inserting data by means of batch with 1000 statements is good or splitting it to 500 and executing 500 and 500 under batch is good—which performance is good out of these two…..
    Reply to this mail ASAP to swathi.hsk@gmail.com

  17. srikanth says:

    Hi,
    I am Retrieving 48lakh data from the database to write in to csv file but i am getting java heap space error,can any one help me out this issue

  18. hari krishna says:

    Hi All,

    I am trying to read the one billion records of data from database uings sample java code like

    Connection conn = DriverManager.getConnection(url,username,password);
    Statement stmt = conn.createStatement();
    String query = “select * from table_name”;
    ResultSet rs = stmt.executeQuery(query);
    ArrayList arl = new ArrayList();
    while(rs.next())
    {
    arl.add(rs.getString(1));
    }
    rs.close();
    stmt.close();
    conn.close();

    when i execute that code it showing heep memory full exception and out of memory error.

    Any one can suggest me what is the correct way to read all the data and put it into collection object

    thanks& regards
    hari krishna G

  19. Jon Roberts says:

    For MS SQL Server, set responseBuffering=adaptive which makes it significantly faster.

  20. Pingback: jdbc statement fetch size in DB2 | Jisku.com - Developers Network

  21. Pingback: Pagination on DB rows « Kleine Blase

  22. Pingback: Pagination on DB results « Kleine Blase

  23. siva says:

    I am Retrieving 25lakh data from the database to write in to csv file but i am getting java heap space error..can anyone give me the solution??

  24. siva says:

    I am Retrieving 25lakh data from the database to write in to csv file but i am getting java heap space error..can anyone give me the solution??
    I am using spring jdbc template

  25. siva says:

    could someone help me on this issue?

  26. Anonymous says:

    Hi,

    I am fetching some huge data from database.
    The fetchSize we set is 50.
    Now while load testing we are getting out of memory exception for this query only.
    After analysing I found that now we are fetching only 10 records at a time however in future it can be more than 10 lakes.
    As current fetch size is less than the number of record selected,is there any link with OOM error or is it hamper our performance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: