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:
- 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.
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.