#1 2018-03-28 12:56:24

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

ZEOsLib 7.2 ,7.3

Arnaud, could you correct it in:
SynDBZeos.pas - Line701:

    'firebird:','','postgresql:','',''); // M.A. was 'firebird-2.5:','','postgresql-9:','','');

In ZEOS 7.3, it will no longer distinguish the version.

ZEOS 7.2.4(p) - current version with the current patches.
https://sourceforge.net/p/zeoslib/code- … 2-patches/

ZEOS 7.3.x - development version (with added OLE DB and ODBC).
http://sourceforge.net/p/zeoslib/code-0 … sting-7.3/

Michal

Offline

#2 2018-03-28 13:36:51

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: ZEOsLib 7.2 ,7.3

Running tests using Synopse mORMot framework 1.18.4411, compiled with Delphi 10 Seattle-Win32, against SQLite 3.22.0, at 2018-03-28 15:25:54.
ZEOS 7.3 svn 4439

Insertion speed
 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	91	3354	42227	67349
SQLite3 (file off)	143	9260	61751	123878
SQLite3 (file off exc)	17270	176348	74585	219346
SQLite3 (mem)	60141	226295	75340	233623
ZEOS SQlite3	68	1406	34188	93281
FireDAC SQlite3	12481	17488	37676	122952
UniDAC SQlite3	62	1725	19395	27923
ODBC MSSQL2012	1355	18867	2167	20610
UniDAC MSSQL2012	1425	16641	1675	17969
ZEOS ODBC_W MSSQL2012	1547	30759	2097	36073
ZEOS ODBC_A MSSQL2012	1477	37499	2108	34394
ZEOS OleDB MSSQL2012	1464	6036	2441	39043
FireDAC MSSQL2012	1446	5215	2235	32500
ZEOS Firebird	359	4312	6818	5887
Oracle	844	62797	1554	63160
ZEOS Oracle	836	53167	1888	57174
FireDAC Oracle	856	44356	1591	47445
UniDAC Oracle	801	3734	1412	3954
ZEOS PostgreSQL	1031	16384	1944	18856
FireDAC PostgreSQL	1002	14832	1679	13438
UniDAC PostgreSQL	546	7709	731	10072
ZEOS MySQL	2105	26943	2316	21840
FireDAC MySQL	1623	31357	1769	29642
UniDAC MySQL	1984	13529	2426	16366

Read speed
 	By one	All Virtual	All Direct
SQLite3 (file full)	6858	423405	443301
SQLite3 (file off)	7474	446388	459389
SQLite3 (file off exc)	79483	452652	463821
SQLite3 (mem)	79249	449721	459263
ZEOS SQlite3	55367	196556	354032
FireDAC SQlite3	5757	92196	132303
UniDAC SQlite3	1514	78125	108875
ODBC MSSQL2012	2171	81319	200024
UniDAC MSSQL2012	1527	104560	167123
ZEOS ODBC_W MSSQL2012	1206	86673	156372
ZEOS ODBC_A MSSQL2012	1231	94853	152821
ZEOS OleDB MSSQL2012	1796	132682	289972
FireDAC MSSQL2012	1664	99259	166450
ZEOS Firebird	24598	92079	132467
Oracle	1825	80741	183358
ZEOS Oracle	2341	74806	159367
FireDAC Oracle	1620	54693	71397
UniDAC Oracle	764	29977	35569
ZEOS PostgreSQL	2030	72621	158212
FireDAC PostgreSQL	451	32386	42686
UniDAC PostgreSQL	242	61615	104620
ZEOS MySQL	2464	45702	61141
FireDAC MySQL	1455	63178	69454
UniDAC MySQL	1692	91175	127945

Michal

Offline

#3 2018-03-28 14:47:08

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: ZEOsLib 7.2 ,7.3

Thanks for the feedback and testing!

Please check https://synopse.info/fossil/info/5b1b8a1099

Offline

#4 2018-04-03 10:38:03

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: ZEOsLib 7.2 ,7.3

ZEOS 7.3 svn 4465
Running tests using Synopse mORMot framework 1.18.4411, compiled with Delphi 10 Seattle-Win32, against SQLite 3.22.0, at 2018-04-03 11:37:39.

Insertion speed
 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	61	3370	33507	47418
SQLite3 (file off)	128	6328	57489	101936
SQLite3 (file off exc)	16420	167807	71413	207520
SQLite3 (mem)	50374	224850	73271	221356
ZEOS SQlite3	37	3231	46221	62536
FireDAC SQlite3	11913	17354	36568	120857
UniDAC SQlite3	34	1904	22155	33330
ODBC MSSQL2012	1117	13622	1791	16296
UniDAC MSSQL2012	1424	17278	1664	17343
ZEOS ODBC_W MSSQL2012	1458	32818	2116	32665
ZEOS ODBC_A MSSQL2012	1464	30677	2230	32463
ZEOS OleDB MSSQL2012	1735	6600	2312	37281
FireDAC MSSQL2012	1483	5855	2200	31220
Oracle	841	65858	1608	64500
ZEOS Oracle	848	50452	1774	54915
FireDAC Oracle	834	45772	1558	44162
UniDAC Oracle	776	3952	1515	4026
ZEOS PostgreSQL	969	14150	2149	18770
FireDAC PostgreSQL	956	15265	1676	15504
UniDAC PostgreSQL	552	7915	746	10081
ZEOS MySQL	1824	14933	2350	20445
FireDAC MySQL	1613	21320	1672	22484
UniDAC MySQL	2002	16905	2374	15402





