poor Hibernate select performance comparing to running directly - how debug?

Question

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

Answer 1

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 *

Answer 2

To get a definitive answer, I think more info is needed. Primarily:

• Do you have any entity association or collection fields in the SalesUnit entity class? <-- This would be my first guess for the performance differences you are seeing in lieu of any other info.

• Do you have the same performance issue when running the query using the same JDBC driver in a non Hibernate environment? (i.e. Using a 3rd party JDBC client like DbVisualizer).

Also,

Although it's impossible to know for sure with info in your question, I think you wanted to query for all SalesUnits that do not have a child SalesUnit. Correct? (It depends on what you are using sales_unit_relation table for.) If so, you wrote your query like this:

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";

But, I think you likely wanted something more like this:

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

As is, your query is joining against the column you filter for null in the WHERE clause. If that really is what you wanted, then you could have also written it as an INNER JOIN without the WHERE clause, right?

Lastly,

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

That is incorrect as stated. HQL and Criteria do not support left join only if there is not a mapped relationship between entities/tables being queried. So the assumption per your example would be that there is not a mapped entity relationship between SalesUnit and whatever entity/association the sales_unit_relation table represents. (A native query should work regardless, but if there was a mapped relationship, then one benefit to using HQL/Criteria would be that you could do a left join fetch.)