Batch update records in Grails

Question

I have to calculate a "total" for each user based on individual actions -- that is, a User hasMany Actions and each Action has a point. So I need to basically get a sum of all the points on all the actions.

Actions are added and subtracted regularly. Since this can be a rather heavy operation, it's not feasible that I execute the "total" each time I need it. Therefore, I am thinking of running the operation once a day for each user and storing the total points on the User as an int.

Because I have thousands of users, I am trying to figure out the best way to do this. Should I basically iterate through all the users and call User.save() each time, or is there some kind of batch update mechanism I can use in Grails / GORM?

Answer 1

Calling User.save() will not actually write the change to the database, it is only when the Hibernate session is flushed that the change is written (docs)

You can flush the session manually be accessing the SessionFactory and calling flush on the resulting session, as shown in this FAQ.

I imagine you would want to load the users using a batching technique to ensure you don't have all of the thousands of users in memory at the same time.

As an aside if you wanted to cache the value but have it automatically updated whenever you add an action you could hook into active record's events to update the calculated value, here is an example from Ruby

Answer 2

Is the model in your question your actual model, or a simplified version of it? If all you're doing is User hasMany Actions and each Action has a point value (an integer?) that you'd like to sum up, that's really the kind of thing that relational databases excel at. If you have the proper indexes on your database, I'd think this would be a very quick call, as long as you're doing a groupBy query (or using grails projection criteria).

Here's an example method that will take a list of users and will return an array that pairs user.id to the number of points that user currently has:

def calculateCurrentPoints(users) {
    Action.executeQuery('select a.user.id, sum(points) from Action a where a.user.id in (:userIds) group by a.user.id', [userIds: users.id])
}

If desired, you could easily turn this into a map of user id to points for easier look-up:

def calculateCurrentPoints(users) {
    def result = Action.executeQuery('select a.user.id, sum(points) from Action a where a.user.id in (:userIds) group by a.user.id', [userIds: users.id])
    result.inject([:]) { map, userWithPoints -> 
        map[userWithPoints[0]] = userWithPoints[1]
        return map
    }
}

If this really is slower than I'm thinking it is, you could use the executeUpdate with HQL similiar to what I have above to update a "totalPoints" field on each user (or keep it updated as part of a service whenever you add a new Action to that user).