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
The Find aggregation function has added important improvements when used with SQL generators.
The Find aggregation function selects the first value of any model attribute that matches a condition.
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.
- 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.
Suppose we have the following structure:
When doing a For each to get CustomerParameterValue the navigation is:
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:
In this case, the resolution of CustomerParameterValue is resolved in a second navigation.
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:
For example CustomerName = Find( EntityName, EntityID = CustomerId, "Does not exist.").
- Default Value returned not empty.
Try to use an empty value so that the Find formula can be optimized.
For example, Find( ...) IF Condition.
This case can't be optimized because the condition is always evaluated on the client side.
This generally occurs when more than one table is available to solve the formula. If possible, add more constraints to eliminate any ambiguity.
- Logical dependencies between the tables involved in the resolution of a Find query.
Try to restrict the conditions to instantiate the table Primary Key or Candidate Key.
- The matching condition for the formula returns more than one Record.