差的Hibernate选择性能比较直接运行 - 如何调试？ [英] poor Hibernate select performance comparing to running directly - how debug?

问题描述

这让我发疯。与通过Navicat直接运行该查询相比，使得hibernate简单选择非常缓慢。更有趣的是什么。使用本地数据库运行这个查询非常快，但是远程使用它真的很差。

我正在执行Hibernate原生SQL查询（因为HQL或Criteria不支持）

  List list = new ArrayList（）; 
 String queryStr =select s。* from sales_unit s left join sales_unit_relation r on（s.sales_unit_id = r.sales_unit_child_id）where r.sales_unit_child_id is null; 
 Query query = session.createSQLQuery（queryStr）.addEntity（SalesUnit.class）; 
 
长启动= System.currentTimeMillis（）; 
 list.addAll（query.list（））; 
 Long stop = System.currentTimeMillis（）; 
 System.out.println（Time：+（stop  -  start）+ms。）; 
  

实体的结构并不重要。 SALES_UNIT和SALES_UNIT_RELATION表共有约28k条记录。 使用本地数据库在我的本地JBoss 上运行的结果大约在30-120ms之间。在远程 databasem，本地JBoss（相同数据）上运行时，结果在30000到40000毫秒之间。当我使用Navicat运行这个查询时，本地和远程调用都非常快（20-30ms）。

本地和远程数据库都以相同方式安装 - > Oracle企业版11.2.0.1.0。

这可能是性能低下的问题吗？如何调试它？

阅读此：简单的hibernate查询返回非常缓慢，但设置构造函数并没有改变任何东西

编辑。

SALES_UNIT 表包含销售单位节点的一些基本信息，例如名称等。唯一的关联是将表SALES_UNIT_TYPE作为ManyToOne。主键是ID和字段VALID_FROM_DTTM，它是date。

SALES_UNIT_RELATION 包含销售单位节点间的关系PARENT-CHILD。由SALES_UNIT_PARENT_ID，SALES_UNIT_CHILD_ID和VALID_TO_DTTM / VALID_FROM_DTTM组成。与任何表格没有关联。这里的PK是..PARENT_ID，..CHILD_ID和VALID_FROM_DTTM

解决方案

谢谢大家的帮助。经过很长一段时间的努力，这个问题，最后kaliatech答案帮助我调试问题。

首先，我犯了一个可怕的错误。我写道：
$ b

使用本地数据库运行这个查询非常快，但是远程使用它真的很差。

因为它不完全正确。我在Hibernate中所做的查询看起来像是一个：

  select s。* from sales_unit s left join sales_unit_relation r on s.sales_unit_id = r.sales_unit_child_id）其中r.sales_unit_child_id为空
  

但是我所做的实际查询以SQL PLus或Navicat为例：

  select * from sales_unit s left join sales_unit_relation r on（s.sales_unit_id = r。 sales_unit_child_id）其中r.sales_unit_child_id为空
  

请注意，第一个查询选择开始： select s。* ... ，第二个是 select * ... 。这就是表现如此糟糕的原因。现在这两个查询都很快完成。问题是，有什么不同：性能问题：选择s之间的差异。 * vs select *

It's driving me crazy. Making hibernate simple select is so slow, comparing to running that query directly via Navicat. What is more intereting. Running this query with local database is really fast, but using it remotely is really poor.

I'm doing following Hibernate native SQL query (as HQL or Criteria does not suppor left join):

List list = new ArrayList();
String queryStr = "select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null";
Query query = session.createSQLQuery( queryStr ).addEntity( SalesUnit.class );

Long start = System.currentTimeMillis();
list.addAll( query.list() );
Long stop = System.currentTimeMillis();
System.out.println( "Time: " + (stop - start) + "ms." );

Structure of Entity doesn't matter really. There's around 28k record for both SALES_UNIT and SALES_UNIT_RELATION table

The results, runned on my local JBoss with local databse, are around 30-120ms. While running on remote databasem, local JBoss (same data), results in time's between 30000-40000ms. When I'm running this query with Navicat, both local and remote calls are really fast (20-30ms).

Both local and remote database were installed same way -> Oracle Enterprise Edition 11.2.0.1.0.

WHat might be the problem of such poor performance? How can I debug it?

Read this: Simple hibernate query returning very slowly , but setting constructors didn't change anything

EDIT.

SALES_UNIT table contains some basic info abot sales unit node such as name and etc. The only association is to table SALES_UNIT_TYPE, as ManyToOne. The primary key is ID and field VALID_FROM_DTTM which is date.

SALES_UNIT_RELATION contains relation PARENT-CHILD between sales unit nodes. Consists of SALES_UNIT_PARENT_ID, SALES_UNIT_CHILD_ID and VALID_TO_DTTM/VALID_FROM_DTTM. No association with any tables. The PK here is ..PARENT_ID, ..CHILD_ID and VALID_FROM_DTTM

解决方案

Thank you all for help. After long time of struggling with that issue, finally kaliatech answer helped me to debug the problem.

First of all, I've made a terrible mistake in my quesion. I wrote that:

Running this query with local database is really fast, but using it remotely is really poor.

As it is not completly true. The query which I did in Hibernate looks like the one up:

select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null

But the actual query which I did with SQL PLus or Navicat for example was:

select * from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null

Please notice that first query select starts: select s.* ... and second one is select * .... And that was the reason of such poor performance. Now both queries are completed in no time. The question is, what's the difference: performance issue: difference between select s.* vs select *

这篇关于差的Hibernate选择性能比较直接运行 - 如何调试？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！