Hibernate Pagination using HQL

Question

Problem with hibernation pagination

I have a problem with Hibernate Pagination, and to some extent this is explained in

Mysql pagination optimization

Using Hibernate Scrolling Results to Slowly Read 90 Million Records

Hibernate - HQL pagination

Pagination and Sorting Issues

Hibernate row pagination

More details

HQL query from application:

Query q = session.createQuery("from RequestDao r order by r.id desc"); q.setFirstResult(0); q.setMaxResults(50); 

Query returns 3 million records, and for pagination we set only 50 of these records, the pagination page is very slow, because with each update we call a query that receives 3 million records, and of them we set only 50 records.

My main question is:

Does HQL always work and get into the database, or does it go and clicks on a session or memory to search for data, and if every time it accesses the database and receives results, then this is very good from the point of view of performance, which would be better solutions to improve it?

Using HQL in sleep mode is the way we can query the database and first get only 50 records, and then get other records as required by the user. This problem really clogs the application, and so what is the best way to solve this problem?

HQL query generated in logs

 from com.delta.dao.RequestDao r order by r.id desc 

Hibernate generated request

 select getrequest0_.ID as ID24_, getrequest0_.TIME as START3_24_, getrequest0_.STAT as STATUS24_, getrequest0_.SUM as SUMMARY24_, getrequest0_.OUTNAME as OUTPUT7_24_, getrequest0_.INPNAME as INPUT8_24_, getrequest0_.REQUEST_DATE as requestT9_24_, getrequest0_.PARENT_ID as PARENT10_24_, getrequest0_.INTER_TYPE as INTERPO60_24_, getrequest0_.OPEN_INT as OPEN61_24_, getrequest0_.SOURCE_TYPE as SOURCE62_24_, getrequest0_.TARGET_TYPE as TARGET20_24_, getrequest0_.SOURCE as SOURCE14_24_, getrequest0_.COPY_DATA as COPY16_24_, getrequest0_.CURVE as GENERATE63_24_, getrequest0_.TITLE as TITLE24_, getrequest0_.TIME_ID as TIMESERIES12_24_, getrequest0_.TASK_NAME as TASK51_24_ from REQUEST getrequest0_ where getrequest0_.KIND='csv' order by getrequest0_.ID desc 

Here Explain the plan for the request:

---

  |  id |  select_type |  table |  type |  possible_keys |  key |  key_len |  ref |  rows |  filtered |  Extra | 
  |  1 |  SIMPLE |  getrequest0_ |  ref |  TR_KIND_ID |  TR_KIND_ID |  6 |  const |  1703018 |  100.00 |  Using where |

---

Additional information: query execution time with and without order conditions for 50 recording restrictions

---

If I run a query with order , then the query takes 0.0012s with the setting LIMIT 50 and without order , the same query takes 0.0032s with the same LIMIT 50 .

---

Just as we can find if:

• Does a private HQL query end up in a database rather than caching or retrieving information from a session?
• Is it true that HQL Query will always access the database to get the result, and the criteria will go and attack the session or cache and get results from it?

• Also in my following request:

   a) Query q = session.createQuery("from RequestDao r order by r.id desc"); b) q.setFirstResult(0); c) q.setMaxResults(50); 

in a, is it true that we get the result from the database and store it in memory or where, if not, and at this time we have 3 million results in the result set, and then in b and c we set the offset value and limit so on, on the page we would see only 50 results, so now, where 3 million records are left, and on the second call of this query, we again go and hit the database and get 3 million records and put them into memory, and then back to c we set 50 records and continue.

I don’t understand this problem, and I would really appreciate it if someone could give a clear and detailed explanation of how this works and what would be the best solution for this problem.

Update

As it turns out, the problem with am is not related to the display of records on the page, but I have a filter on this page, and for each request I get all the outgoing values ​​from the database again, and there are some funky things that happen in this causing an increase in time page loading.

I make several nested hibernation queries in the database and get the results back, what would be the best solution for this problem?

Answer 1

Your query tells the database to sort all records that satisfy the WHERE clause. It can potentially sort millions of records before returning you the top 50.

EDIT 1/26: Now that the specific questions have been clarified, I will try to answer more specifically.

• Each time you execute such a request, Hibernate is sent to the database. Moreover, it will clear all new / updated data in a session on disk. If this is your situation, this behavior may contribute to slowness.

• Using the Hibernate query APIs is usually pretty good in most situations and compatible with a wide range of database platforms. If you are really worried about compressing the latest performance degradation at your data access level, you can write your own custom SQL query to select the top 50 results. But once you do this, you will almost certainly lose the independence of the database. So, evaluate your costs and benefits.

The query execution time is displayed in one milliseconds. This is usually as good as with relational databases that store data on disk. Therefore, you can evaluate whether you really have performance problems.

EDIT 1/27: OK. This seems like a problem in the overall design of the page. I have been using AJAX for the last 7 years or so, so I usually do not have to wait until the filtering user interface controls are redrawn when browsing the table. I assume that switching user interface application interfaces is not an option in your case. You need to figure out how to optimize data loading for drop-down lists, etc. Does this data change often? Can you cache it somewhere in the application? If you have to load them every time, can you just remove the display lines instead of whole objects?

Answer 2

If you set the first result and the maximum number of results, Hibernate will retrieve only these records from the database. If it is slow, set the maximum results to 1 and turn on the SQL log to see which associations are also loaded.

Hibernate also supports several caches: * first level cache: will be used only during one Hibernate session, which often corresponds to one transaction * second level cache: used for session / transaction boundaries, but mainly only by find by id method * query cache: if enabled, Hibernate will first search for query results from it. However, the query should be the same in terms of HQL and parameters, so each page can be loaded from the database once and then cached. (For more information, see here: http://docs.jboss.org/hibernate/core/3.5/reference/en/html/performance.html#performance-querycache )

Please note: caching requires a lot of memory, and depending on the size of your objects caching 3 million objects, it can lead to a huge memory loss and thus increase garbage collection, which will again hit performance.