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.
But:
- 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 (see OCI_ATTR_PREFETCH_ROWS)
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.
For example, using a parameter in isc_dsql_fetch or its equivalent.
As a result, this would solve all needs as well as OCI_ATTR_PREFETCH_ROWS, but better (adaptive modes described in the article).
Quote:
- "But obviously, it wastes a lot of time in the case of slow networks. So Firebird uses the asynchronous batching, also known as pipelining. As soon as all records of the batch are sent to the client, the server starts to fetch new records from the engine and cache them for the next transmission."
- "As soon as the client library has processed some part of the current batch, it asks the server for the next batch and continues processing the remaining records. This allows to distribute the load more evenly and provide a better overall throughput. The current (hardcoded) pipelining threshold is 1/2 of the batch size."