subqueries in hibernate

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, from T_PERSON person, T_CITY city

And I want hibernate to generate this

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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
alikoankAuthor Commented:
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?

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Hibernate 2.1 supports subselect if the underlying database supports it
alikoankAuthor Commented:
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 name="com.City">

when I issue my HQL hibernate generates something like following,

select, from T_PERSON person, T_CITY city

And I want hibernate to generate this:

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

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


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

alikoankAuthor Commented:
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.
If I remember correctly there is the Criteria class where you can pass several criteria for your query. Maybe it will help.
alikoankAuthor Commented:
Still no answers.
I will ask CS to delete this question.
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).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alikoankAuthor Commented:
I wish there was a way to do that, sub queries improve Oracle perfomance greatly.

Thanks anyway.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.