e.g.
OleDB:
'DRIVER={SQL Server Native Client 11.0};Server=SERVER\SQLEXPRESS;Database=master;Uid=user;Pwd=password;MARS_Connection=yes'
ODBC:
'DRIVER={ODBC Driver 13 for SQL Server};Server=SERVER\SQLEXPRESS;Database=master;Uid=user;Pwd=password;MARS_Connection=yes'
Thank you. @miab3.
My backend MSSQL is MSSQL2016. I tried the above strings, and It seems not work.
Then I compiled the whole ZEOS package V7.3-alpha. By using the TZConnection, TZQuery components, I found that the value for parameter Url of function TZDriverManager.GetConnectionWithParams(const Url: string; Info: TStrings): IZConnection;
is a quite long string: zdbc:OleDB://127.0.0.1:1433/Provider=SQLOLEDB.1'#9'Password=XXXX'#9'Persist Security Info=True'#9'User ID=YYYY'#9'Initial Catalog=ZZZZ'#9'Data Source=127.0.0.1?username=UUUU;password=PPPP'
So I give the following string in UI of SynDBExplore as: OleDB://127.0.0.1:1433/Provider=SQLOLEDB.1, and found that when SynDBZeos call
GetConnectionWithParams, the Url is just about the same.
zdbc:OleDB://127.0.0.1:1433/Provider=SQLOLEDB.1?username=UUUU;password=PPPP;controls_cp=CP_UTF8
The TZConnection, TZQuery works in Win64. But when combined zeos with mORMot, the Zeos raised a lot of exceptions such as EZSQLException OLEDB Error 80040E14 and Can't found the datatype CLOB. I use RawUTF8 datatype for UTF-8 Strings.
What is wrong for my usage of SynDBZeos and Zeos?
]]>I didn't expect any performance change, but we may have introduced some regressions.
]]>Insertion speed
Direct Batch Trans Batch Trans
SQLite3 (file full) 114 4037 25570 72882
SQLite3 (file off) 138 9021 53396 206927
SQLite3 (file off exc) 21507 189559 50923 139314
SQLite3 (mem) 59344 232959 74805 154841
TObjectList (static) 154635 348967 102633 265181
TObjectList (virtual) 121621 325097 152234 326733
SQLite3 (ext full) 58 2233 55208 31312
SQLite3 (ext off) 118 3299 58428 194696
SQLite3 (ext off exc) 31444 178393 61732 201873
SQLite3 (ext mem) 59670 221650 79743 129829
ZEOS SQlite3 60 1139 25151 37976
FireDAC SQlite3 9570 15477 36853 85142
UniDAC SQlite3 58 836 18833 26196
ODBC MSSQL2012 1547 11531 2083 15905
UniDAC MSSQL2012 1517 16792 1796 20062
ZEOS ODBC_W MSSQL2012 1522 32306 2247 32976
ZEOS ODBC_A MSSQL2012 1733 40470 2190 33862
ZEOS OleDB MSSQL2012 1639 7212 2315 41795
FireDAC MSSQL2012 1419 6633 2342 35987
ZEOS Firebird 417 8167 5719 8150
ZEOS PostgreSQL 918 16483 2139 17043
FireDAC PostgreSQL 802 15080 2001 13429
UniDAC PostgreSQL 490 6564 803 8997
ZEOS MySQL 2177 20076 2334 26144
FireDAC MySQL 1563 27880 1821 31115
UniDAC MySQL 2133 16483 2301 17258
Read speed
By one All Virtual All Direct
SQLite3 (file full) 9560 502664 489332
SQLite3 (file off) 9666 502260 499950
SQLite3 (file off exc) 76029 470322 499950
SQLite3 (mem) 59154 488472 504999
TObjectList (static) 109880 739535 698031
TObjectList (virtual) 153214 400128 696669
SQLite3 (ext full) 70790 252105 494657
SQLite3 (ext off) 92388 263601 491593
SQLite3 (ext off exc) 77553 223493 429959
SQLite3 (ext mem) 91910 237993 470987
ZEOS SQlite3 41619 203848 342371
FireDAC SQlite3 5783 98656 139844
UniDAC SQlite3 1689 79473 106796
ODBC MSSQL2012 2309 52682 199060
UniDAC MSSQL2012 1612 106042 168163
ZEOS ODBC_W MSSQL2012 1157 77278 153125
ZEOS ODBC_A MSSQL2012 1190 93353 150543
ZEOS OleDB MSSQL2012 2324 155768 291086
FireDAC MSSQL2012 1639 111423 159815
ZEOS Firebird 22574 97019 132044
ZEOS PostgreSQL 1941 81329 126553
FireDAC PostgreSQL 416 34732 45187
UniDAC PostgreSQL 196 56491 99111
ZEOS MySQL 2279 77811 176006
FireDAC MySQL 1335 46214 70106
UniDAC MySQL 1745 89455 125398
Michal
]]>Thanks in advance.
]]>Running tests using Synopse mORMot framework 1.18.2260, compiled with Delphi 10 Seattle, against SQLite 3.10.0, at 2016-01-15 15:16:47.
Insertion speed
Direct Batch Trans Batch Trans
SQLite3 (file full) 68 2165 54346 117774
SQLite3 (file off) 1213 68362 75790 191109
SQLite3 (file off exc) 24027 192108 75024 202971
SQLite3 (mem) 65624 225968 77443 225509
TObjectList (static) 178986 365176 176435 353756
TObjectList (virtual) 169825 343430 170508 341064
SQLite3 (ext full) 83 2944 62902 147110
SQLite3 (ext off) 1301 33429 86846 185542
SQLite3 (ext off exc) 33104 179623 85576 192663
SQLite3 (ext mem) 77218 208255 87423 210190
ZEOS SQlite3 154 4027 43263 68121
FireDAC SQlite3 17998 31924 38710 118962
ODBC MSSQL2012 1115 11842 2371 21344
ZEOS ODBC_W MSSQL2012 1177 31570 2404 36979
ZEOS ODBC_A MSSQL2012 1282 40021 2446 33856
ZEOS OleDB MSSQL2012 1378 3184 2600 42358
FireDAC MSSQL2012 1206 2271 2555 30365
ZEOS Firebird 423 5129 3848 8803
FireDAC Firebird 284 6497 6293 8595
Oracle 677 19427 1624 51729
ZEOS Oracle 528 43027 2006 40446
FireDAC Oracle 801 33016 1737 28510
ZEOS PostgreSQL 505 11355 2221 19175
FireDAC PostgreSQL 1001 15790 1873 16676
ZEOS MySQL 2296 28764 2427 30099
FireDAC MySQL 1612 30686 1873 39025
Read speed
By one All Virtual All Direct
SQLite3 (file full) 91164 506277 523231
SQLite3 (file off) 90778 525430 517812
SQLite3 (file off exc) 91549 531011 515676
SQLite3 (mem) 91426 528932 518134
TObjectList (static) 166588 690035 699300
TObjectList (virtual) 165251 391849 661900
SQLite3 (ext full) 101586 263393 505458
SQLite3 (ext off) 99860 262453 507305
SQLite3 (ext off exc) 101144 258304 506431
SQLite3 (ext mem) 99316 256160 502563
ZEOS SQlite3 52975 202420 348237
FireDAC SQlite3 5385 98767 131669
ODBC MSSQL2012 2391 95995 219211
ZEOS ODBC_W MSSQL2012 1244 102578 164414
ZEOS ODBC_A MSSQL2012 1249 100502 154521
ZEOS OleDB MSSQL2012 2350 159841 309501
FireDAC MSSQL2012 1670 109994 169508
ZEOS Firebird 24432 97578 135725
FireDAC Firebird 444 52564 78346
Oracle 2011 96564 168055
ZEOS Oracle 2380 86110 134890
FireDAC Oracle 1527 57287 71551
ZEOS PostgreSQL 2287 82632 149866
FireDAC PostgreSQL 510 36242 42399
ZEOS MySQL 2555 113247 232536
FireDAC MySQL 1425 79583 110734
ZEOS 7.3 svn3731 Delphi 10 Seattle Win32
Michal
]]>Running tests using Synopse mORMot framework 1.18.2257, compiled with Delphi 10 Seattle, against SQLite 3.10.0, at 2016-01-14 00:28:48.
Insertion speed
Direct Batch Trans Batch Trans
SQLite3 (file full) 73 2224 48436 79994
ZEOS SQlite3 109 2313 32849 63760
FireDAC SQlite3 18414 32522 38993 120473
ODBC MSSQL2012 813 8032 2396 21961
ZEOS ODBC_W MSSQL2012 1322 37504 2474 39177
ZEOS ODBC_A MSSQL2012 1089 34769 2428 37281
ZEOS OleDB MSSQL2012 1188 2519 2522 39679
FireDAC MSSQL2012 1232 2346 2408 30788
ZEOS Firebird 446 5392 7636 12505
FireDAC Firebird 265 9980 6602 10609
Oracle 842 57359 1682 47757
ZEOS Oracle 787 48471 2323 50581
FireDAC Oracle 842 49012 2136 46999
ZEOS PostgreSQL 479 10864 2288 19950
FireDAC PostgreSQL 992 16699 1681 16859
ZEOS MySQL 2350 28706 2466 30955
FireDAC MySQL 1969 38152 2068 38869
Read speed
By one All Virtual All Direct
SQLite3 (file full) 88062 482299 493924
ZEOS SQlite3 57301 196047 450491
FireDAC SQlite3 5493 94957 136032
ODBC MSSQL2012 2524 96694 223015
ZEOS ODBC_W MSSQL2012 1272 97633 174137
ZEOS ODBC_A MSSQL2012 1264 101317 166944
ZEOS OleDB MSSQL2012 2355 125571 372134
FireDAC MSSQL2012 1676 104552 166750
ZEOS Firebird 25949 92407 143135
FireDAC Firebird 643 59191 77674
Oracle 2243 103201 184175
ZEOS Oracle 2467 92033 171697
FireDAC Oracle 1498 58566 75607
ZEOS PostgreSQL 2618 84711 163158
FireDAC PostgreSQL 469 33022 45944
ZEOS MySQL 2448 127349 283575
FireDAC MySQL 1451 80362 111582
ZEOS 7.3 svn3731 Delphi 10 Seattle Win32
Michal
]]>On my side the code compiles and runs fine with my whole compiler battery. No issues to see, just better performance results (:
]]> Synopse mORMot Framework Automated tests
------------------------------------------
...
- Ini files: 7,004 assertions passed 20.60ms
- UTF8: 176,152 assertions passed 516.84ms
- Iso 8601 date and time: 48,017 assertions passed 5.02ms
...
Using mORMot 1.18.2257 FTS3
Running on Windows 7 64bit SP1 (6.1.7601) with code page 1252
TSQLite3LibraryStatic 3.10.0 with internal MM
Generated with: Delphi 10 Seattle compiler
Time elapsed for all tests: 46.78s
Tests performed at 13/01/2016 21:30:01
Total assertions failed for all test suits: 0 / 23,709,103
! All tests passed successfully.
What is your code page?
]]>TestSQL3.exe:
! - UTF8: 14,000 / 175,151 FAILED 1.12s
Delphi 10 Seattle Win32 mORMot 1.18.2257
Michal
]]>Enable USE_SYNCOMMONS
mORMot: [dcc32 Fatal Error] SynDBZEOS.pas(138): F2051 Unit ZDbcCache was compiled with a different version of SynCommons.TTextWriter.AddNoJSONEscapeUTF8
ZEOS: "ZDbc contains implicit unit(s) SynCommons, SynLZ"
It gets a cross between a libraries.
It's probably not a good solution?
Delphi 10 Seattle Win32; mORMot 1.18.2257; ZEOS 7.3 svn3731
Michal
]]>I added to the previous ZEOS 7.3 OleDB (at the moment without $USE_SYNCOMMONS)
Michal
]]>since Zeos7.3 i made a little code join with/for the synopse-project.
There is a new define $USE_SYNCOMMONS available which than directly uses the SynCommons.TJSONWriter.
You can either add it to your compiler options or just uncommend the define in Zeos.inc.
This define than finally brings actual best performance for ZDBC + mORMot. The SynCommons.TJSONWriter is pretty nice optimized, because of this i decided to skip a Zeos own approch and just used the one of mORMot. Now Zeos is able to return JSONs. So both projects do benefit from the latest patches.
As you've noticed Michal (miab3) also did test the two new Zeos-ODBC Drivers. I wrote them to make the leap according MSSQL + Zeos and NON Windows plattforms and becouse i wanted to know what brings best MSSQL access rates ... OleDB is the winner in this domain.
Numbers are talking, guys ... from !my! POV there are no performance (are there otheres for mORMot? ) advantages using FireDac or UniDac as well. Remembering the first results of 7.1 series Zeos access rates where between FireDac/UniDac. Now most code is optimized and completely refactored.
Arnaud and me worked hand in hand to get best performance running for his framework and Zeos it's selves too of course!
Note i'm not ready with the complete refactoring. Just most parts of ZCore up to ZDbc have been reworked from ground up. Actually my Zeos coding time is rare comparing my previous activities but 7.2-stable will come soon. (For those who do not trust in the beta attribute ... It's up to Mark,Michal,Jan and me just to tag 7.2 as stable candidate! We'll do that if latest fix is older than 3 weeks.
Missing points:
I've noticed there are adtional options in mORMot. Such like limit the lob sizes or just ignore them.
As a work around i propose to use the JSONWriter.TSQLFieldBits/Fields for composing the json-contents and just ignore those fields. Any other ideas?
@miab3
thanks for attaching the results! I was out of time to setup the whole database batery!
Sadly i see you miss a Zeos OleDB-driver results, or am i wrong? Could you test the OleDB driver + MSSQL too?
In addition could you enable the $USE_SYNCOMMONS define please?
@AB
i was waiting a while before anouncing the define or new drivers. Some final regressions have been fixed. (And beause of lack of time i've tested each protocol @all)
===> THANK's for updating your documantations! This is a great advertinsing for Zeos.
Cheers, Michael
]]>