Hi,

I was wondering if I do somthing like:

query = query.setMaxResults(N).setFirstResults(offset);
List l = query.list();

And lets say that there are M records when M > N.

Does Hibernate loads all M records to the memory and returns only N from OFFSET or is it done at the DataBase?

Thanks

I think this is done by JDBC. And thus it depends on the implementation of the JDBC driver. There are problably no popular database systems which do this at the JDBC level.

From what you are saying I understand that it is being load to the memory but I'm looking for better performance then that. Are you confidence with your answer?

Sorry, my answer wasn't very clear. I think Hibernate doesn't handle this problem, but JDBC does. The vendor of the JDBC driver has to implement it. So it depends on the JDBC driver if it's done in the driver or at database level. It would make sense if it's done at JDBC level if the database has no support for this feature. I don't think any popular database does this at jdbc level. (Sorry it has a lot of thinks in it)

That is too bad because I know that there are databases that enable setting the cursor and selecting the size of returned resuts on the database side. So isn't there any workaround for that issue?

Sorry I still wasn't clear (?). I think it depends on the vendor of the JDBC driver. It would be stupid not using this feature when it's available. Probably ALL database which support setting the cursor at database level implement this feature in the JDBC driver @ database level.

So, if the database has the feature: it is probably done at the database and if the database doesn't have this feature it is done at jdbc level. Again, problably all popular databases and connectors have the first behaviour: i.e.: setting the cursor on the database and only loading the subset which is queried.

Amichay,
It depend from database - postgresql and mysql have limit clause and hibernate use it, but
with oracle hibernate create ugly query with rownum, sort it and return requested rows (MaxRows form fistRows).
Limit clause in postgersql and my sql exist beacuse this databases load all rows in memory and You have to make bound - Oracle load never all rows in memory (maybe table with a few rows) and for this query it is enough that read rows which you want.It is cursor
Postgresql introduce cursor in last versions, but it isn't default behavior

regards