Grails performance optimization – Unique constraint
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.
