[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615?page=c... ] 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? -- 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira