Performing some function (for example DATE()), on an index field, decreases the efficiency of querying on an index field. The function is performed on each record and therefore the query retrieves every record from database table, thereby losing any gain.
Set up index brackets so that only records between the brackets are returned, eliminating the need for a
table scan.
For example: The following query allows the AVM to isolate the smallest possible index subset. This allows the AVM to return as few records as possible. The function, in this case, is performed on a constant, as opposed to fields in each record. Performing the function on the record field (TableName.DateTime) yields the same performance issues as is discussed in the problem section.
FOR EACH TableName
WHERE TableName.DateTime >= DATETIME(dtVariable, 0) AND
TableName.DateTime < DATETIME(dtVariable + 1, 0)
User Defined Functions (UDF) work differently than internal ABL functions, when it comes to queries. UDFs are evaluated only once for each query, as opposed to on every iteration. Using a UDF in a query such as the ones demonstrated above, may result in records different from what is expected.