These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]

Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: Dialects and sql optimization infos
PostPosted: Mon Mar 08, 2004 3:57 am 

Joined: Mon Sep 15, 2003 8:06 am
Posts: 8
Hello all

Is there any way to specify to hibernate to add some extras in generated
sql, in order to specify optimization information to the underlying rdbms ?

Specifically, I use informix db with large amount of data in multiple
tables (tens of millions rows), so specifying a well-tuned execution-plan
to the db is a must-have. As informix is unable to find a pertinent
execution plan for queries with more than one join (its use of stats is
rather strange), in other projects relying directly on sql programmers
are used to specify a {+ordered} command in each sql query and
order the tables in the from in the order that must be used by informix.

As this command must appear just after the select keyword
(ie 'select {+ordered} toto from equip r where...'), I suppose this
should be specified somewhere by subclassing InformixDialect, but
does Dialect functionality allows specifying such a thing ?

Moreover, is there a documented way telling how tables are ordered
in the from clause of the sql order obtained from a given hql query ?

Thanks in advance for any advice in this topic.

Sincerely yours

Emmanuel Ligne

 Post subject:
PostPosted: Mon Mar 08, 2004 4:02 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
I think we plan to add a Query.setQueryHint() method, we just need to figure out what it would do on various platforms. For now, you can drop back to createSQLQuery() for performance-critical queries.

 Post subject: Dialects and query hints
PostPosted: Mon Feb 06, 2006 10:31 am 

Joined: Mon Sep 15, 2003 8:06 am
Posts: 8

Just going back into business after a long break from Hibernate.
Since a major revision of Hibernate has grown now, is there now a way
to specify hints in hibernate queries, or is it still in the to-do list, or even
definitely rejected ? Just searched into the 3.1 API and didn't find
anything near QueryHint or something similar.

 Post subject:
PostPosted: Mon Feb 13, 2006 10:38 am 

Joined: Tue Mar 22, 2005 2:27 am
Posts: 62

We also would like to see this functionality added to hibernate.
Being forced to re-implement HQL queries using native SQL to address performance problems is a big inconvenience.

Could a JIRA task for this work be created?

FYI, I did a little bit of checking, and it looks like both Informix and Oracle use pretty much the same syntax (i.e. SQL comments after the SELECT token):

http://www.dbasupport.com/oracle/ora9i/ ... ints.shtml
http://publib.boulder.ibm.com/infocente ... erf318.htm

MySQL seems to support adding hints as part of the join syntax:


PostgreSQL doesn't support query hints at all, so that dialect is a non-issue.

I imagine that there can't be too many possible permutations... if so, couldn't the Loader implementation completely delegate this special massaging of the SQL to the Dialect?

Please let us know if you'd be receptive to a patch to add this functionality, or if you have any advice on how to approach the problem.


 Post subject: Re: Dialects and sql optimization infos
PostPosted: Tue Jan 19, 2010 12:54 pm 

Joined: Wed Sep 06, 2006 12:08 pm
Posts: 48
Location: Warsaw - Poland - Europe - Milky Way

I've opened a New Feature Request in Hibernate JIRA.
http://opensource.atlassian.com/project ... e/HHH-2736

Vote for it, please! ;]

Adam Wo┼║niak

Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.