Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

subqueries in hibernate

Posted on 2004-09-28
12
Medium Priority
?
4,379 Views
Last Modified: 2013-11-24
Hi experts,

I have a table with approximately 80 columns, of which about 40 are references to other tables. They are defined as <many-to-one ... /> in hibernate mapping file.

When hibernate generates sql it uses "outer left join" keywords and generates a huge SQL clause. It takes about 20 seconds to execute this query.

I am using Oracle and using subqueries in select statements improves the  performance greatly.

Here is my question:

How can I make Hibernate 2.1 use sub-select statements instead of huge joins?

Eg. hibernate generates this

select person.name, city.name from T_PERSON person, T_CITY city
where person.city=city.code

And I want hibernate to generate this

select
    person.name,
    (select city.name from T_CITY city where city.code = person.city) as city_name
from
   T_PERSON person

thanks
0
Comment
Question by:alikoank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 12

Expert Comment

by:Giant2
ID: 12171108
Not all the databases permit the sub-select statement, so I believe hibernate not implement in this manner and use the outer join.
I believe you could increase the performance if you indexes your tables.

Bye, Giant.
0
 
LVL 4

Author Comment

by:alikoank
ID: 12177054
All my necessary tables have indexes.

I do not think hibernate omit sub-selects just because some vendor does not support them, if it is so shame on them. What is the point of using dialects anyway?

0
 
LVL 12

Expert Comment

by:Giant2
ID: 12177106
0
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
LVL 18

Expert Comment

by:armoghan
ID: 12177123
Hibernate 2.1 supports subselect if the underlying database supports it
http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html
0
 
LVL 4

Author Comment

by:alikoank
ID: 12177212
Ok, I think I need to clarify a bit, using subselects in HQL is not a problem, I want to find out if it is possible to modify the way hibernate generates SQL.

Here is the HQL statement I execute

"from com.Person"

in the mapping

<class name="com.Person">
     <many-to-one name="city" ../>
</class>

<class name="com.City">
  ...
</class>

when I issue my HQL hibernate generates something like following,

select person.name, city.name from T_PERSON person, T_CITY city
where person.city=city.code

And I want hibernate to generate this:

select
    person.name,
    (select city.name from T_CITY city where city.code = person.city) as city_name
from
   T_PERSON person

thanks
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12177228
I find this:
15.1.3. Properties
Database properties may be specified as system properties with -D<property>

in hibernate.properties

in a named properties file with --properties

The needed properties are:
Table 15.3. SchemaExport Connection Properties
Property Name                              Description
hibernate.connection.driver_class   jdbc driver class
hibernate.connection.url                 jdbc url
hibernate.connection.username       database user
hibernate.connection.password       user password
hibernate.dialect                            dialect

0
 
LVL 4

Author Comment

by:alikoank
ID: 12177263
I can already connect to database,

I used both OracleDialect and Oracle9Dialect, generated sql synax changes a bit and OracleDialect generates SQL which executes slightly faster but they both do use Outer joins.
0
 
LVL 35

Expert Comment

by:girionis
ID: 12177555
If I remember correctly there is the Criteria class where you can pass several criteria for your query. Maybe it will help.
0
 
LVL 4

Author Comment

by:alikoank
ID: 12320144
Still no answers.
I will ask CS to delete this question.
0
 
LVL 3

Accepted Solution

by:
Gunt earned 1500 total points
ID: 12468622
There's no way to do that. Dialects do not command the way an SQL is generated (structured), just hints as the keywords to use, ways to do database specific tasks (Sequence nextval, result limiting, etc), sql functions, and the such.

So, not even writting your own dialect you will be able to accomplish what you want. The only way, is to change the queries builder, which implies modifying Hibernate source code (specially SessionImpl and Loader classes). It would be a really challenging task.

Hibernate will ALWAYS fetch associations using outer joins, or just don't fetch them.
You can instruct Hibernate to not load the associations unless accessed, making them lazy (just like a collection, many-to-one associations can be set to lazy using the lazy="true").
This may improve your query, but if you access this associations latter, you will end up issuing a select for each accessed association (n+1 select problem).

Sorry.
0
 
LVL 4

Author Comment

by:alikoank
ID: 12470430
I wish there was a way to do that, sub queries improve Oracle perfomance greatly.

Thanks anyway.
0
 
LVL 12

Expert Comment

by:Giant2
ID: 12482716
Ok.
0

Featured Post

Quick Cloud Training

Looking for some quick training on the cloud in 2 hours or less? Check out these how-to guides in AWS, Linux, OpenStack, Azure, and more!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question