OptimizedUpdate

Unofficial Content

In previous versions, when using the For Each command to update data, the generated code updated each record through a SELECT / DO WHILE structure. As from 9.0 version, this pattern is identified and generated as an UPDATE structure. This greatly improves performance because all updates are done directly with one sentence instead of one update per row.

Description

When the following mass update pattern is identified:

For Each   Where <Cond>
  Att = <Expression>
  Att = <Expression>
EndFor

an "UPDATE where <cond>" is generated.

<Expression>
It can include attributes, variables or constants. All the assigned (updated) attributes must belong to the same table.

Where <Cond>
The condition can only include attributes that belong to the updated table. Certain types of functions can be included in the condition. 

Note: no other commands, such as calls, IFs, when, etc., can be included within the FOR EACH group. Only assignments to attributes are admitted (optimized).

When the massive update pattern is identified, the navigation shows it as follows:

For Each EMPLOYEE (Line: 3)
...
Optimizations: Update

It indicates that the pattern has been identified and that an Optimized Update has been generated.

Samples

1) The following procedure increases all employees' salaries by 10%.

For each
   EmployeeSalary=EmployeeSalary*1.1
endfor


2) The following procedure increases all employees' salaries by 10%, too. The only variant that it presents with respect to the previous one, is that it uses a variable instead of using the fixed value directly.

&Var=1.1
For each
 EmployeeSalary=EmployeeSalary*&Var
endfor


3) The following procedure increases by 10% the salaries of all employees between 30 and 40 years old. That is to say that in this case there are also filters defined (where clause).

For each  
    where EmployeeAge>=30 and EmployeeAge<=40
          EmployeeSalary=EmployeeSalary*1.1  
endfor


4) The following procedure increases by 10% the salaries of all employees whose name start with "C". Notice that it is being used a Function within the where clause.

For each
   where substr(EmployeeName,1,1) = "C"
   EmployeeSalary=EmployeeSalary*1.1
endfor

Note: <Expression> cannot be a formula.


Comments & Collaboration

Compatibility issues could appear depending on the <Expression> and <Att> datatype definition. See related SAC.
 

Scope

Language: Java, .NET, .NET Mobile
Objects: Procedures
DBMS: DB2 UDB, Informix, Oracle, MS SQL Server, MySQL, PostgreSQL.


See Also

Server Side Functions/Methods