1. Groovy Scripting Reference
  2. Explicitly Select Only the Attributes You Need

Explicitly Select Only the Attributes You Need

When performing a business object query, it's important to indicate which fields your code will access from the results. This includes fields your logic plans to update as well. By doing this proactively, your application gains two advantages:

  1. You retrieve only the data you need from the database, and
  2. You avoid an additional system-initiated query to "fault-in" missing data on first reference
Call the selectAttributesBeforeQuery() function to select the attributes your code will access before it performs a view object's query. As shown in the example below, the first parameter is a view object you have created with newView() and the second argument is a case-sensitive list of field names. If you are including a sort in your query by calling setSortBy(), make sure to include the sort field name(s) in the selected attributes list as well. 
def employees = newView('StaffMember')
addBindVariable(employees,'Job','Text')
addBindVariable(employees,'Dept','Number')
// Make sure that JobId or DepartmentId is indexed!
employees.appendViewCriteria('JobId = :Job and DepartmentId = :Dept')
employees.setSortBy('Salary desc')
selectAttributesBeforeQuery(employees,['Email','LastName','FirstName','Salary'])
setBindVariable(employees,'Job','SH_CLERK')
setBindVariable(employees,'Dept',50)
employees.executeQuery()
while (employees.hasNext()) {
  def employee = employees.next()
  // Work with employee.Email, employee.LastName, employee.FirstName, employee.Salary
}
Important: If you fail to call the selectAttributesBeforeQuery() function before executing a view object for a custom object you've created with newView(), then by default the query will retrieve only the primary key field from the database when initially performing the query, and then for each row of the while loop as soon as your code references one of the other attributes like Email, LastName, FirstName, or Salary, the system is forced to perform an additional query to retrieve all of the current row's fields from the database using the primary key. If your object has 200 fields, this means retrieving 200 fields of data even through your code may actually reference only four of them. This can quickly lead to your application's performing many, many avoidable extra queries and fetching much unnecessary data. Neither of these situations is good for performance.
Using the queryMaps() or queryRows() helper functions described in Simplifying Business Logic Queries you can perform the same optimized query in the example above with fewer lines of code like this:
// Make sure that JobId or DepartmentId is indexed!
for (employee in adf.util.queryRows(select:'Email,LastName,FirstName,Salary',
                                      from: 'StaffMember',
                                     where: 'JobId = :Job and DepartmentId = :Dept',
                                   orderBy: 'Salary desc',
                                     binds:[Job:'SH_CLERK',Dept:50]) {
  // Work with employee.Email, employee.LastName, employee.FirstName, employee.Salary
}

Under the covers, the query helper function calls all of the other functions above, including selectAttributesBeforeQuery(), to produce the data your application needs to process.

Related Topics