OptimizeFindNavigation

Unofficial Content

Optimize Find Navigation

Scope

Languages: Java, .NET, .NET mobile, Visual Basic, Visual FoxPro
Objects: Work Panels, Web Panels, Procedures, Reports
DBMS: Oracle, SQL Server, DB2 Universal Database, DB2 UDB for iSeries, PostgreSQL, mySQL, Informix

Introduction

The Find aggregation function has added important improvements when used with SQL generators.

Description

The Find aggregation function selects the first value of any model attribute that matches a condition.

Syntax

Find(Att, Cond, Def)
Type Returned: Same as argument attribute
Att: Attribute to find
Cond: Condition to be evaluated
Def: Default value to be returned if the search is unsuccessful


Up to GeneXus 8.0 the resolution of Find formulas involves the generation of two Navigations.
The first one is associated to the base table navigation and the second navigation is triggered for each record associated to the base table to resolve the Formula.

Now on, if the Formula meets the considerations to be optimized, it belongs to the extended table. This means:
  • The formula is resolved in only one navigation, improving automatically it´s performance.
  • The formula can also be used as an order or filter in any navigation.

The optimization takes place when the matching condition for the formula returns only one record and no default value is set. It means that the complete key of the table where the query is resolved (Departure table) is addressed by means of its Primary or Candidate Key.

Considerations

  • If several Find queries can be resolved in the same table with the same conditions, everything is resolved in the same navigation.
  • This feature is supported for all SQL generators.
  • The Specification Report shows which Find is optimized. A READ allowing nulls section between the tables involved is displayed; otherwise, the navigation needed to resolve the Find query is displayed.
  • The ModelObject Property Optimize Find Navigation is added to enable or disable the optimization; the default value is Yes.

Examples

Suppose we have the following structure:

img/wiki_up//frm_transaction_structure.gif

When doing a For each to get CustomerParameterValue the navigation is:

img/wiki_up//frm_foreach_optimize.gif

Note that the formula CustomerParameterValue is resolved in one navigation.

When the property is disabled or the formula is not optimizable the navigation will be:

img/wiki_up//frm_foreach_nooptimize.gif

In this case, the resolution of CustomerParameterValue is resolved in a second navigation.

Tips to Optimize Find Formulas

In order to improve performance, analyze all the non-optimized Finds() and check if it's possible to modify them.

Also, check if the lack of optimization is caused by one of the following reasons, and modify their definition whenever possible:
  • Default Value returned not empty.
For example CustomerName = Find( EntityName, EntityID = CustomerId, "Does not exist.").
Try to use an empty value so that the Find formula can be optimized.
  • Conditional Find
For example, Find( ...) IF Condition.
This case can't be optimized because the condition is always evaluated on the client side.
  • Logical dependencies between the tables involved in the resolution of a Find query.
This generally occurs when more than one table is available to solve the formula. If possible, add more constraints to eliminate any ambiguity.
  • The matching condition for the formula returns more than one Record.
Try to restrict the conditions to instantiate the table Primary Key or Candidate Key.