Tips to improve Query Performance

While there are many functions, here is a handy list of properties that should be used whenever a [color=blue]query API[/color] is used to fetch results.

Empty – Informs whether the result set (of multiple items) is empty. The common mistake is to use Count property just to understand if there are rows that match the query. Do not use Count property if we only want to know if there are rows that match our query in the data set. Relational query performance often improves if you use Empty property.

Pls use the following best practices

// Correct

var results = Query.make(Person).compare(Person#Code,Equals, code). withLogSQL(true).select()
if(results.Empty) // This is the right WAY !
     // Logic here 


var results = Query.make(SomeEntity).compare("Code_Ext", Equals, code).withLogSQL(true).select()

if(results.Count > 0) // This is NOT the right WAY !{ 
     // Logic here 

• AtMostOneRow – Use this whenever a maximum of 1 result is expected.

var account =  Query.make(Account).compare(Account#AccountNumber, Equals, accountNumber).select().AtMostOneRow

FirstResult – DO NOT use first(). The first() function loads the entire result set into a collection.
Instead use FirstResult , when multiple results may be retrieved, but any result from the result set would suffice. Relational query performance often improves when we use the FirstResult property to access only the first item in a result.

var result = Query.make(Person).compare(Person#Code,Equals, code). withLogSQL(true). select().FirstResult

Posted 4 years ago


Gordon Freeman   4 years ago Report
0 Answers
4 years ago
4 years ago