{"id":16436,"date":"2014-12-20T16:02:37","date_gmt":"2014-12-20T10:32:37","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=16436"},"modified":"2014-12-20T16:16:08","modified_gmt":"2014-12-20T10:46:08","slug":"grails-performance-optimization-unique-constraint","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/grails-performance-optimization-unique-constraint\/","title":{"rendered":"Grails performance optimization – Unique constraint"},"content":{"rendered":"

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:<\/p>\n

Suppose we have one domain having the following structure:<\/p>\n

[code]<\/p>\n

class User {<\/p>\n

String firstName
\nString lastName
\nString emailId<\/p>\n

static constraints = {
\n emailId nullable: false, blank: true, unique: true, email: true
\n }
\n}<\/p>\n

[\/code]<\/p>\n

To check database queries for this transaction enable the logging sql into Datasource.groovy. This will print all\u00a0database\u00a0queries trigger by grails application.<\/p>\n

[code]<\/p>\n

dataSource {
\nusername = ”
\npassword = ”
\ndbCreate = ‘create-drop’ \/\/ one of ‘create’, ‘create-drop’, ‘update’, ‘validate’, ”
\nurl = ‘jdbc:mysql:\/\/localhost:3306\/db_test?autoReconnect=true’
\nloggingSql\u00a0= true
\n}<\/p>\n

[\/code]<\/p>\n

We can also log sql for a piece of code as well, read this blog<\/a>.
\nNow create object of User domain and save it.<\/p>\n

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

[code]<\/p>\n

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=?
\nHibernate: 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=?
\nHibernate: insert into user (version, email_id, first_name, last_name) values (?, ?, ?, ?)<\/p>\n

[\/code]<\/p>\n

This might be a bug but we can fix it as well using following solution code:<\/p>\n

[code]<\/p>\n

User user = new User(firstName: ‘Deepak’, lastName: ‘Mittal’, emailId: ‘deepak.krmittal@intelligrape.com’)<\/p>\n

if(user.validate() && user.save(flush: true, validate: false)) {
\n println(‘Success’)
\n} else {
\n println(‘Error’)
\n}<\/p>\n

[\/code]<\/p>\n

When you run this code, then this will trigger only 2 queries like:<\/p>\n

[code]<\/p>\n

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=?
\nHibernate: insert into user (version, email_id, first_name, last_name) values (?, ?, ?, ?)<\/p>\n

[\/code]<\/p>\n

Now your purpose is solved with a little bit optimized code. We can further optimize this code in the next blog.<\/p>\n

Code written with Grails 2.4.3.<\/p>\n","protected":false},"excerpt":{"rendered":"

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: […]<\/p>\n","protected":false},"author":145,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":12,"footnotes":""},"categories":[7],"tags":[29,1576,1579,1577,442,648,1578],"class_list":["post-16436","post","type-post","status-publish","format-standard","hentry","category-grails","tag-gorm","tag-grails-constraints","tag-grails-hibernate","tag-grails-hibernate-query-logging","tag-grails-performance","tag-grails-sql-logging","tag-grails-unique-constraint"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t