Best Practices for Writing Effective SQML
Proper Field Selection
Avoid Unnecessary Fields
For example, if svmx_installed_product is not used in related code, remove it from SELECT in queries:
SELECT svmx_name, svmx_installed_product FROM svmx_job
Avoid Unnecessary Joins
For example, the following query has an extra join on the svmx_installed_product table.
SELECT svmx_name, svmx_installed_product.io_uuid FROM svmx_job
Here is the same query with the extra join removed.
SELECT svmx_name, svmx_installed_product FROM svmx_job
 |
Be sure to prepend field names with underscores (_) in Groovy to read their values as UUIDs. For example, specify job._svmx_installed_product instead of job.svmx_installed_product.io_uuid, which causes an extra query to the related installed product.
|
Avoid Lazy Entity Reloading
For example, the following query to the svmx_appointment table has a join on the svmx_resource table via the svmx_assigned_to field that returns the svmx_name field in the same query. This implementation avoids entity reloading when svmx_assigned_to.svmx_name is read later on.
SELECT svmx_appointment.svmx_assigned_to.svmx_name FROM svmx_appointment
In cases where svmx_name is loaded in other locations, avoid the join and use the UUID value of _svmx_assigned_to to find the name.
SELECT svmx_appointment.svmx_assigned_to FROM svmx_appointment
Use Appropriate Filters
• Filter on indexed fields.
• Use selective filters to reduce the number of records returned by queries.
• Avoid ILIKE conditions that start with percent (%) wildcard characters, which cannot use indexes.
• Avoid filtering on Recalculated-on-Read fields, whose field values are calculated in real time.
Other
• Use LIMIT.
• Use Order by for indexed fields.
Programming Tips
• Use MaxObject._fieldFullIdentifier to get Relationship field values as UUIDs without getting the related records.
• Use MaxObject.getValue to get field values for unchanged records.
• Use cache when working with data that is used repeatedly in Groovy operations. Get the data in advance and use cache to retain the data in the operation to remove the need to read from the database repeatedly.
• Break complex queries into simpler queries to take advantage of indexes.
• Query on incremental data instead of the full data set.
• Avoid unneeded actions by using check conditions, such as isFieldChanged and getContextParameter.
• Avoid using queries in loops.
• Avoid loading large numbers of records in a single query by using queryInBatches, for example:
Database.queryInBatches(String smql, Map namedParameterValues, int batchSize, Closure objectMapper)
For more information: