[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1615) GROUP BY entity does not work

[Giampaolo Tomassoni (JIRA)]

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

Giampaolo Tomassoni commented on HHH-1615:
------------------------------------------

You mean, to fetch each single field with a subselect? Timings are also strongly tied to the number of fields to fetch, then. Right?

I don't have an Oracle installation handy. I can only produce timings with PostgreSQL 8.2.

Nor I have a suitable companies/products database handy. But I have an Italian provinces/localities database relating 15125 localities (towns) many-to-one with the corresponding province from a set of 104.

Timings are this way:

Case 1: explode attributes.

	SELECT p.idlocality, p.code, p.name, COUNT(*)
	FROM italy.provinces AS p INNER JOIN italy.cities AS c ON c.idprovince=p.idlocality
	WHERE p.code NOT LIKE '_A'
	GROUP BY p.idlocality, p.code, p.name
	HAVING COUNT(*) > 100
	ORDER BY p.name ASC

Output row count: 69
Total runtime: 36.062 ms


Case 2: wrapping dereferencing query

	SELECT p1.idlocality, p1.code, p1.name, p2.cnt
	FROM italy.provinces AS p1 RIGHT OUTER JOIN (
		SELECT p.idlocality, COUNT(*) AS cnt
		FROM italy.provinces AS p INNER JOIN italy.cities AS c ON c.idprovince=p.idlocality
		WHERE p.code NOT LIKE '_A'
		GROUP BY p.idlocality
		HAVING COUNT(*) > 100
	) AS p2 ON p1.idlocality=p2.idlocality
	ORDER BY p1.name ASC

Output row count: 69 (of course, the same as case#1)
Total runtime: 29.829 ms

Now, I believe that in this examples timings differences mostly depend on attribute dereferencing, not on the "core" query. They depend on the count of output rows, then.

Hoever, it seems to me that case#2 performs better and that this improvement in performances would be even more evident as the number of output rows increases.

Finally, albeit all this performance tests aresomehow related to this issue, I think it would be better to open a thread in some forum. In this way we could even exange test datasets, don't you agree?

> GROUP BY entity does not work
> -----------------------------
>
>                 Key: HHH-1615
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615
>             Project: Hibernate3
>          Issue Type: Improvement
>          Components: query-hql
>    Affects Versions: 3.2.5
>         Environment: PostgreSQL 8.1.3, Hibernate 3.1.3
>            Reporter: Xavier Bugaud
>            Assignee: Anthony Patricio
>            Priority: Critical
>         Attachments: hibernate-group-by.diff
>
>
> The query : "select cat, count(*) from Cat cat group by cat" does not work.
> ERROR: column "xxxx" must appear in the GROUP BY clause or be used in an aggregate function
> See :
>  - http://forum.hibernate.org/viewtopic.php?t=953716
>  - http://jira.nhibernate.org/browse/NH-528

-- 
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