Salesforce

Will function performed on index slow a query?

« Go Back

Information

 
TitleWill function performed on index slow a query?
URL NameWill-function-performed-on-index-slow-a-query
Article Number000156295
EnvironmentProduct: OpenEdge
Version: 11.6
OS: All supported platforms
Question/Problem Description
Will performing a DATE function on that datetime index field decrease the query performance?
Does adding a DATETIME field as a database index for improving query performance.

For example:
 
FOR EACH TableName
    WHERE DATE(TableName.FieldName) = <some date>



 
Steps to Reproduce
Clarifying Information
DATETIME field added as index for improving the performance of a query.
DATE function used to isolate the date from the DATETIME variable.

An "index bracket" is a set of consecutive entries in an index. The bracket is set up by defining a query composed of one or more index components that are at the beginning of the index and contiguous. The AVM uses this to isolate the smallest necessary index subset, so that the fewest records are returned.

A "table scan" represents a query wherein all table records are accessed to complete the query. A query involving an index bracket generally involves many less records and takes less time, then does a query that performs a table scan. 
Error Message
Defect Number
Enhancement Number
Cause
Resolution
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.
Workaround
Notes
References to other Documentation:
OpenEdge Web Paper: ABL Database Triggers and Indexes, Chapter 2, "Database Index Usage > How ABL chooses and brackets indexes to satisfy queries".

Progress Articles:

 Single Index Selection Explained
 Can't call a function from a query WHERE clause.
 Index Brackets Explained
 Progress Indexing and Bracketing Rules
Keyword Phrase
Last Modified Date11/20/2020 7:24 AM

Powered by