Use comparison methods instead of the Where method

As a performance best practice, Guidewire recommends that you always use comparison methods on query objects made with the query builder APIs. Never convert a query builder result object to a collection and then use the where method on the collection to specify the criteria for the query result. PolicyCenter applies comparison methods whenever a query executes, and the database returns only qualifying entity instances to the application. Without any comparison methods, converting a query result to a collection loads all instances of the primary entity type into the application cache. The where method on the collection then creates a second collection with the qualifying instances.

Comparison methods example

The following sample Gosu code queries the database for addresses in the city of Chicago by using a compare method on the query object. The select object returns only Chicago addresses from the database. The database executes the query at the time the code calls the iterator method.

uses gw.api.database.Query 

 var queryObject = Query.make(Address)           // Create a query object.

 queryObject.compare(Address#City, Equals, "Chicago")  // Apply qualifying, where-clause, criteria. 

 var selectObject = queryObject.select()         // Convert the query object to a select object.

 var resultIterator = selectObject.iterator()    // Convert the select object to an iterator object, 
                                                //   which causes the query to be executed 
                                                //   in the database.

 for (address in resultIterator) { // Iterate the the qualifying addresses in result object.
    print (address.AddressLine1 + ", " + address.City  + ", " + address.PostalCode)

   }

The preceding sample code performs efficiently, because the database selects the Chicago addresses. The sample code also uses the application cache efficiently, because it loads only the Chicago addresses into application memory.

The Where method example

In contrast to a comparison method on a query object, the where method on a collection performs less efficiently and is highly inefficient in its use of application memory. For example, the following sample Gosu code queries the database for all addresses because it omits comparison methods on the query object. The code then converts the result object, with all addresses, to a collection. Finally, the code calls the where method on the collection to create a second collection with only addresses in the city of Chicago. 

uses gw.api.database.Query 

 var resultContainer = Query.make(Address).select().toCollection().where(  // Fetch all addresses into
                                                                          //   a collection and make
        \ elt -> elt.City == "Chicago"                                    //   another collection with 
  )                                                                       //   qualifying addresses.

 for (address in resultContainer ) {  // Iterate the second container with the qualifying addresses.
  print (address.AddressLine1 + ", " + address.City  + ", " + address.PostalCode)

   }

Returning all addresses to the application uses the cache inefficiently by loading it with unwanted addresses. Converting the result to a collection uses the application heap inefficiently by loading the collection with unwanted addressees. Calling the where method on the collection to select only addresses in Chicago performs the selection much less efficiently than the database.