Optimize Grails Queries

Question

I'm trying to optimize the speed in my grails app. I have this:

Catalog a= Catalog.findByName('a');
Element b= Element.findByCatalogAndNumber(a,2);

This way i can find b.

But I'm thinking I could use something like this:

Element b= Element.createCriteria().get{
       catalog{
          eq("name",'a')
       }
       eq("number",2)
}

But I'm not sure if it reduces the queries to the database, or I'm just making a fool of myself and creating even bigger files and reducing the speed of my app by doing this.

any idea?

Answer 1

I have compared three versions of your query using

• Grails 2.4.4, default settings for caches in a the Grails application
• PostgreSQL 8.4, SQL statement logging has been turned on to count/see the SQL queries.

---

The first versions one using two calls on the Grails domain class:

def query1() {
  Catalog a = Catalog.findByName('a');
  log.info(a)

  Element b = Element.findByCatalogAndPos(a, 2);
  log.info(b)

  render(b.toString())
}

The 2nd one using criteria

def query2() {
  Element b = Element.createCriteria().get {
    catalog {
      eq("name", "a")
    }
    eq("pos", 2)
  }

  render(b.toString())
}

and the last one using a where query

def query3() {
  def query = Element.where {
    catalog.name == "a" && pos == 2
  }

  Element b = query.get()

  render(b.toString())
}

The first one results in two SQL queries, the other ones will only send one query to the database (using an inner join from Element to Catalog).

As for readability/expressiveness, choose the 3rd version: It expresses your intention in a single line, and it's the most compact version.

As for performance, choose the 2nd or the 3rd version. Under high load, many concurrent users/requests, the number of queries does matter. This might not be an issue for all applications.

Anway, I'd always choose the 3rd version for the expressiveness; and it will scale, if the query conditions gets more complex over the time.

---

Update

The SQL statements used by the 1st version:

select this_.id as id1_1_0_, this_.version as version2_1_0_, this_.date_created as date_cre3_1_0_, this_.last_updated as last_upd4_1_0_, this_.name as name5_1_0_, this_.remark as remark6_1_0_ 
  from catalog this_ 
  where this_.name=$1 limit $2
Parameter: $1 = 'a', $2 = '1'

select this_.id as id1_2_0_, this_.version as version2_2_0_, this_.catalog_id as catalog_3_2_0_, this_.date_created as date_cre4_2_0_, this_.last_updated as last_upd5_2_0_, this_.pos as pos6_2_0_, this_.remark as remark7_2_0_ 
  from element this_ 
  where this_.catalog_id=$1 and this_.pos=$2 limit $3
Parameter: $1 = '10', $2 = '2', $3 = '1'

---

The SQL statement for the 2nd and 3rd version:

select this_.id as id1_2_1_, this_.version as version2_2_1_, this_.catalog_id as catalog_3_2_1_, this_.date_created as date_cre4_2_1_, this_.last_updated as last_upd5_2_1_, this_.pos as pos6_2_1_, this_.remark as remark7_2_1_, catalog_al1_.id as id1_1_0_, catalog_al1_.version as version2_1_0_, catalog_al1_.date_created as date_cre3_1_0_, catalog_al1_.last_updated as last_upd4_1_0_, catalog_al1_.name as name5_1_0_, catalog_al1_.remark as remark6_1_0_ 
  from element this_ inner join catalog catalog_al1_ 
    on this_.catalog_id=catalog_al1_.id 
    where (catalog_al1_.name=$1) and this_.pos=$2
Parameter: $1 = 'a', $2 = '2'