Read speed
 	By one	All Virtual	All Direct
SQLite3 (file full)	6709	437139	421159
SQLite3 (file off)	6965	427899	439135
SQLite3 (file off exc)	75604	430774	447107
SQLite3 (mem)	75755	429295	447587
ZEOS SQlite3	44619	198106	350926
FireDAC SQlite3	5608	96303	128806
UniDAC SQlite3	1508	78416	104644
ODBC MSSQL2012	2235	87136	204432
UniDAC MSSQL2012	1569	109603	172461
ZEOS ODBC_W MSSQL2012	1202	90273	150172
ZEOS ODBC_A MSSQL2012	1097	78722	149808
ZEOS OleDB MSSQL2012	1950	119537	293014
FireDAC MSSQL2012	1720	96676	166422
Oracle	1898	101097	184223
ZEOS Oracle	2270	73552	158002
FireDAC Oracle	1631	66096	68718
UniDAC Oracle	860	34711	34752
ZEOS PostgreSQL	1756	81865	155303
FireDAC PostgreSQL	449	32332	40952
UniDAC PostgreSQL	240	59152	103730
ZEOS MySQL	2123	100178	202864
FireDAC MySQL	1525	76673	112412
UniDAC MySQL	1672	84537	137991

Michal

Offline

#5 2018-04-03 13:21:07

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: ZEOsLib 7.2 ,7.3

@miab3
Note that I've updated this morning the Sqlite3 engine to latest 3.23.0 revision, so perhaps upgrading your mORMOt version may be worth it.

Offline

#6 2018-05-17 05:37:57

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: ZEOsLib 7.2 ,7.3

I made a small emergency fix to SynDBZeos - see pull request.
Without this fix queries what cannot be parametrized ( like "comment on table myTable is 'non-latin-chars-here' ") and contains a non-latin chars failed under FPC with message "Invalid byte sequence for encoding "UTF8""
Merged to both git and fossil repos

Offline

#7 2018-05-17 06:35:41

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: ZEOsLib 7.2 ,7.3

@mpv,

this is correct, i'd overlook this! It was my pull-request arnaud did apply. So I'm sorry for this inconsistency..

Offline

#8 2018-05-18 10:35:57

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: ZEOsLib 7.2 ,7.3

@EgonHugeist - not a problem. Case is very rare - in most case we bind strings as a parameters. But I add it to my CI, so now it will be checked automatically
Also I switch my products to Zeos7.2  and made a small pull request to SynDBZeos to support binding of Int64/String arrays for select statement for Zeos7.2

Offline

#9 2018-06-06 08:08:06

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: ZEOsLib 7.2 ,7.3

@EgonHugeist - during migation to Zeos72 we found a problem in TSQLDBZEOSConnectionProperties.CreateWithZURL.
Function has a side effect - it create a DB connection to obtain a DB metadata. It's broke my existed projects because on the moment Properties are created (usually during server startup)
1) actual database may not exists (multitenancy server what create DB's in runtime)
2) in case of several connection props inside one process some of them may be not in use (or used rarely), so creating a DB connection during server startup is overhead

I made a small emergency fix (I need to fix problem very quickly) by adding a condition define (we use ZEOS only for postgres, for other DB we use SynDB[Oracle|OleDB|Sylite3|ODBC] and this classes do not create DB connection during Properties creation, so I fount error on pre-production stage) - see [583aff0f] but IMHO this side effect should be removed.

Last edited by mpv (2018-06-06 08:09:15)

Offline

#10 2018-06-10 11:48:15

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: ZEOsLib 7.2 ,7.3

ZEOS 7.3 svn 4643
http://zeoslib.sourceforge.net/viewtopi … 987#p97987

Running tests using Synopse mORMot framework 1.18.4610, compiled with Delphi 10 Seattle 32 bit, against SQLite 3.24.0, at 2018-06-10 13:31:12.

Insertion speed
 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	112	1842	39944	67732
SQLite3 (file off)	164	11349	62080	207874
SQLite3 (file off exc)	16234	181389	70906	226480
SQLite3 (mem)	59569	234082	73827	237000
ZEOS SQlite3	80	1910	50793	68846
FireDAC SQlite3	12573	16086	32113	119970
UniDAC SQlite3	82	1548	22369	37545
ODBC MSSQL2012	1270	14121	2106	14586
UniDAC MSSQL2012	1367	15787	1684	19553
ZEOS ODBC_W MSSQL2012	1739	32914	2146	28584
ZEOS ODBC_A MSSQL2012	1628	34496	2330	34166
ZEOS OleDB MSSQL2012	1986	6131	2468	39348
FireDAC MSSQL2012	1462	5923	2020	30032
ZEOS Firebird	8633	24740	23716	25506
FireDAC Firebird	3676	43510	20781	43813
Oracle	846	65057	1538	64364
ZEOS Oracle	870	47642	1585	58441
FireDAC Oracle	863	44969	1610	43588
UniDAC Oracle	503	1325	1230	4094
ZEOS PostgreSQL	1055	40301	2156	35457
FireDAC PostgreSQL	996	12704	1944	15085
UniDAC PostgreSQL	560	7600	783	11266
ZEOS MySQL	7814	55018	6098	62825
FireDAC MySQL	5150	36283	5409	36936
UniDAC MySQL	4035	24121	8349	23383


