When set to the default value of Both select and cursor, the generated code will first perform a SELECT statement, and if the sqlcode indicates that this resulted in multiple rows, it will then fetch the first row using a cursor.
In the situation where it is likely that more than one row will be returned, this will result in a wasted initial SELECT, so the code will be more efficient if the property is changed to Cursor only and thus avoid the expense of the SELECT.
Similarly, in the case where there can only be one row returned, for example when qualifying on the identifier or a cardinality one relationship, the generation of the cursor results in a larger memory requirement, code size and additional processing for compile and bind steps. In this situation, the code will be more efficient if the property is set to Select only.
For example, using this example data model:
The statement below should have the property set to Select only since a CHILD can only have one parent.
Whereas the statement below which checks to see if a parent has at least one child should have the property set to Cursor only since they can have many children.
A refinement to the check would be to leave the property as Both select and cursor in this situation of a parent has a average of one child.
IET have developed a check in VerifIEr which will validate the correct and optimum setting of this property. The initial version of this check is for a READ statement that qualifies using a single relationship. In the future we may extend this to cover more complex READ statements. The VerifIEr check also contains a genIE 'Fix' that enables the property to be reset to the optimum value.
