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)
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:
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
{../../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.
]]>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}
Running tests using Synopse mORMot framework 2.2.6749, compiled with Free Pascal 3.2.2 64 bit, against SQLite 3.44.2, on Windows 8 64bit (6.2.9200), at 2024-01-19 13:28:17.
Insertion speed | Read speed
Direct Batch Trans Batch Trans | By one All Virtual All Direct
Zeos firebird 25 2265 60781 16408 65852 | Zeos firebird 25 10884 366770 399840
Zeos firebird 4 2187 55836 6949 63136 | Zeos firebird 4 6944 373538 412864
Zeos firebird 5 2473 65590 8084 52379 | Zeos firebird 5 8154 403177 490196
Ibx firebird 25 2366 43955 15503 40030 | Ibx firebird 25 3850 164076 120290
Ibx firebird 4 1296 37603 3675 49165 | Ibx firebird 4 1798 106503 99975
Ibx firebird 5 1275 30746 4452 40146 | Ibx firebird 5 1967 221028 214850
For some reason log shows Windows 8 64bit, but in fact Windows 11 64bit.
]]>Yes need to be changed to string. I will try to create push request.
Thank you so much.
Fixing these two issues
for Lazarus:
MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"
MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"
and
for Delphi:
[dcc32 Error] mormot.db.sql.ibx.pas(120): E2010 Incompatible types: 'UTF8String' and 'string'
[dcc32 Error] MVCServerFirebirdIbx.dpr(54): E2003 Undeclared identifier: 'CreateDescendingPK'
MVCServerFirebirdIbx compiling successful for both and working as expected, except FTS
]]>MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"
property CreateDescendingPK was changed to CreateDescendingOnlyPK, ex\mvc-blog need to change
MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"
I usually copy this file to project folder from mormot2/src. Line 10 can be changed to:
{$R ..\..\src\mormot.win.default.manifest.res}
mormot.db.sql.ibx.pas(119):
property FirebirdLibraryPathName: RawUtf8
but
mormot.db.sql.ibx.pas(87):
fFirebirdLibraryPathName: string;
Yes need to be changed to string. I will try to create push request.
]]>For Lazarus:
MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"
MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"
mormot.db.sql.ibx.pas(119):
property FirebirdLibraryPathName: RawUtf8
but
mormot.db.sql.ibx.pas(87):
fFirebirdLibraryPathName: string;