When processing a result set, the application must explicitly or implicitly issue a fetch to retrieve each row of data into the application. If this were done a single row at a time, there would be a substantial overhead in making multiple CLI function calls. In addition, each time a fetch is issued to the underlying call level interface, a network message may need to be sent to the database server to retrieve the data for the row. Obviously, there would be a substantial performance gain if we could retrieve the data for more than one row each time we issue a fetch to the call level interface. The statement’s rowSetSize parameter does precisely this. However, from the application’s perspective, rows are still fetched (implicitly or explicitly) one row at a time as the complexity of handling row set sizes greater than one are hidden by the Advanced Database feature.
By default, the Advanced Database feature adjusts the row set size for each query so that the total memory used to retrieve multiple rows data does not exceed a fixed amount of 102400 bytes. This was determined by performance tests to provide reasonable response times for most queries. However, if the application developer knows how many rows they expect to receive, they should specify the desired row set size. This can be done by sending the rowSetSize: message to an instance of the UtyDB2SqlDescriptor class. It is also possible, using the same message, to set the row set size on an instance of the UtyDB2ResultTable class. However, this change can only be done prior to accessing any of the result table’s row data.
sd := connection newSqlDescriptor sql: 'SELECT * FROM TEMP'; rowSetSize: 10; yourself. result := sd executeOn: connection. rows := result rows. result := connection executeSql: 'SELECT * FROM TEMP'. result rowSetSize: 10. rows := result rows.