One of the new features in Gen r8 that we have started to test is the ability to specify a multi-row fetch for READ EACH statements. This property changes the generated code to perform a block fetch of multiple rows into an array rather than fetching one row at a time.
A simple test of reading many rows from a large table using multi row fetch provided a 60% reduction in CPU compared with a standard READ EACH table. (The test was a DB2/COBOL batch job without much code apart from the READ EACH, so a real world example will show less of a gain)
The test compared various array sizes of 100, 1000 and 10000. There was very little difference in the cpu consumed between these, showing that there seems to be little benefit in setting a very large array size.
When we upgrade our own code to using Gen r8 for the next release, we plan to update all READ EACH statements to use this new option. To help do this, we have developed a new VerifIEr check that ensures that all READ EACH statements have this option specified and a corresponding genIE automated fix to set the property value. This intelligently inspects the code within the READ EACH to determine what array size to use, and it can also have an upper limit defined.
3 comments:
Will READ EACH continue to work if upgraded from 7.5 to R8? Should all instances of READ EACH be replaced with the new multi row fetch?
Are there any known issues while upgrading from 7.5 to R8? The platform is HP Unix and code is in C, the database is Oracle 9i.
Yes, the READ EACH will continue to work. To take advantage of a multi-row fetch, you then need to specify the additional multi-row property on each READ EACH statement. To make this easier, we automated this using VerifIEr which calculates the optimal array size and updates the READ EACH property.
Post a Comment