Hello All,
What is the difference between query.setMaxResults and query.setFetchSize. Which one is faster and how can we use query.setFetchSize? What are the pros and cons of both.

The setMaxResults() control how many rows the query will return. Even if there are more matching rows only the maximum specified number are returned. The setMaxResults() is typically used together with setFirstResult() to implement paging. Eg. the first query returns records 1 to 1000, the second query returns 1001 to 2000, and so on.

The setFetchSize() control how many rows are fetched at a time by the JDBC driver. So, if you for example have setMaxResults(1000) and setFetchSize(100) the query will return no more than 1000 rows, and will do so in batches of 100 rows at a time. A larger fetch size will typically use more memory since the JDBC driver will have to cache more information, but may improve execution time since less time is needed for network communication. How much performance improvement you can get depends on a lot of factors, such as total number of rows, network speed, available memory, the JDBC driver implementation, etc. I guess a lot also depends on which query method you use. For example, Query.list() will load all entities into a list (and use more memory), but Query.scroll() will load data as needed. Combining scroll() with evict() will reduce the memory used for the first-level cache as well.

So, setMaxResults() and setFetchSize() are not something that you choose one or the other. They are both useful on their own or together and the "best" values may vary from situation to situation depending on the data, hardware limitations, and more.

Thanks Nordborg,
Very well explained. Is there any default value for fetchSize because at one place I was reading that setMaxResults(100) was heavy but when they placed
setMaxResults(100);
setFetchSize(100);
performance got improved. So there may be some default value for fetchSize, right? Or is it just 1.

Actually I want to have a pagination where the size on page varies according to user input. So it may be 10 records per query or 5000 records per query. And I want to improve the performance.

And btw what is evict() and how does it work with scroll? An example will help a lot.

I think the default fetch size depends on the database/jdbc driver. I have not looked into it, but I found this page: http://webmoli.com/2009/02/01/jdbc-perf ... etch-size/

I can confirm that the MySQL default is to fetch everything immediately since it caused out-of-memory problems for some of our queries returning 100000+ records.

Thanks a lot,
It helped a lot. Your help is like a treasure for me. Thanks Thanks. Actually I am doing some performance tunings of hibernate.
By the way what is evict() and how does it work with scroll? An example will help a lot.

Session.evict() can help you control memory usage. A Session keeps references to and copies of information that is loaded through it. If, for example, my 100000+ query would be loaded with Query.list() then Hibernate would cache everything inside the session. The entire result would be loaded before I got the result back. I would most certainly get out of memory before that. On the other hand, with Query.scroll(), I can move forward one record at a time. Then, at regular intervals I call Session.evict() to clear the cache inside Hibernate and release used memory. This works well assuming that it is a read-only operation. Such as listing information on a web page (or, as in our case, exporting data to a tab-separated file).

But I am actually using a StatelessSession. It doesn't have an internal cache so there is no need to clear it. I found that it was a bit quicker and it is also possible to update records. The major drawback is that it will not fetch proxies or lazy collections unless it was specified with 'fetch join' in the query, but I can live with that since I only use it in a few dedicated places.

Regarding performance tuning, we have done our fair bit of that, both with respect to execution time and memory usage. Memory usage can be affected a lot depending on things I have described here. Execution time is only marginal. But we could get a lot better execution time by optimizing settings for our MySQL server. I was not directly involved in that part but as I understood it giving MySQL more memory for some buffers, etc. made everything a lot quicker. Performance tuning is not easy. It helps to have a few (realistic) test cases that you can measure. We had a case with migration of an old database to a new server. The old was not Hibernate-based and the schemas differed a lot. If I recall correctly the first prototype needed several weeks to complete. In the end we got it down to about 50 hours.