JPA 2 - 如何缓存/优化计数查询？
我有 Java EE 应用程序，其中有组列表。我想显示每个组中的项目数，所以我这样做：
SELECT COUNT(p) FROM Product p WHERE LOWER(p.group) = LOWER(:group)
组是纯字符串。
问题是当我有 50 多个组时，加载页面的时间约为 10 秒，这是不可接受的。当我删除计数时间下降到〜1秒。我该如何优化/缓存这个？
我的第一个想法是使用 EJB 单例在本地保存计数并使用 EJB 计时器刷新它们。
我在同一台机器上使用 glassfish v3、eclipselink 和 oracle 10g 数据库。 我在 eclipselink 中使用缓存，但它似乎不缓存计数查询。
<property name="eclipselink.cache.shared.default" value="false"/>
<property name="eclipselink.cache.size.default" value="500"/>
I have Java EE application, where I have list of groups. I want to display number of items in every group, so I do:
SELECT COUNT(p) FROM Product p WHERE LOWER(p.group) = LOWER(:group)
Group is plain String.
Problem is when I have 50+ groups, time to load page is about 10s, which is unacceptable. When I remove counting time drops to ~1s. How can I optimize/cache this?
My first idea was to use EJB singleton to keep counts locally and refresh them with EJB timer.
I'm using glassfish v3, eclipselink and oracle 10g database on same machine.
I'm using cache in eclipselink, but it seems not to cache count queries.
<property name="eclipselink.cache.shared.default" value="false"/>
<property name="eclipselink.cache.size.default" value="500"/>
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在一个查询中选择所有组和关联计数怎么样？这不是 JPA 答案，而是一个普通的 SQL 答案：
How about selecting all groups and associated counts in one query? This is not a JPA answer, but a plain SQL one:
长查询通常是数据库中缺少索引的标志。尝试在
product(lower(group))上添加索引。另外，在不知道确切的用例的情况下很难说，但我发现使用产品表中的组标签来获取组的产品数量很奇怪。您不应该有一个 Group 实体，并在产品表中引用该组的主键吗？这将消除对
lower(group)的索引的需要（但需要对product.group_id的索引）。A long query is usually the sign of a missing index in the database. Try adding an index on
product(lower(group)).Also, it's hard to say withount knowing the excact use-case, but I find it strange to get the number of products of a group using a group label in the product table. Shouldn't you have a Group entity, and reference the group's primary key in the product table? This would eliminate the need of an index on
lower(group)(but would need an index onproduct.group_id).如果您无论如何都要传入
:group作为参数，我会将降低部分委托给 java 代码。无论如何，这可能已经被 SQL 解释器优化掉了，但值得一试......
If you are passing in
:groupas parameter anyway, I would delegate the lowering part into java code.This might have been optimized out by the SQL interpreter anyway, but it's worth a try...