Function usage

Any query that contains a RAD system language function such as index() can potentially cause a full table scan because it may not translate to SQL. Generally, you want to limit the use of functions in queries to avoid degrading your system's performance every time the query is run. If you anticipate a query will run frequently, consider rewriting the query to use operators instead of functions. The most typical high-use queries are those in the following areas:

• Favorites
• Views
• Links
• Stored queries.

For example, suppose you want to create a view that displays closed high priority incidents with activity from a particular operator named Manager. You could create this query by using the index() function.

Inefficient query example: index() function

View definition field	View definition value
Table	probsummary
Query	(flag=false) and (priority.code<="2") and index("Manager", assignee.name)>0

As written this is an inefficient query. However, you can rewrite this query to use simple operators and prevent unnecessary table scans. For example, the following query accomplishes the same objective.

Efficient query example: using simple operators

View definition field	View definition value
Table	probsummary
Query	(flag=false) and (priority.code<="2") and (assignee.name like "*Manager*")

If you cannot rewrite the query (for example, if you are using an exclusive feature of the function), you can minimize its performance impact by limiting access to the query.