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

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.

Hello

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.

Hi,

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:

http://dev.mysql.com/doc/refman/5.1/en/join.html

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.

Thanks.

Hi

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

Vote for it, please! ;]

Smiles,
Adam Woźniak