Code optimization when processing just the first record of a set
Scope
Languages: ALL
Objects: ALL
Introduction
In many times it is necessary to process just the first element of a set. E.g. suppose you want to know if a customer has already bought any products. In that case, you should make a query over the Invoices table to find at least one invoice for that customer:
&ret = 0
For each
where CustomerID = 50
Defined by InvoiceDate
&ret = 1
Exit
Endfor
Although only one record will be fetch, the code above would traditionally
"prepare" a SELECT statement over Invoices table returning all the invoices for customer 50. However, why it would be necessary to
"prepare" a select in order to bring all the invoices for customer 50 if just bringing one invoice would be enough? We must take into account that if we create an optimized
"prepare" statement the DBMS will then manage to handle the query in a more efficient way.
Description
For that kind of situations there is a feature that really improves the application performance. The optimization is made every time we have something like:
For Each [Order]
[Where ...]
[Defined by ...]
....
Exit
EndFor
As we already said, the main idea is that we only want to process the first record of a set. The optimization consists in indicating the DBMS that we only want the first element of the set that matches the conditions (the
"prepare" statement is optimized). Consequently, the DBMS can then make optimizations in how it handles the search and internal work areas. On the other hand, only one record will be returned from the DBMS to the application so the process will be faster.
The way of indicating the DBMS that it is only necessary to process one record is using specials clauses that will be added in the SELECT statement. These clauses are off course different for each DBMS. E.g. TOP (SQLServer), FETCH FIRST n ROWS ONLY (DB2 ISeries).
The optimization will then be shown in the navigation diagram as:
Optimizations: First 1 record(s)
Considerations:
- The Exit command must be immediately before the EndFor or can have comments between the Exit and the EndFor .
- The For Each can access more than a table (with join in the client or the server) whenever all the conditions can be evaluated in the DBMS.
- It applies as from GeneXus 9.0