Subject Firebird buffer fetch size, or how can I to improve fetching performance ?
Author
Post date 2018-08-30T15:07:21Z
Hi,

Is there a way to adjust the size of the Firebird fetch buffer size in Firebird 3 or tweak fetching? (in Windows)

I have a performance issue that occurs when doing dumps/exports of queries with many records of few / small fields.

Even when running the SQL in a client on the local server, fbclient.dll and firebird.exe service use a full CPU core each, and the amount of disk traffic is negligible.

It is possible to reproduce the issue by fetching all the rows of a "select * from test" with the following test database
it is a 5 MB zip that unzips to a 1 GB fdb file (it was created with a lot of data repetition to keep the zip small)

In terms of software, I have reproduced the issue with various Delphi libraries (UIB, FreeIB and bare-metal calls to isc_xxx), I do not specify a cursor name (isc_dsql_set_cursor_name is not called), and the performance issues can be reproduced even when there is just one read access connexion to the test database zipped above.

In all the cases, the executable making the isc_xxx calls has negligible CPU usage compared to fbclient.dll & firebird.exe, and that high CPU usage occurs with very limited disk I/O (everything appears in cache), so I surmise this is an issue with some fetching parameter between client and server.

The communication protocol does have an impact, with the testdb, the full fetch takes about 50 seconds here with TCP/IP (server:dbpath), 80 seconds with named pipes (\\server\dbpath), and by comparison a full table scan with 
select max(a), max(b), max(c), max(d) from test
takes just 8 seconds.

Thanks,

Eric