Batch inserts with Firebird 3

[abk964]

I try to execute butch with 384 inserts with Firebird 3. Really executed just first chunk with 126 lines.
Do not know, why FStatementType <> stInsert in TZAbstractFirebirdInterbasePreparedStatement.GetExecuteBlockString, but even it will be limited at 256 lines (in case of FStatementType = stInsert), chunking logic does not use PreparedRowsOfArray in TZAbstractFirebirdStatement.Prepare:
FinalChunkSize := (BatchDMLArrayCount mod FMaxRowsPerBatch);
FMaxRowsPerBatch is 26215, so even if prepared just 126 lines, first chunk is executed only.
Seems, something wrong there.

[marsupilami]

Hello,

could you please prepare a small sample application and a create script for the database? That really would help me in debugging the issue.

Best regards,

Jan

[abk964]

Hi.

Test project (Delphi 7) attached.
Create table:
CREATE TABLE BATCH_TEST_TABLE (
VAL_FIELD INTEGER
);

Set Host and Database in Connection component.
Is it enough?

Seems batch limitation logic was made for FB 2.x and has to be reviewed for FB 3.

[abk964]

I can't see my posts in this topic. Try to resend last.
Delphi 7 project attached.
Create table:

Code: Select all

CREATE TABLE BATCH_TEST_TABLE (
    VAL_FIELD  INTEGER
);

[marsupilami]

Hello,

I didn't get to approve new posts for some days. This is why you couldn't see your posts. You should be a regular member now. Your posts should be visible immediately.

Best regards,

Jan

[abk964]

Hi.

OK. Is this example enough to debug?

[marsupilami]

I tried your program. With this program i hit an internal lilmit of Firebird I get the following error:

Too many Contexts of Relation/Procedure/Views. Maximum allowed is 255; GDS Code: 335544800

I assume that your insert statement is much longer?

When reducing the count to 250 I get no data at all into the database. I tested this using an FB3.0 server and FB2.5 client library.

[abk964]

I can't connect to Firebird server (version WI-V6.3.7.33374 Firebird 3.0) with FB 2.5 client library (version 2.5.9.27139). Do not know, why. Keep using FBCLIENT.DLL version 3.0.7.33374.

But your result looks even more strange.
As I understand tracing TZAbstractFirebirdStatement.Prepare, Zeos device FB limits and execute chunks of acceptable size, so this error must not exists.
There are 2 limits: FMaxRowsPerBatch and PreparedRowsOfArray. FMaxRowsPerBatch defines count on chunks, and PreparedRowsOfArray - count of operators in chunk.
In case of FB3 FMaxRowsPerBatch = 171898 and PreparedRowsOfArray = 127 (for my environment). So executes 1 chunk (BatchDMLArrayCount < FMaxRowsPerBatch) with 127 inserts, other data are lost.
In case of FB2.5 (WI-V6.3.7.27050 Firebird 2.5) FMaxRowsPerBatch = 537 and PreparedRowsOfArray = 127. Again 1 chunk with 127 operators.

In case of more complex inserts (more fields) with FB2.5 limits are lower, so count of chunks more then 1, but count of chunks and last chunk size are counted for FMaxRowsPerBatch = 83, but block consists of 127 inserts, not 83). I think, minimal example is enough to find conflict between these 2 limits, if it will be reproduced.

To control results in table, switch ZConnectionFB.AutoCommit to true. And can add

Code: Select all

ZConnectionFB.ExecuteDirect('delete from BATCH_TEST_TABLE');

as 1 line in ExecButtonClick.

I'm confused by your results, so if you want to dive deeper, set breakpoint to line

Code: Select all

FinalChunkSize := (BatchDMLArrayCount mod FMaxRowsPerBatch);

in TZAbstractFirebirdStatement.Prepare in ZDbcFirebirdStatement.pas and check values of FMaxRowsPerBatch and PreparedRowsOfArray.

PS. Situation appears with r7814 and now - r7867.

[miab3]

Hi Jan,

I tried your program. With this program i hit an internal lilmit of Firebird I get the following error:

Too many Contexts of Relation/Procedure/Views. Maximum allowed is 255; GDS Code: 335544800

I assume that your insert statement is much longer?

When reducing the count to 250 I get no data at all into the database. I tested this using an FB3.0 server and FB2.5 client library.

Weird. I have never been able to connect the FB2.5 client to the FB3.0 servers. The opposite is true.

Michał

[marsupilami]

Hello Michal,

Weird. I have never been able to connect the FB2.5 client to the FB3.0 servers. The opposite is true.

I had to think about this for some time. You might have to enable the legacy authentication. Firebird 3 and Firebird 4 introduced new default authentication schemes and new user mangement plugins which are used as the default by the installer. Also legacy authentication is not enabled by default by the installers for Windows.

Best regards,

Jan

[Fr0sT]

Weird. I have never been able to connect the FB2.5 client to the FB3.0 servers. The opposite is true.

I successfully did that. Just add Legacy_Auth to firebird.conf:

AuthClient = Srp, Srp256, Win_Sspi, Legacy_Auth #Windows clients