Wednesday, 31 March 2010

Multi Row Fetch

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.