Read speed
 	By one	All Virtual	All Direct
SQLite3 (file full)	6990	456704	466070
SQLite3 (file off)	7039	450612	459389
SQLite3 (file off exc)	79913	457163	470898
SQLite3 (mem)	80684	464770	463735
ZEOS SQlite3	62151	200360	371305
FireDAC SQlite3	5747	91197	114955
UniDAC SQlite3	1479	76755	104501
ODBC MSSQL2012	2289	35955	216150
UniDAC MSSQL2012	1575	107148	174477
ZEOS ODBC_W MSSQL2012	1225	90277	152928
ZEOS ODBC_A MSSQL2012	1152	73007	146036
ZEOS OleDB MSSQL2012	2009	118945	282055
FireDAC MSSQL2012	1652	99072	165114
ZEOS Firebird	26825	90881	134372
FireDAC Firebird	2060	61123	77987
Oracle	1837	84582	184924
ZEOS Oracle	2214	74515	160529
FireDAC Oracle	1495	52421	70614
UniDAC Oracle	752	31152	36467
ZEOS PostgreSQL	2071	75557	165267
FireDAC PostgreSQL	454	31863	42082
UniDAC PostgreSQL	251	57759	102671
ZEOS MySQL	8944	163110	282981
FireDAC MySQL	2256	82572	116306
UniDAC MySQL	3955	115799	185521
Running tests using Synopse mORMot framework 1.18.4610, compiled with Delphi 10 Seattle 64 bit, against SQLite 3.22.0, at 2018-06-10 13:38:55.

Insertion speed
 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	122	5926	57986	55170
SQLite3 (file off)	165	9402	55420	141687
SQLite3 (file off exc)	16696	174282	75795	203981
SQLite3 (mem)	54087	220167	75948	218159
ZEOS SQlite3	61	2636	47750	136444
FireDAC SQlite3	12989	20403	37964	133832
UniDAC SQlite3	70	1038	21024	31790
ODBC MSSQL2012	1149	15229	2157	15010
UniDAC MSSQL2012	1293	19537	1886	20306
ZEOS ODBC_W MSSQL2012	1477	32151	2346	28993
ZEOS ODBC_A MSSQL2012	1631	30738	2278	30511
ZEOS OleDB MSSQL2012	1726	5618	2304	38873
FireDAC MSSQL2012	1474	6069	2320	31642
ZEOS Oracle	880	57740	1750	59312
FireDAC Oracle	868	41922	1446	44314
UniDAC Oracle	779	4260	1672	4411
ZEOS PostgreSQL	1050	36631	1823	38135
FireDAC PostgreSQL	972	13699	1850	14051
UniDAC PostgreSQL	560	8252	725	11011
ZEOS MySQL	2085	30562	2475	31991
FireDAC MySQL	1667	30619	1684	27841
UniDAC MySQL	2326	17529	2140	17274


Read speed
 	By one	All Virtual	All Direct
SQLite3 (file full)	6714	472277	478423
SQLite3 (file off)	6964	497215	499650
SQLite3 (file off exc)	78571	490918	499850
SQLite3 (mem)	77941	506688	509424
ZEOS SQlite3	65279	281119	435502
FireDAC SQlite3	6065	115928	147789
UniDAC SQlite3	1514	102973	131385
ODBC MSSQL2012	2142	84821	218150
UniDAC MSSQL2012	1539	136963	221503
ZEOS ODBC_W MSSQL2012	1121	139477	157292
ZEOS ODBC_A MSSQL2012	1230	89306	151574
ZEOS OleDB MSSQL2012	1790	120102	171444
FireDAC MSSQL2012	1656	106828	164755
ZEOS Oracle	2018	84770	139528
FireDAC Oracle	1630	51432	65121
UniDAC Oracle	812	35760	36196
ZEOS PostgreSQL	2095	70780	149633
FireDAC PostgreSQL	493	30115	39552
UniDAC PostgreSQL	257	73584	123548
ZEOS MySQL	2461	91377	190454
FireDAC MySQL	1355	71881	71479
UniDAC MySQL	1737	100100	152611

Michal

Offline

#11 2018-06-10 18:49:12

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: ZEOsLib 7.2 ,7.3

With Michael(EgonHugeist) modification to SynDBZeos.pas, ZEOS 7.3 svn 4643 should be faster than FireDAC in the Insertion test, too.

Insertion speed
...
ZEOS Firebird        8505    71649    23334    71289   
FireDAC Firebird    3441    41948    20873    42585
...

Michal

Offline

Board footer

Powered by FluxBB