[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-544) select new ... results in n+1 selects

[Dan Allen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-544?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27758 ] 

Dan Allen commented on HHH-544:
-------------------------------

Would it be reasonable to request some way to add a hint that forces an eager load?

As a particular use case, I needed to query for a set of associated entities and their frequency within a table.

select q, count(*) from Queue q group by q

So to make things pretty and object oriented, I made a QueueFrequency object

select new QueueFrequency(q, count(*)) from Queue q group by q

The result set List<QueueFrequency> is then passed on to some other area of the code and does calculations on those queues and their frequencies. As soon as that code touches the queue in one of the QueueFrequency instances, it triggers a select (eventually leading to n+1)

As a workaround, I just ran the first query, and then iterated over its results to generate the QueueFrequency objects, and then returned the List<QueueFrequency> result. It just seems lame not to be able to push this work into the session manager.

> select new ... results in n+1 selects
> -------------------------------------
>
>                 Key: HHH-544
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-544
>             Project: Hibernate3
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 3.0.5
>         Environment: Hibernate 3.0.5, Oracle 8i, JDK 1.4.2
>            Reporter: Salvatore Insalaco
>            Priority: Minor
>
> Using collection constructors in select clause of HQL (e.g. new map(...)) generates n + 1 SQL statements (one for primary keys, one for each object).
> This is a problem when there're clauses as "distinct" and "order by":
> select distinct o from MyEntity o order by o.description
> works, because in the generated SQL all the columns are mapped in the select clause; but:
> select distinct new map (o as result) from MyEntity o order by o.description
> doesn't work, because the first line of generated SQL is like:
> select distinct id from MyTable order by description
> and that's invalid sql (at least in Oracle): description shoud be placed in select clause.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira