Closed hubeny closed 2 years ago
AlexPeshkoff
commented
2 years ago On 6/30/22 17:45, hubeny wrote:
The question of this issue is: Can I somehow force Jaybird/Firebird to use the full size of the network packet (1500 MTU) when making lot of inserts to database in order to have smaller number of packets (which will be routed on network with higher latency)?
The short answer for Firebird is yes, using IBatch (in FB4).
hubeny
commented
2 years ago OK, thank you. Firebird v4 has IBatch. Are there plans to backport this feature to FB3?
Is IBatch feature of Firebird supported in Jaybird?
If yes,
1) In which version of Jaybird is IBatch feature of FB4 supported?
2) Is it supported both in PURE_JAVA and NATIVE driver?
AlexPeshkoff
commented
2 years ago On 7/1/22 09:44, hubeny wrote:
OK, thank you. Firebird v4 has IBatch. Are there plans to backport this feature to FB3?
No.
Is IBatch feature of Firebird supported in Jaybird? If yes,
- In which version of Jaybird is IBatch feature of FB4 supported?
- Is it supported both in PURE_JAVA and NATIVE driver?
Should be present - it was designed in a way to support JDBC batches.
vasiliy-yashkov
commented
2 years ago Hi, @hubeny !
If you are interested in using batch operations in jaybird, you can try our Red Soft Jaybird driver with IBatch implementation. IBatch is supported in NATIVE driver only.
You can also use nexus repository to download artifacts:
<repository>
<id>nexus-red-soft-ru-jaybird</id>
<url>https://nexus.red-soft.ru/repository/jaybird</url>
</repository>
...
<dependency>
<groupId>ru.red-soft.jdbc</groupId>
<artifactId>jaybird-jdk18</artifactId>
<version>4.0.18</version>
</dependency>The use of batches is available via protocols jdbc:firebirdsql:fboo:native: or jdbc:firebird:fboo:native:. Also you need to make sure that fbclient library is located on the PATH environment variable (on Windows) or LD_LIBRARY_PATH environment variable (on Linux).
If you have any questions, write to vasiliy.yashkov@red-soft.ru
mrotteveel
commented
2 years ago On 7/1/22 09:44, hubeny wrote: OK, thank you. Firebird v4 has IBatch. Are there plans to backport this feature to FB3?
Is IBatch feature of Firebird supported in Jaybird? If yes, 1. In which version of Jaybird is IBatch feature of FB4 supported? 2. Is it supported both in PURE_JAVA and NATIVE driver?
Should be present - it was designed in a way to support JDBC batches.
No, it is not present, as documented in the Jaybird 4 release notes, in Notes on Firebird 4 support:
Jaybird 4 [..] does not implement the new batch protocol.
The normal Jaybird implementation does not support the batching added in the Firebird protocol. I haven't gotten around to implementing it, and given the protocol and packet content for batching was (and partially still is) undocumented, it hasn't been a priority for me.
mrotteveel
commented
2 years ago This would have been more suitable to ask on the firebird-java Google Group than here, at least as a starting point.
The basic problem is that Jaybird needs to execute each insert individually, and that means that each execute (assuming auto-commit mode is false, and use of a prepared statement) will generate at minimum one request to the server, and one response back (with either "success" or an error if the execute failed) for each execute. This also means it is not possible to "force Jaybird/Firebird to use the full size of the network packet"
If an update count is needed (e.g. executeUpdate or batch execution), it will need 2 requests to the server, and 2 responses. This also explains your observed difference between using execute() and addBatch()/executeBatch(), as this last option requires the update counts (per set of parameters!).
Jaybird emulates JDBC batch execution, so it doesn't result in any performance gain. In fact, as you discovered, compared to execute() it performs even worse, though in auto-commit mode it would perform better compared to individual executes as it avoids transaction commit + transaction start (which requires an extra 2 requests and 2 responses).
Based on the provided information, I don't think there is a possibility to improve performance, other than Jaybird implementing support for Firebird 4+ batch execution. As Vasiliy mentioned, if you're using Firebird 4, you could also switch to RedSoft's fork of Jaybird and use their support for batch execution (native only).
mrotteveel
commented
2 years ago Created #695 for implementing Firebird 4 batch protocol. I'm closing this issue. For further discussion, I suggest posting to firebird-java.
Hi,
Problem description
I created a simple single-purpose datapump application, which reads data from Oracle Express database and writes data to Firebird database. The datapump copies hundreds of thousands of records, there are no blobs in tables just short varchars and integers. We have several VLANs in our corporate network. We currently have database servers in server VLAN and client computers in client VLAN. All traffic between this two VLANs is routed. The datapump task, which I describe here, runs on a client PC and connects to Oracle DB (for reading) and to Firebird DB (write) on two different servers, but both are in the server VLAN. I tried to place databases and datapump application on different servers and positions within VLANs and found that the running time of the application strongly depends on where the application and firebird database is located and whether traffic between application and firebird database passes through the router (packets must be routed). Further, the oracle JDBC driver uses the full size of the TCP IP packet when reading data, but jaybird/firebird driver did not when writing to database (it will be shown later).
The question of this issue is: Can I somehow force Jaybird/Firebird to use the full size of the network packet (1500 MTU) when making lot of inserts to database in order to have smaller number of packets (which will be routed on network with higher latency)?
Detailed problem description
We use the Jaybird library in the latest version 4.0.6, firebird in the latest version 3.0.10. Here are the results. I measure runtime of the datapump in three scenarios:
1) Everything runs locally on same computer (oracle db, firebird db and datapump): Runtime: Firebird related: 51.232418 seconds Oracle related: 7.991233 seconds
3) Oracle remotely, firebird remotely, datapump is on the same VLAN as the oracle and firebird database Runtime: Firebird related: 215.621674 seconds Oracle related: 22.818972 seconds
4) Oracle remotely, firebird remotely, datapump is on the different VLAN than the oracle and firebird database Runtime: Firebird related: 1227,511597 seconds Oracle related: 111,871330 seconds
On both databases are tables with same names and with columns of same type. The code of the application can be simplified to following (not complete code, just for idea):
I found out that the main reason that the write to firebird database is that firebird protocol under creates too much small packets. See listing bellow (see the end of the lines of TCP dump):
Further I experiment with PURE_JAVA and NATIVE type of Jaybird connection.
Jaybird with pure-java driver, output from tcpdump:
Jaybird with native driver (tested that it is really used, after complete removal of firebird libraries libfbclient.so from PC, stopped working), firebird version is the last 3.0 - 3.0.10 output from tcpdump:
It can be seen that the length of the packet increased slightly, but otherwise nothing.
I also thought of experimenting with jaybird addBatch and executeBatch. I.e. instead of dropping individual inserts into the database in a while loop, I tried to do insertStmt.addBatch () in the while loop and finally insertStmt.executeBatch ()
Jaybird with pure-java driver, output from tcpdump:
Jaybird with native driver
Here you can see that compared to the classic insertStmt.execute (), there are two more packets in the packet pattern for both implementations - this also confirms the measured result that the runtime is even worse via addBatch and executeBatch!
So for me, for the given task (tens of thousands of inserts into firebird via Jaybird) there is no difference in whether I use the pure-java implementation or the native library of the latest version (firebird 3).