使用HQL进行Hibernate分页 [英] Hibernate Pagination using HQL
问题描述
我有一个与Hibernate分页有关的问题，并且在一定程度上已经在
D etails
来自应用程序的HQL查询：
Query q = session.createQuery（from RequestDao r order by r.id desc）;
q.setFirstResult（0）;
q.setMaxResults（50）;
查询返回300万条记录，对于分页我们只设置其中的50条记录，分页页面非常因为在每次刷新时我们都会调用查询获得3百万条记录，而在那些我们只设置了50条记录的查询中。
我的主要问题是：
HQL是否总是出现并碰到数据库或者它会去找会话或内存去寻找数据，如果每次都去找数据库并得到结果集，那么从性能的角度来看它是非常合适的，那么改进它的最佳解决方案是什么？
在Hibernate中使用HQL有一种方法，我们可以查询数据库并只获取50条记录，然后根据用户的要求获取其他记录。这个挑战让应用程序陷入困境，那么解决这个问题最好的办法是什么？
日志中生成的HQL查询
from com.delta.dao.RequestDao r order by r.id desc
Hibernate生成的查询
select
getrequest0_.ID作为ID24_，
getrequest0_.TIME作为START3_24_，
getrequest0_.STAT作为STATUS24_，
getrequest0_.SUM作为SUMMARY24_，
getrequest0_.OUTNAME作为OUTPUT7_24_，
getrequest0_.INPNAME作为INPUT8_24_，
getrequest0_.REQUEST_DATE作为请求T9_24_，
getrequest0_.PARENT_ID作为PARENT10_24_，
getrequest0_.INTER_TYPE作为INTERPO60_24_，
getrequest0_.OPEN_INT作为OPEN61_24_，
getrequest0_.SOURCE_TYPE作为SOURCE62_24_，
getrequest0_.TARGET_TYPE作为TARGET20_24_，
getrequest0_.SOURCE作为SOURCE14_24_，
getrequest0_.COPY_DATA作为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_
其中
getrequest0_.KIND ='csv'
order by
getrequest0_.ID desc
这是查询的解释计划：
| id | select_type |表| |键入| possible_keys |键| key_len | ref |行|过滤|额外|
| 1 | SIMPLE | getrequest0_ | ref | TR_KIND_ID | TR_KIND_ID | 6 | const | 1703018 | 100.00 |使用where |
附加信息：查询运行时间50条记录限制条件限制
如果我运行查询 另外我们如何找到： 在我下面提到的查询中： at a，是的，我们得到从数据库中获得结果并将其存储在内存中，或者如果不存在，并且此时我们在结果集中有300万个结果，然后在b和c处我们设置偏移值并在页面上设置限制，所以现在只能看到50个结果。 300万条记录和我们对这个查询的第二次调用，我们会再次访问数据库并获取300万条记录，并将它们存储在内存中，然后在c处再次设置50条记录并继续。 这个问题对我来说并不清楚，所以如果有人能够提供清晰详细的解释，说明这个问题是如何运作的，那么这个问题最好的解决方案是什么。 更新 事实证明，问题与我无关显示页面上的记录，但是我在该页面上进行筛选，并且每次请求都会从数据库中再次获取所有下拉值，并且会导致页面加载时间上升。 我正在对数据库进行多重嵌套的hibernate查询并取回结果，对于这个问题，最佳解决方案是什么？ 您的查询告诉数据库对满足WHERE子句的所有记录进行排序。它可能可能会排序数百万条记录，然后再返回前50名。 编辑1/26：现在具体问题已澄清，我会尝试做出更具体的回应。 每次执行这样的查询时，Hibernate都会进入数据库。更重要的是，它会将会话中的所有新/更新数据刷新到磁盘。如果这是你的情况，这种行为可能会导致缓慢。 order 子句，然后查询将 0.0012s 设置为 LIMIT 50 和<$ c $在没有订单子句的情况下，相同的查询需要 0.0032s 以及相同的 LIMIT 50 。
a）查询q = session.createQuery（from RequestDao r order by r.id desc）;
b）q.setFirstResult（0）;
c）q.setMaxResults（50）;
您的查询运行时间似乎在单毫秒范围内。这通常与在磁盘上存储数据的关系数据库一样好。所以你可能想评估你是否确实有性能问题。
编辑1/27：好的。它看起来像是整个页面设计中的问题。过去7年来我一直在使用AJAX，所以我通常不必等待过滤UI控件在浏览表格页面时重新绘制。我想，切换应用程序UI框架不是你的选择。你必须弄清楚如何优化下拉菜单等数据的加载。这些数据经常变化吗？你可以将它缓存在应用程序的某个地方吗？如果您每次都必须加载它们，您是否可以摆脱显示字符串而不是整个对象？
Hibernate Pagination Issue
I have an issue which is related to Hibernate Pagination and to some extent this has been explained in
Using Hibernate's ScrollableResults to slowly read 90 million records
Issues with Pagination and Sorting
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 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.
My main question is
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?
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?
HQL Query generated in logs
from com.delta.dao.RequestDao r order by r.id desc
Hibernate Generated Query
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 is the Explain Plan for the query:
| 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 run time with and without order by clause on 50 records limit
If i run query with order clause then query takes 0.0012s with setting LIMIT 50 and without order clause, same query takes 0.0032s with same LIMIT 50.
Also how can we find if:
- Particular HQL Query is hitting database and not cache or getting information from session?
- 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?
Also in my below mentioned query:
a) Query q = session.createQuery("from RequestDao r order by r.id desc"); b) q.setFirstResult(0); c) q.setMaxResults(50);
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.
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.
Update
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.
I am making multiple nested hibernate queries to database and getting results back, what would be an optimal solution for this problem?
Your query tells database to sort all records that satisfy the WHERE clause. It potentially may sort millions of records before returning you top 50.
EDIT 1/26: Now that the specific questions were clarified, I'll try to respond more specifically.
Every time you execute query like that, Hibernate goes to the database. Even more, it would flush all new/updated data in the session to disk. If this is your situation, this behavior might contribute to the slowness.
Using Hibernate Query API usually performs quite well in most situations and is compatible with a broad variety of the database platforms. If you are really concerned about squeezing last drop of performance out of your data access layer, you can write your own native SQL query to select top 50 results. But as soon as you do that, you'll almost certainly loose database independence. So, evaluate your costs vs. benefits.
Your query run times appear to be in the single milliseconds range. This is usually as good as it gets with the relational databases that store data on disk. So you might want to evaluate whether you indeed have a performance problem.
EDIT 1/27: OK. It looks like the problem in the overall design of the page. I had been using AJAX for last 7 years or so, so I don't usually have to wait for the filtering UI controls to redraw when going through pages of a table. I guess, switching application UI frameworks is not an option in your case. You have to figure out how to optimize loading of the data for the dropdowns and such. Does this data change frequently? Can you cache it somewhere in the application? If you have to load them every time, can you get away with just getting the display strings instead of entire objects?
这篇关于使用HQL进行Hibernate分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
