Row-by-row processing VS Set Processing with Example

 Row By Row Processing

· A Single row will be fetched and rest of actions will be performed and then next row will be fetched and rest of actions will be performed and next and so on..

· Program will fetch the data Row by Row from database and performs the Subsequent Actions.

    Sample Program

· Assume a table ABC is having EMPLID, DEPTID, and SALARY.

· If we wanted to give 10% hike to the employees who are belongs to ‘BBB’ Department.

EMPLID	DEPTID	SALARY
11111	AAA	10,000
22222	BBB	10,000
33333	BBB	25,000
44444	CCC	12,000
55555	BBB	20,000

· With Row By Row Processing, AE Program Structure would be

MAIN    Step1       DoSelect %Select(EMPLID) Select EMPLID From ABC WHERE DEPITD = ’BBB’       SQL UPDATE ABC SET SALARY = (0.10*SALARY) + SALARY WHERE EMPLID = %Bind(EMPLID);

· DoSelect will fetch 22222, 33333, and 55555 employees.

· In First Loop - Inserts 22222 into State Record.

· Executes the SQL Action which updates the Salary with 10% hike for 22222 Employee.

· In Second Loop - Inserts 33333 into State Record and then updates the Salary.

· In Third Loop – Inserts 55555 into State Record and then Updates the Salary.

· Then comes out from the DoSelect.

· As the program in Row by Row Processing, it goes to the database 3 times and updates the Salary.

     Set Processing

Set processing is nothing but processing set of rows at one time rather than processing each row individually.

     Sample Program 

· With Set Processing, AE Program Structure would be

MAIN    Step1       SQL UPDATE ABC SET SALARY = (0.10*SALARY) + SALARY WHERE DEPTID = ‘BBB’;

· SQL Action will be executed which updates the Salary for all the 3 employees at a time.