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:
- You retrieve only the data you need from the database, and
- 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.