Grails performance optimization – Unique constraint

20 / Dec / 2014 by Deepak Kumar Mittal 0 comments

Grails unique constraint is very powerful way to check duplicate keys in any DBMS, but it has some impact on application performance. Lets check it by one example:

Suppose we have one domain having the following structure:


class User {

String firstName
String lastName
String emailId

static constraints = {
     emailId nullable: false, blank: true, unique: true, email: true
  }
}

To check database queries for this transaction enable the logging sql into Datasource.groovy. This will print all database queries trigger by grails application.


dataSource {
username = ''
password = ''
dbCreate = 'create-drop' // one of 'create', 'create-drop', 'update', 'validate', ''
url = 'jdbc:mysql://localhost:3306/db_test?autoReconnect=true'
loggingSql = true
}

We can also log sql for a piece of code as well, read this blog.
Now create object of User domain and save it.

User user = new User(firstName: 'Deepak', lastName: 'Mittal', emailId: 'deepak.krmittal@intelligrape.com')
user.save(flush: true)

In case, your unique validation pass and if you check your console, then there are 3 queries triggered by grails application :


Hibernate: select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.email_id as email_id3_3_0_, this_.first_name as first_na4_3_0_, this_.last_name as last_nam5_3_0_ from user this_ where this_.email_id=?
Hibernate: select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.email_id as email_id3_3_0_, this_.first_name as first_na4_3_0_, this_.last_name as last_nam5_3_0_ from user this_ where this_.email_id=?
Hibernate: insert into user (version, email_id, first_name, last_name) values (?, ?, ?, ?)

This might be a bug but we can fix it as well using following solution code:


User user = new User(firstName: 'Deepak', lastName: 'Mittal', emailId: 'deepak.krmittal@intelligrape.com')

if(user.validate() && user.save(flush: true, validate: false)) {
    println('Success')
} else {
    println('Error')
}

When you run this code, then this will trigger only 2 queries like:


Hibernate: select this_.id as id1_3_0_, this_.version as version2_3_0_, this_.email_id as email_id3_3_0_, this_.first_name as first_na4_3_0_, this_.last_name as last_nam5_3_0_ from user this_ where this_.email_id=?
Hibernate: insert into user (version, email_id, first_name, last_name) values (?, ?, ?, ?)

Now your purpose is solved with a little bit optimized code. We can further optimize this code in the next blog.

Code written with Grails 2.4.3.

FOUND THIS USEFUL? SHARE IT

Leave a comment -