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?

Your question is interesting. Whe I willl have time, I'll try to answer you properly. — fabien7474, May 29 '10 at 08:55
You don't need the total to be accurate at all times? Why not just calculate a user's total on the fly? — Mike Sickler, May 29 '10 at 20:53
Mike - That is the way I initially did it, but it had a pretty significant impact on performance --especially when I need to display the total for several users at once. It's a pretty complex calculation in my situation. — RyanLynch, May 30 '10 at 01:29

score 1 · Accepted Answer · answered May 31 '10 at 03:39

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

score 0 · Answer 2 · edited May 23 '17 at 11:48

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

Batch update records in Grails

2 Answers2