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:

[code]

class User {

String firstName
String lastName
String emailId

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

[/code]

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

[code]

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

[/code]

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

[code]
User user = new User(firstName: ‘Deepak’, lastName: ‘Mittal’, emailId: ‘deepak.krmittal@intelligrape.com’)
user.save(flush: true)
[/code]
In case, your unique validation pass and if you check your console, then there are 3 queries triggered by grails application :

[code]

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 (?, ?, ?, ?)

[/code]

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

[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’)
}

[/code]

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

[code]

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 (?, ?, ?, ?)

[/code]

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 Reply

Your email address will not be published. Required fields are marked *