I am wondering if the Criteria.setFirstResult() and Criteria.setMaxResults() manipulate the JDBC connection through the drivers to return the results? And, if so, whether it happens on the Database side or the Application server.

This is why I ask. There are three parts to retrieving a query. The query must execute, the records must be retrieved (still on database side), and then the records need to be sent over the line and displayed for the user. Out of those three what you need to get control over for large results is the amount of records that get retrieved. The two ways you can do this is by either manipulating the query or setting something on the JDBC connection.

The way I have been returning just the rows I need is by using the sybase specific set rowcount in the query string. This works, but if I use the Criteria object then I cannot do that. It appears as though the Criteria.setFirstResult() and Criteria.setMaxResults() are in fact only returning the rows I need because the speed is the same. However, I can see in the logging that the query string is not being manipulated like I would have guessed. So that leaves it to the JDBC connection is being manipulated.

I know that these questions are implemenation specific so I am wondering how the implemenation for Sybase does it? I also know that you cannot get a specifc set of rows, but rather must start at the beginning. That is Ok because for paginating results I always start at the beginning and then give the rows I need. As long as I do not have to get all the results I do not mind having to start at the first record.

Reading POJO in Action it says that Hibernate would be manipulating the query string to get the results, but I can clearly see that is not happening in the log files. However if I run the query wide open without the Criteria.setFirstResult() and Criteria.setMaxResults() it takes 10 seconds to run the query. If I limit the results with those commands then it takes miliseconds to run so I assume it must be happening on the connection when I call the Criteria.list.

Any thoughts?

Here is a code snippet just to show what I am doing.

There is one other manipulatable (if that's a word) thing that you didn't mention: the JDBC statement. And that is what's being manipulated this time. Have a look at the javadocs for java.sql.Statement.setFetchSize and java.sql.Statement.setMaxRows. Configuring a statement doesn't change the outputted SQL, or any properties on the connection as a whole: just that one statement is modified.

For first result, which isn't configurable via normal JDBC, the implementation details are dialect specific. Sybase doesn't support LIMIT, so I'm guessing that the query is actually for the first (FIRSTROW+MAXROWS) rows, then the result set is scrolled past the first FIRSTROW rows. So the query is returning more data, but the driver never fetches it from the server (which is why it's not taking any more time).

I'm not certain that this is how it works, I'm inferring this from a reasonable about of code perusal.

Your right...I did not see that this is actually on the Statement object, not the Connection. That is a big difference.



That seems almost correct. I finally got smart and did a valid integration test with JUnit and Sybase to prove how this works. I used a table with almost 2 million records in it so I can be assured that if it was bringing back all the results I would just hopelessly run out of memory right away.

With Sybase it must be using the setMaxRows() on the Statement to return the amount of records needed to bring back the correct number of paginated results. I know this because the query string is not being manipulated and I can tell from the memory usage and speed that only a small amount of records are being returned. However as I ask for more pages of data (more records) the memory starts to increase and the speed decreases.

So if I need rows 100 - 115 then it would return 115 records from the database, but then return 15 records from the Hibernate call. So it does continue to bring back more results each time from the database and then Hibernate paginates those results.

This is actually as close to perfect as we can get with Sybase and is very cool. I just have to make sure I do not give the users any kind of last page button :) !


Thank you for your help!

Once I recognized it is not on the connection things finally clicked. Like many developers the whole database JDBC connection mechanism has always been abstracted away from me, even before Hibernate when I was using a custom inhouse Connection wrapper. Now that performance is a really big deal at my current job I am trying to learn what is happening at the lowest level so I do not take things at face value. I do wish it was documented somewhere on what is really happening under the covers for each dialect, but I do feel confident in my testing that I could infer what is happening.

I do have the source code with Hibernate and did spend some time looking through it, but it is quite a bit of code to wade through but will continue to ramp up on it as I get time and have questions.

Not quite. JDBC would get the server to create a resultset of rows 1 - 115, then Hibernate would tell JDBC to scroll the resultset to row 100, then it would ask for all remaining rows (100 - 115). So the first 100 rows are scrolled over on the DBserver side of things, not in java or anywhere where your app's memory might be impacted.

At least, that's the way I read it when I looked in the code yesterday. It's also they way I'd do it.

That would be ideal, but it is not the way it happens in my test case. If I always ask for 15 rows but keep setting the setFirstResult() higher I can clearly see the memory requirements getting higher. Once I set it to over 100,000 rows then I just run out of memory, which would not happen if it scrolled the results on the database side.

This was a test against 2 million records so it was a fair test. I did an integration test just using JUnit so that pretty much eliminates anything else that could be using memory whiile doing the tests. I also printed out the memory before and after running the query so I could see the memory being used.

The way it works is fine with me as long as I do not give a last page button as that would defeat the whole purpose...unless it really worked the way you are saying. This is why I think it would be really nice if it was documented somewhere. When I do find documentation it says the query string would be manipulated, so I wonder if that is the way it happens with an Oracle or MySql.

I do appreciate your help though!

Any database that supports the LIMIT keyword can handle paging entirely serverside. So can SQLServer through the TOP keyword, but support for offsets isn't implemented. I think it's a bit more complicated than limit (for one thing, you have to reverse the ordering in order to do it properly).

Have a look through org.hibernate.dialect for DBs that support LIMIT: they'll return true from supportsLimit() (in the dialect class). MySQL and Oracle both do, Sybase doesn't.