Grails: populate a domain with data from cross join: hql, criteria, SQL query?

i'm a little bit stuck with that question. Let's say I have a domain class:

class Activity {
    String activityID
    String activityDescription
    String activityType
}

and this other one

class ActivityMap {
    String activityID1
    String activityDescription1
    String activityType1
    String activityID2
    String activityDescription2
    String activityType2
}

I want to populate ActivityMap domain class with the result of a cross join of domain class Activity with itself, that is with result of equivalent SQL query

SELECT a.activityID, a.activityDescription, a.activityType, a1.activityID, a1.activityDescription, a1.activityType
FROM Activity AS a, Activity AS a1
ORDER BY a.activityID, a1.activityID;

I'm not sure which is the most efficient way to achieve that: using Criteria, HQL or embedd开发者_StackOverflow中文版ed SQL query, as resulting set of data may be quite large (300-50+ Activity occurrences, 250k result rows expected)?

Any suggestions/tip, example?

Thanks in advance, Patrick

Hibernate have problems with queries returning such large results, even if there is 10k+ result rows. So 250k rows eats all your memory and cpu.

I recommend you to try with native SQL query, furthermore you not expecting actual results, only INSERT ... SELECT