Use FETCH FOR n ROWS
Applications that perform many FETCH statements in succession could be improved by using FETCH FOR n ROWS. With this clause, you can retrieve multiple rows of table data with a single FETCH, putting them into a host structure array or row storage area.
An SQL application that uses a FETCH statement without the FOR n ROWS clause can be improved by using the multiple-row FETCH statement to retrieve multiple rows. After the host structure array or row storage area is filled by the FETCH, the application loops through the data, processing each of the individual rows. The statement runs faster because the SQL run-time was called only once and all the data was simultaneously returned to the application program.
You can change the application program to allow the database manager to block the rows that the SQL run-time retrieves from the tables.
In the following table, the program attempted to FETCH 100 rows into the application. Note the differences in the table for the number of calls to SQL runtime and the database manager when blocking can be performed.
|Database Manager Not Using Blocking||Database Manager Using Blocking|
|Single-Row FETCH Statement||100 SQL calls 100 database calls||100 SQL calls one database call|
|Multiple-Row FETCH Statement||one SQL runtime call 100 database calls||one SQL runtime call one database call|