Databases
StackOverflow2013
Postsdbo
POHibernate Pagination using HQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate Pagination using HQL
    primarykey
    data
    text
    <p><strong>Hibernate Pagination Issue</strong></p> <p>I have an issue which is related to Hibernate Pagination and to some extent this has been explained in </p> <p><a href="http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/" rel="nofollow noreferrer">Mysql Pagination Optimization</a></p> <p><a href="https://stackoverflow.com/questions/2826319/using-hibernates-scrollableresults-to-slowly-read-90-million-records">Using Hibernate&#39;s ScrollableResults to slowly read 90 million records</a></p> <p><a href="https://stackoverflow.com/questions/6348143/hibernate-hql-pagination">Hibernate - HQL pagination</a></p> <p><a href="https://stackoverflow.com/questions/1030924/issues-with-pagination-and-sorting">Issues with Pagination and Sorting</a></p> <p><a href="https://stackoverflow.com/questions/489360/hql-row-identifier-for-pagination">Hibernate Row Pagination</a></p> <p><strong><em>Details</em></strong></p> <p><strong>HQL Query from Application:</strong></p> <pre><code>Query q = session.createQuery("from RequestDao r order by r.id desc"); q.setFirstResult(0); q.setMaxResults(50); </code></pre> <p>Query returns 3 million records and for pagination we are setting only 50 of those records, pagination page is very slow because on every refresh we are calling the query which get 3 millions records and out of those we only set 50 records. </p> <p>My main question is </p> <blockquote> <p>Does HQL always goes and hits database or does it go and hit session or memory to look for the data and if it goes everytime to hit database and get resultset then it is very proper from performance point of view, what would be best solutions to improve it?</p> </blockquote> <p>Using HQL in hibernate is there a way we can query database and get only 50 records out first and then get other records as required by the user. This challenge is really bogging down application and so what would be best way to solve this problem?</p> <p><strong>HQL Query generated in logs</strong></p> <pre><code>from com.delta.dao.RequestDao r order by r.id desc </code></pre> <p><strong>Hibernate Generated Query</strong></p> <pre><code>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 </code></pre> <p>Here is the <strong>Explain Plan</strong> for the query:</p> <hr> <pre> | 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 | </pre> <hr> <p><strong><em>Additional information: Query run time with and without order by clause on 50 records limit</em></strong></p> <hr> <p>If i run query <code>with order</code> clause then query takes <strong><em>0.0012s</em></strong> with setting <code>LIMIT 50</code> and <code>without order</code> clause, same query takes <strong><em>0.0032s</em></strong> with same <code>LIMIT 50</code>. </p> <hr> <p>Also how can we find if:</p> <ol> <li>Particular HQL Query is hitting database and not cache or getting information from session?</li> <li>Is it true that HQL Query will always go and hit database to get result out and Criteria would go and hit session or cache and get results from it?</li> <li><p>Also in my below mentioned query:</p> <pre><code>a) Query q = session.createQuery("from RequestDao r order by r.id desc"); b) q.setFirstResult(0); c) q.setMaxResults(50); </code></pre></li> </ol> <p>at a, is it true that we get result from database and store it in memory or where if not and at this time we have 3 million results in result set and then at b and c we set offset value and limit so on page we would only see 50 results so now where are remaining 3 million records and on our second call to this query do we again go and hit database and get 3 million records and put them in memory and then at c again we set 50 records and go on an on. </p> <p>This issue is not clear to me and so would highly appreciate if someone can provide clear and detailed explanation as how this is working and what would be best solution for this problem.</p> <p><strong>Update</strong></p> <p>As it turns out, issue am having is not related to display of records on the page but i have filter on that page and on every request am getting all drop down values again from database and there are some funky things going on in there that is causing rise in page load time. </p> <p>I am making multiple nested hibernate queries to database and getting results back, what would be an optimal solution for this problem?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USRachel
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHibernate Pagination using HQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHibernate Pagination using HQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHibernate Pagination using HQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthis question is open as i have yet not got an answer to my questions, do add answers if you have any information.
      singulars
      1. POHibernate Pagination using HQL
      1. USRachel
    2. COI just edited my answer to reflect the update.
      singulars
      1. POHibernate Pagination using HQL
      1. USOlaf
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload