Grails withCriteria fetch, or a fast way to sort in predetermined order

Question

A database function returns id's of all events within a certain radius, ordered by their distance.

Afterwards, to preserve performance I'm eagerly loading necessary collections in withCriteria like this:

    def events = Event.withCriteria {
        'in'('id', ids)
        fetchMode("someRelation", FetchMode.JOIN)
        // a few more joins
        setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
    }

However this messes up the ordering. I've noticed that the result of this criteria query returns all events sorted by id. Which does somewhat make sense since in doesn't guarantee any sort of special ordering (nor does it make any sense that it should). However this poses a bit of a problem, since I want this list to be ordered.

So what I did was this:

    List<Event> temp = [];
    ids.each { id -> temp << events.find { it.id == id } }
    events = temp;

However when the list contains ~2400 elements this piece of code adds around 1 second to total execution time which is something I wish to lower as much as possible.

Is there any other way of doing this which could speed up the process?

Organic

Have you tried adding order("id", "asc") to your criteria? – Warms

@Warms That would order my events by their id in ascending order. However that is not what I want. I have a predetermined order or event id's by distance (which i receive from my database function, and distance is not a property but a calculated value). I wish to somehow keep that order, or quickly sort according to it in/after the criteria in my thread post. – Organic

Look at this question #4690414 . You could try this technique using Grails HQL interface grails.github.io/grails-doc/latest/guide/GORM.html#hql – Warms

@Warms thanks for the help, that's what I'm looking for, however since I'm using postgres as my RDBMS I don't have the luxury of order by field and since I'm also using distinct due to eager fetch joins I can't seem to use the alternative solutions for field ordering. I guess I'll have to come up with something else. – Organic

Answer 1

I think that there are at least three way to solve your problem (I've done additional researches and considerations):

SQL way

According to this gist, ordering by field is possibile also with Postgres and not only in mysql, but it's a little bit tricky since it's not directly supported. As you can read in the gist discussion there are different approaches, but I think that the cleaner and simpler is the last: adding a custom order by clause!

ORDER BY (ID=10, ID=2, ID=56, ID=40) DESC

To create a custom SQL query in Grails that returns objects with a specific type, you can follow this tutorial

Groovy way (your current)

// only four id, for the sake of simplicity
def ids = [10, 2, 56, 40];

// get your events with your criteria
def events = Event.withCriteria {
    'in'('id', ids)
    fetchMode("someRelation", FetchMode.JOIN)
    // a few more joins
    setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
}

// create an array that maps event.id -> sortedEventIndex
def indexToIdArray = [];
// event with id 2 is at index 1
// event with id 10 is at index 0 
// ...
// [null, null, 1 , null, null, null, null, null, null, null, 0, ...]
ids.eachWithIndex{ num, idx -> indexToIdArray[$num] = $idx }

def sortedEvents = [];
events.each { ev -> sortedEvents[indexToIdArray[ev.id]] = ev }

In this way you have sorted in O(n), consuming some additional memory. I don't know if this solution really performs better than your but you should give it a try.

See also this interesting article on how to find performance issue in your groovy code.

Client way

Return the unsorted list of events to the client with sorting data, then sort them in the client. If a client of your application can sort the events list in more ways, I think that this solution could be useful (every sorting action is done only client side).

the 4th, the 5th, ...

Since this is a also a performance problem, there are other possible solutions (depends on your domain/environment):

• Cache eagerly everything, even the ordered result
• Use a fixed list of points for sorting (if I'm near p1, use p1 for sorting)
• Your ideas

I hope this helps.

Warms