You are not logged in.
Thanks for the numbers!
So Zeos seams WAY faster than IBX.
Nice to know it - but not unexpected.
About the Windows version detected, on you need a proper manifest in the executable to have Windows 10/11 be detected.
Add e.g.
{$R ..\src\mormot.win.default.manifest.res}
Offline
This results is expected with default config. 3 reasons:
1. Zeos have better parameters and results binding, low level access to Fb Api records, without copy strings/varchars.
2. Zeos only use CommitRetaining after any statement and never close/Commit transaction. Ibx use StartTransaction, execute statement and CommitTransaction. This is by design. You can simulate zeos transaction handling if you enable {$define ZEOSTRANS} at line 48 in mormot.db.sql.ibx.
3. fbintf for Fb3 and above use new Firebird3 interface, by default, which is slower then old legacy ICS/Interbase Api. zeos use only legacy Api. Look in fbintf, IB.pas lines 76-81, you can force to use legacy Api interface only for Fb3/4/5 by defining LEGACYFIREBIRDAPIONLY define to your project. You will see Direct/Trans insertion speed increase for Fb4/5 in range of Fb25 (Fb2.5 use only legacy interface)
Last edited by ttomas (2024-01-19 16:56:27)
Offline
ab, thanks for the tip.
Still, there may be a small typo, line 10 in ..\ex\extdb-bench\PerfTestConsole.dpr
{../../src/$R mormot.win.default.manifest.res}
ttomas, thank you for the clarification!
1 and 3 - clear.
2. A few words about this. As I wrote earlier in this thread, this long transaction leads to an increase records versioning for frequently updated tables, which for systems running 24/7 after some time, leads to a dramatically decrease performance for both reading and writing. Firebird 4 solved this problem at some point, but still, a long transaction that opens when the server starts, in my opinion, is not a very correct configuration. Therefore fbintf seems more suitable.
Offline
@ab, I create push request.
2. Correct, this is the reason I create ibx mormot driver. Main goal is to have 0 active transaction if nobody is connected to app/server.
{$define ZEOSTRANS} is only for testing, not recomended for production.
Last edited by ttomas (2024-01-19 23:48:10)
Offline
@vs, do you success build with delphi, if yes delphi version please. @ab alearedy commit changes in trunk.
Offline
2. Correct, this is the reason I create ibx mormot driver. Main goal is to have 0 active transaction if nobody is connected to app/server.
Great, thanks for sharing your experience!
{$define ZEOSTRANS} is only for testing, not recomended for production.
Ок, thanks. Yes, it is clear!
@vs, do you success build with delphi, if yes delphi version please. @ab alearedy commit changes in trunk.
Yes, with my local edits MVCServerFirebirdIbx compiling successful for both (Lazarus and Delphi 12) and working as expected, except FTS.
Tomorrow I'll try to play with ..\ex\extdb-bench\PerfTestConsole.dpr for both (Lazarus and Delphi 12) with the latest ab's commit.
I suspect that the performance figures that I gave a little earlier are not entirely correct.
Most likely the embedded version of fbclient.dll server from firebird 5 installation was used instead of connecting to a particular version of firebird server.
But this is just a guess, I'll try to figure it out tomorrow.
Add.
By the way. Thanks madorin, there is a pretty good library with precise transaction controls for Firebird. Separate for reading and writing transactions, at least.
Maybe this could be a starting point for the next Firebird driver for mORMot 2
https://github.com/madorin/fibplus/
https://github.com/madorin/fibplus/commits/master
Last edited by vs (2024-01-20 02:28:31)
Offline
More accurate test results.
Running tests using Synopse mORMot framework 2.2.6792, compiled with Free Pascal 3.2.2 64 bit, against SQLite 3.44.2, on Windows 11 64bit (10.0.22631), at 2024-01-20 20:02:16.
Insertion speed (rows/second) Read speed (rows/second)
Direct Batch Trans Batch Trans | By one All Virtual All Direct
Zeos firebird 25 2739 76921 5911 58403 | Zeos firebird 25 7811 407398 427313
Zeos firebird 4 2471 57124 7942 41158 | Zeos firebird 4 7868 391282 412609
Zeos firebird 5 1960 70443 7295 49310 | Zeos firebird 5 8212 413479 498529
Ibx firebird 25 756 43030 6628 40509 | Ibx firebird 25 1356 100883 117096
Ibx firebird 4 1824 33112 7650 42821 | Ibx firebird 4 1617 138475 104979
Ibx firebird 5 1367 60364 6479 40248 | Ibx firebird 5 1791 235510 211555
A separate test was run for each driver and each firebird server version.
Ibx firebird 25 did not pass the test due to an error: Solved by changing line 616 cMaxStm = 50 in mormot.db.sql.ibx at cMaxStm = 45
Project PerfTestConsole raised exception class 'EIBInterBaseError' with message:
Engine Code: 335544569
Dynamic SQL Error
-SQL error code = -204
-Implementation limit exceeded
-block size exceeds implementation restriction
In file 'FBClientAPI.pas' at line 412:
Last edited by vs (2024-01-21 17:10:44)
Offline
Do you change line 616 cMaxStm = 50 in mormot.db.sql.ibx. Previous test on 2.5 pass without problem. Fb3 have limit of 255 statements, Fb2.5???
Edited: Or execute statement size exceeded 64K limit on Fb2.5, Fb3 and above have limit of 10MB.
Batch insert create execute block statement with 50 insert statements inside.
Do you try to add define LEGACYFIREBIRDAPIONLY to force using of legacy api with Fb4/5?
Last edited by ttomas (2024-01-20 20:23:03)
Offline
Do you change line 616 cMaxStm = 50 in mormot.db.sql.ibx.
Yes, 48 and below working fine ))). I added the numbers for Ibx firebird 25 to my previous post.
Previous test on 2.5 pass without problem.
Because, as I wrote earlier, embedded version of fbclient.dll from firebird 5 installation was used instead of connecting to 2.5 firebird server
Edited: Or execute statement size exceeded 64K limit on Fb2.5, Fb3 and above have limit of 10MB.
Yes, that's right, https://www.firebirdfaq.org/faq197/
Do you try to add define LEGACYFIREBIRDAPIONLY to force using of legacy api with Fb4/5?
Yes, but without success.
! EXC EIBInterBaseError {Message:"Engine Code: 335544351\r\nunsuccessful metadata update"} [Main] at 0100375a46 client/include/wloadlibrary.inc tfbclientapi.ibdatabaseerror (209)
Offline
Thanks @vs for report of exception with LEGACYFIREBIRDAPIONLY. I found problem with definition of isc_start_transaction dll function in fbintf.
More info in this topic: https://forum.lazarus.freepascal.org/in … opic=66087
Offline