Grails: Updating a single object in a domain, ".save" Vs ".executeUpdate"

Question

I'm wondering what the most efficient way of updating a single value in a domain class from a row in the database. Lets say the domain class has 20+ fields

    def itemId = 1

    def item = Item.get(itemId)
    itemId.itemUsage += 1
    Item.executeUpdate("update Item set itemUsage=(:itemUsage) where id =(:itemId)", [usageCount: itemId.itemUsage, itemId: itemId.id])

    vs

    def item = Item.get(itemId)
    itemId.itemUsage += 1
    itemId.save(flush:true)

Answer 1

I think they are equal. The both issue 2 sql calls.

More efficient would be just a single update

Item.executeUpdate("update Item set itemUsage=itemUsage+1 where id =(:itemId)", [ itemId: itemId.id])

Answer 2

If you use get/save, you'll get the maximum advantage of the hibernate cache. executeUpdate might force more selects and updates.

The way executeUpdate interacts with the hibernate cache makes a difference here. The hibernate cache gets invalidated on executeUpdate. The next access of that Item after the executeUpdate would have to go to the database (and possibly more, I think hibernate might invalidate all Items in the cache).

Your best bet is to turn on debug logging for 'org.hibernate' in your Config.groovy and examine the SQL calls.

Answer 3

executeUpdate is more efficient if the size and number of the un-updated fields is large (and this is subjective). It's how I often delete instances too, running 'delete from Foo where id=123' since it seems wasteful to me to load the instance fully just to call delete() on it.

If you have large strings in your domain class and use the get() and save() approach then you serialize all of that data from the database to the web server twice unnecessarily when all you need to change is one field.

The effect on the 2nd-level cache needs to be considered if you're using it (and if you edit instances a lot you probably shouldn't). With executeUpdate it will flush all instances previously loaded with get() but if you update with get + save if flushes just that one instance. This gets worse if you're clustered since after executeUpdate you'd clear all of the various cluster node caches vs flushing the one instance on all nodes.

Your best bet is to benchmark both approaches. If you're not overloading the database then you may be prematurely optimizing and using the standard approach might be best to keep things simple while you solve other problems.