Add the ability to specify in the isc_dsql_fetch (or in another API) how many records need to be prefetched from the server.
This feature will allow to avoid the fragmentation of the on the network, as well as to avoid multiple context switching during server calls to fetch next several records.
As a result, this leads to an increase of the client application performance and a reduction multiple but small calls to the server — the select will be processed in large parts, and there will be no need to return to it many times.
I know that in the current implementation, Firebird independently returns as many records as fit in the packet of server exchange protocol.
- in some cases it may be necessary to more finely manage the number of returned records (based on the specifics of the application)
- this does not solve the problem of network fragmentation
- this makes it impossible for the server to process the request in bulk, rather than returning to it many times
- this does not solve the problem of permanent round-trip to the server to retrieve next records. Instead of one request and one big answer (let it be fragmented in parts, but sent at one time)
A similar feature is available in Oracle and Postgres.
Oracle documentation - https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci04sql.htm#i429698
Postgres documentation - https://www.postgresql.org/docs/9.6/sql-fetch.html
(see FORWARD count)
Oracle has 2 options:
1) use of OCI_ATTR_PREFETCH_ROWS - the requested number of records is cached in the client library internal buffer (oci.dll).
Then, on the next OCIStmtFetch calls, the records are taken from the internal buffer, and not requested from the server until the buffer is empty.
2) the ability to get an array of records immediately to the client's application buffer (not the client library internal buffer).
Postgres has only 1 option:
1) obtaining an array of records from the server to the client library internal buffer.
Then, the user application can access the row values of the records by index.
If necessary, the application can request the next portion of the array of records that will overwrite the current contents of the buffer.