#1 2013-02-08 22:27:11

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

SynDBDataset - using SynDB or mORMot ORM with any database backend

A dedicated "SynDBDataset" sub-folder has been created in the repository, to contain all SynDBDataset-based database provider.
See http://synopse.info/forum/viewtopic.php?pid=6663#p6663

Huge step for mORMot database connectivity!

Offline

#2 2013-02-11 17:23:36

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

I've implemented SynDBZEOS direct access - directly call the ZDBC abstract layer of ZEOS, by-passing the TDataset components overhead.

Tested with tested with Oracle 11g:

Insertion speed

            Oracle    ODBC Oracle    ZEOS Oracle
Direct      484    558    363
Batch     63259    580    367
Trans           983          1096    855
Batch  Trans    61312    1098    876

chart?chtt=Insertion+speed+%28rows%2Fsecond%29&chxl=1:|ZEOS+Oracle|ODBC+Oracle|Oracle&chxt=x,y&chbh=a&chs=600x300&cht=bhg&chco=3D7930,3D8930,309F30,6070F0,5070E0,40C355,65D055,80C1A2,F05050,F0A280&chxr=0,0,63259&chds=0,63259,0,63259,0,63259&chd=t:484,558,363|63259,580,367|983,1096,855|61312,1098,876&chdl=Direct|Batch|Trans|Batch+Trans

Read speed

    Oracle    ODBC      Oracle    ZEOS    Oracle
By one    1505             1152           439
All Virtual    74698    34644    40940
All Direct    85023    39973    48713

chart?chtt=Read+speed+%28rows%2Fsecond%29&chxl=1:|ZEOS+Oracle|ODBC+Oracle|Oracle&chxt=x,y&chbh=a&chs=600x300&cht=bhg&chco=3D7930,3D8930,309F30,6070F0,5070E0,40C355,65D055,80C1A2,F05050,F0A280&chxr=0,0,85023&chds=0,85023,0,85023,0,85023&chd=t:1505,1152,439|74698,34644,40940|85023,39973,48713&chdl=By+one|All+Virtual|All+Direct

Our direct Oracle access layer (SynDBORacle) is the fastest, but ZEOS layer is in fact similar to direct ODBC connection, or a bit faster.

Offline

#3 2013-02-15 15:31:43

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

Hi..

Thanks for the speed tests you made! Did you test AnyDAC too?

Which tests did you use for these Results? Are You sure you've created IZPreparedStatments? Did you test large binaries too? e.g Option "CHUNK_SIZE"..

Michael, ZeosDevTeam

Btw. Zeos7.1 allready uses Prepared Statments for SQLite. For Oracle it's already implemented. Zeos7.1.x stable will be available in the first Quarter of the year.

Last edited by EgonHugeist (2013-02-16 08:15:43)

Offline

#4 2013-02-17 06:18:25

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

AnyDAC is not intefaced, nor tested yet.

Direct access to the ZDBC layer does make sense.
Depending on the ZDBC driver, performance is more or less optimized.
I had to fight a bit with Unicode support, but sounds to be working by now.

I did not interfaces IZPreparedStatements by now.
It was a rough wrapping of SynDB abstract classes to ZDBC.
Lack of updated documentation of Zeos components did not help (but to be honest, the UniDAC documentation is not accurate either).
You can take a look at this unit: http://synopse.info/fossil/finfo?name=SynDBZEOS.pas

So all those benchmarks are NOT a speed test comparison of the libraries, but reflects the integration within our framework.
We did not use the fastest way of use for each library, just tried to make them work as best as possible.

See also this blog article, for a wider speed comparison.

I started a fork of ZEOS some years ago, but it was too huge for me, so I wrote my own direct layers for Oracle, ODBC, OleDB and Sqlite3, with less features, but also less overhead.
You have made great work with the 7.* series! It is a pleasure to include ZEOS support in our Open Source project.
Any feedback is welcome, especially from a ZEOS expert like you!

Offline

#5 2013-02-17 09:33:22

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

Hi Arnaud,

nice to clearify the purpose of the diagramms again (for me personally). I had a look to your SynDBZEOS.pas and i'm pretty happy to see, you understand how to work with our Dbc-Layer!!!

If you've Zeos related questions, i'll try to support as good as i can! Don't hesitate to write me on my private eMail (; .
I had a look to your SynOLEDB.pas implementation because we're looking for an ADO replacement to get OLE support for FPC running too. Well i think we need a loads of additional {$IFDEF ..}'s. One user did point me to you implementation http://zeos.firmos.at/viewtopic.php?p=16948#16948 .

May i ask you if we can start an joint-venture in this domain? If ZeosDevMember finds the time of course..

OpenSource rocks! Keep it up!

Offline

#6 2013-02-17 13:53:54

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

Here is what I've added today to our documentation (SAD pdf):

ZeosLib, aka Zeos, is a Open Source library that provide native access to many database systems, developed for Delphi, Kylix and Lazarus + Freepascal. It is fully object-oriented and with a totally modular design. It access the databases by wrapping their native client libraries, and make them accessible via its abstract layer, named ZDBC. Originally, ZDBC was a port of JDBC 2.0 (Java Database Connectivity API) to Object Pascal. Since that time the API was slightly extended but the main ideas remain unchanged, so official JDBC 2.0 specification is the main entry point to the ZDBC API.

Since revision 1.18 of the framework, we included direct integration of ZeosLib into mORMot persistence layer, with direct acces to the ZDBC layer. That is, it does not references DB.pas unit, and does not need to marshall all incoming data into a TDataSet compatible layout.

Such direct access, by-passing the VCL DB additional layer, is very close to our SynDB design. As such, ZeosLib is a first class citizen library for mORMot.

Working with ZDBC layer was not difficult, since it is pretty the same as JDBC.
I had only issues with Unicode support - and did only fully test it for Delphi 7-2007 yet (perhaps I missed something about parameters to use with Delphi 2009+).
But our SynDB unit and all mORMot is already Unicode, since it uses UTF-8 everywhere, in fact.

We support only Delphi by now. We prepared for FPC but it is not finished yet. There is a lot of low-level RTTI stuff in mORMot (not SynDB.pas by itself) which is not compatible with FPC.
I did not notice the {{ issue up to now with FPC. I could easily fix it and include the fix to mORMot.
I'm not able to understand the point of this forum post: is it to add a SynDB layer to Zeos (i.e. the reverse way?).
You have to note that ADO is base on OleDB, but is not the same. Our SynOleDB unit only implements the OleDB part, not the ADO overlay.

But you are right: OleDB is deprecated.
Microsoft ask developers to switch to ODBC.
See http://blog.synopse.info/post/2012/02/2 … enjoy-ODBC!
So I guess an ODBC layer could make sense.

I suppose adding ODBC support to ZDBC is not difficult.
You can use our units as start, without any problem.
mORMot is published under a tri-license scheme, i.e. GPL / LGPL / MPL.
So you can integrate some part of our code within Zeos LGPL project. Just do not forget to acknowledge the source.

We used a tri-license and not only a LGPL, due to some restrictions issues.
As far as I understood, the LGPL license requires you to release all the source code of any library you link statically to.
I do not understand how it is compatible with ZeosLib to be used within closed source proprietary projects, statically linked with the executable.

By email wrote:

Well i've some friendly critics accordingly the Unicode-Support you've made and i hope you're receptivily for my hints? (:

Your current implementation leads to pain in several environment settings i know about. Theire are providers like FireBird which have some very spezial CharacterSets like 'NONE' or MySQL 'binary'. Your current implenation starts allways from the premisse you open an UTF8 compatible connection. Is this correctly for OLE too? Also does the SC 'NONE' absolutely no client-characterset conversations. You've to submit the data in the collumn related collation or write data 'as is'. Very tricky. I'm not sure about, but i guess Zeos currently is the only access component which is doing this job correctly... All others do simply ignore such cases.

Well i could write you endless suggestions but i propose you check my implementation on ZCompatibility.pas and ZEncoding.pas? Theire you can find a good examle how to avoid comming encoding issues on your side.You'll need also some more informations about the client-charactersets 'em selves, which you can find in each ZPlainxxx.pas files.

You are right: SynDBZeos is limited about connection provider charset, by now.
SynDB/mORMot principle is that you use UTF-8 in the APIs, and let the DB provider to the encoding for you.
In fact, UTF-8 is supported outside the SynDB layer, but any charset should be able to be used within the connection.

I set the UTF-8 encoding within SynDBZeos.pas up to now, because I wanted it to work with tables created by our ORM, which expects Unicode/UTF-8 encoding.
It is a first draft version. Feedback is welcome, and I won't invest much time if noone is using it!
With the current implementation of ZDBC, I was not able to find out the current charset encoding of the connection, in a standard way - help is needed here.
And in some places, the returned AnsiString is of the same charset than the connection - which could be diverse, by definition.
The implementation of ZCompatibility and ZEncoding just sounds over-complicated to me. You can find a light and very fast charset support in our SynDBOracle direct layer, for instance. ZDBC just does not work with pre Delphi 2009 versions of Delphi, if the database charset does not match the current VCL charset e.g. with Sqlite3 or Firebird UTF-8 support: this is the reason why I had to use WideString in SynDBZeos for Firebird and Sqlite3, which slows down everything. For Oracle, when used on a database matching the VCL AnsiString charset, it worked.
As far as I understood Zeos string handling, it does make a lot of unnecessary charset conversion or string allocation. Just make a test about our SynDBOracle layer speed against the ZDBC Oracle provider, and you will find out what I mean. SynOleDB or SynDBODBC handles perfect Unicode process, whatever the database charset is, since by definition OleDB connection is UTF-16 (like all other WinAPI), and ODBC provides *W() Unicode APIs, used by SynDBODBC.

My question is how the current connection charset could be retrieved, and row unconverted data accessed by the ZDBC providers? For what I've seen, the internal buffer is not available, and AnsiString process seems broken (at least for some ZDBC drivers I've read). So even if I've got the connection charset, client is not able to use it as expected, since it will be converted either to the current AnsiString code page (for AnsiString method), or to WideString, but not using the encoding, for some drivers.

By the way, I have at least two main issues/limitations with ZEOS:
- Firebird embedded library can not be in a sub-folder - I add to change the code and add a prev := GetDirectory / ChangeDirectory(new) / LoadLibrary / ChangeDirectory(prev) to let it work as expected;
- SQLite3 layer identifies an INTEGER column as Delphi integer, which is wrong, since in SQlite3, an INTEGER column is an Int64 - and if you use BIGINT for an ID field, it won't be shared with the internal RowID.

Offline

#7 2013-02-18 13:39:36

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

First of all let's start from the premisse i've studied you souces in less than a half hour. I didn't know anything about synopse until one user proposed to use it as OleDB replacement + FPC.

I had only issues with Unicode support - and did only fully test it for Delphi 7-2007 yet (perhaps I missed something about parameters to use with Delphi 2009+).
But our SynDB unit and all mORMot is already Unicode, since it uses UTF-8 everywhere, in fact.

I don't know you current DataSet-implementations. But i would wonder if you get WideString and WideMemo-Fields running on D7?

We support only Delphi by now. We prepared for FPC but it is not finished yet. There is a lot of low-level RTTI stuff in mORMot (not SynDB.pas by itself) which is not compatible with FPC.
I did not notice the {{ issue up to now with FPC. I could easily fix it and include the fix to mORMot.
I'm not able to understand the point of this forum post: is it to add a SynDB layer to Zeos (i.e. the reverse way?).
You have to note that ADO is base on OleDB, but is not the same. Our SynOleDB unit only implements the OleDB part, not the ADO overlay.

But you are right: OleDB is deprecated.
Microsoft ask developers to switch to ODBC.
See http://blog.synopse.info/post/2012/02/2 … enjoy-ODBC!
So I guess an ODBC layer could make sense.

I suppose adding ODBC support to ZDBC is not difficult.
You can use our units as start, without any problem.
mORMot is published under a tri-license scheme, i.e. GPL / LGPL / MPL.
So you can integrate some part of our code within Zeos LGPL project. Just do not forget to acknowledge the source.

We used a tri-license and not only a LGPL, due to some restrictions issues.
As far as I understood, the LGPL license requires you to release all the source code of any library you link statically to.
I do not understand how it is compatible with ZeosLib to be used within closed source proprietary projects, statically linked with the executable.

Well an direct ODBC support seems the better choise for future works. The most OS's do support ODBC. Using third party souces isn't decided yet in the team. But thank you for allowing the usage in terms of your licence restriction.

You are right: SynDBZeos is limited about connection provider charset, by now.
SynDB/mORMot principle is that you use UTF-8 in the APIs, and let the DB provider to the encoding for you.

Well this works for 95% of all cases, except for some spezials like the CS 'NONE' etc. Create a FireBird DB with default Charset 'NONE', add a talbe with some spezial collations and you run into the first issues. A starting discussion and the solution: http://zeos.firmos.at/viewtopic.php?t=3 … &start=600

You can call it feature, if you want but IMHO is it a qustion of time for bug-reports.

In fact, UTF-8 is supported outside the SynDB layer, but any charset should be able to be used within the connection.

I set the UTF-8 encoding within SynDBZeos.pas up to now, because I wanted it to work with tables created by our ORM, which expects Unicode/UTF-8 encoding.
It is a first draft version. Feedback is welcome, and I won't invest much time if noone is using it!

Me too ((: Like i wrote i'll try to support you as good as i can.

With the current implementation of ZDBC, I was not able to find out the current charset encoding of the connection, in a standard way - help is needed here.

Well i wrote a DbcMetaData function GetCharacterSet(..): IZResultSet. But i had not the time to complete it for all protocols. Here our current implementation leaks if you really need it -> go on reading.

And in some places, the returned AnsiString is of the same charset than the connection - which could be diverse, by definition.

Fact. No conversations are needed except WideString/UnicodeString mapping.

The implementation of ZCompatibility and ZEncoding just sounds over-complicated to me.

No problem it was an hint how to unify the Delphi compilers with Ansi and Unicode-Support.

You can find a light and very fast charset support in our SynDBOracle direct layer, for instance.

Equals to my implementation (; Some CS aliases are missing (ZPlainOracle.pas)

ZDBC just does not work with pre Delphi 2009 versions of Delphi, if the database charset does not match the current VCL charset e.g. with Sqlite3 or Firebird UTF-8 support: this is the reason why I had to use WideString in SynDBZeos for Firebird and Sqlite3, which slows down everything. For Oracle, when used on a database matching the VCL AnsiString charset, it worked.

As far as I understood Zeos string handling, it does make a lot of unnecessary charset conversion or string allocation. Just make a test about our SynDBOracle layer speed against the ZDBC Oracle provider, and you will find out what I mean. SynOleDB or SynDBODBC handles perfect Unicode process, whatever the database charset is, since by definition OleDB connection is UTF-16 (like all other WinAPI), and ODBC provides *W() Unicode APIs, used by SynDBODBC.

My question is how the current connection charset could be retrieved, and row unconverted data accessed by the ZDBC providers? For what I've seen, the internal buffer is not available, and AnsiString process seems broken (at least for some ZDBC drivers I've read). So even if I've got the connection charset, client is not able to use it as expected, since it will be converted either to the current AnsiString code page (for AnsiString method), or to WideString, but not using the encoding, for some drivers.

Disagree. ((: D7 -> WideFields? You simply do the job twize. The Dbc-Layer uses an enum-type: TZControlsCodePage = (cCP_UTF16, cCP_UTF8, cGET_ACP)
The default for:
Ansi-Delphi is cGet_ACP
Unicode-Delphi is cCP_UTF16
FPC is cCP_UTF8.

Imagine the IZResultSet.GetString returns a String which type ever. AnsiString/UnicodeString/RawByteString(for FPC2.7.1 which is doing automated AnsiString(CP ??) conversations). 600 compiler-warnings have been gone. Convert all between Dbc and Plain-tier nowhere else.

Here i was starting from the premisse we should be able to encode the Strings in dependencies what the Controls do expect. Theire are controls like TNT which can use WideStrings or UTF-8 (with a little hack).
Add Param 'controls_cp=GET_ACP' or 'controls_cp=CP_UTF8' or 'controls_cp=CP_UTF16' to the TZURL.Properites and you have pure UTF8/Ansi/Unicode-Strings for the Ansi-Delphis or WideFields-Support. It doesn't matter which Client-Encoding Zeos does the job. Well what's faster: The Zeos is able to do ALL encodings or the Server does it? Allways think about the compiler we have and what must be done to keep the work of the users as minior as possible. Btw. Delphi calls the same function on casting AnsiString to Wide and vice versa which Zeos has allready done if you move everything up to WideStrings. Here i've the impression the same String is converted 4x until the Dataset retrieves him? Speed?

Hint for full overview: ZCompatibility.pas procedure TZCodePagedObject.SetConSettingsFromInfo(Info: TStrings);
This Enum, the current os CP and the Client-CP are the magic key. Zeos does everything for you. Another option is 'AutoEncodeStrings=ON' which simply checks the encoding end sends the Data like the Server does expect it, but slows donw the speed for updates. No exceptions like 'Invalid Byte Sequence for UTF8'... Start from the premisse a newbie is using Zeos. Everything is optional for newbies and pro's.

Zeos is able to open Latin1 connections (even if CP 1251 isn't you current), leave the String as is/convert to UTF8 or Wide..

By the way, I have at least two main issues/limitations with ZEOS:
- Firebird embedded library can not be in a sub-folder - I add to change the code and add a prev := GetDirectory / ChangeDirectory(new) / LoadLibrary / ChangeDirectory(prev) to let it work as expected;

TZURL.LibLocation := 'C:\blabla\MyLib.dll' doesn't work to load the libs named and located somewhere else?

- SQLite3 layer identifies an INTEGER column as Delphi integer, which is wrong, since in SQlite3, an INTEGER column is an Int64 - and if you use BIGINT for an ID field, it won't be shared with the internal RowID.

Thanks for the hint (: Will be done tomorrow.

Offline

#8 2013-02-18 14:34:55

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

EgonHugeist wrote:

I don't know you current DataSet-implementations. But i would wonder if you get WideString and WideMemo-Fields running on D7?

No it was not working e.g. with Sqlite3 provider, as far as I remember.

EgonHugeist wrote:

Well this works for 95% of all cases, except for some spezials like the CS 'NONE' etc. Create a FireBird DB with default Charset 'NONE', add a talbe with some spezial collations and you run into the first issues. A starting discussion and the solution: http://zeos.firmos.at/viewtopic.php?t=3 … &start=600

Within mORMot, we won't have a big problem with it, since we basically expect CRUD statements to be available, not using complex collation scheme at the DB layer.
I understand the ZeosLib point of view, which has to feature a wider range of abilities.

EgonHugeist wrote:

Fact. No conversations are needed except WideString/UnicodeString mapping.

But then, how do you convert the returned data into the current charset, if you do not know it? sad

EgonHugeist wrote:

Equals to my implementation (; Some CS aliases are missing (ZPlainOracle.pas)

Just check how many string allocation appear, and how SynCommons handle ASCII 7 chars, and I suspect you will find a difference.

EgonHugeist wrote:

Disagree. ((: D7 -> WideFields? You simply do the job twize. The Dbc-Layer uses an enum-type: TZControlsCodePage = (cCP_UTF16, cCP_UTF8, cGET_ACP)

I tried this property, but it was not working for me.
Is it part of ZDBC layer SynDBZeos.pas uses? Or is it at component level?
I'll check this twice.
Thanks for the input.

EgonHugeist wrote:

TZURL.LibLocation := 'C:\blabla\MyLib.dll' doesn't work to load the libs named and located somewhere else?

No, the sub libraries (e.g. icuuc30.dll) are reported as missing, if you do not change the current library to C:\Blabla
At least with latest stable 2.5.2 I downloaded from official web site.

Thanks a lot for the feedback!

Offline

#9 2013-02-18 17:08:14

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

I've just used fURL.Properties.Add('controls_cp=CP_UTF8') instead of WideString conversion for Delphi 7.
Code just sounds better than my previous version, since it should now work with any connection charset, just as expected.
If I use GetBinaryString(), it just does not work, since for SQlite3 it returns always ''.
So I used GetString().
If I'm right:
-  TZRowAccessor.GetString() returns the UTF-8 encoded text in Delphi < 2009;
-  TZRowAccessor.GetString() returns an UnicodeString converted from UTF-8 content for Delphi > 2009.
The Result := String(TempBlob.GetString); statement in line 1003 of ZDBCCache.pas sounds like an unneeded conversion to me, for Unicode versions of Delphi.
Or should I just not use "controls_cp=CP_UTF8" for Unicode version of Delphi?

See http://synopse.info/fossil/info/1141f74f08

OLD wrote:

Insertion speed

   Direct  Batch  Trans  Batch Trans
ZEOS SQlite3  372 374 362 467
ZEOS Firebird  1791 1870 6522 7213

Read speed

   By one  All Virtual  All Direct
ZEOS SQlite3 3486 81959 115856
ZEOS Firebird  2508 56771 71822

But, unfortunately, no noticeable speed increase... around 5-10% faster or slower, depending on SQLite3 or Firebird.

NEW wrote:

Insertion speed

   Direct  Batch  Trans  Batch Trans
ZEOS SQlite3  472  421  446  429
ZEOS Firebird  1809  1650  7368  7649

Read speed

   By one  All Virtual  All Direct
ZEOS SQlite3  3299  100124  120307
ZEOS Firebird  2677  61863  82527

Our direct classes are still much efficient (SQLite3 reading speed is e.g. 26527 435995 438519).

About transactions, I'm quite sure I've missed something, at least for SQlite3.
Or perhaps the IZConnection.SetAutoCommit(true) does not start a transaction for the SQLite3 provider, whereas it seems to create a transaction for Firebird (as above results may indicate).
Our direct Sqlite3 classes writing speed is 536 525 93687 113527. So here transaction does make a huge difference!

Any feedback is welcome, and thanks for the "controls_cp=CP_UTF8" trick!

Offline

#10 2013-02-18 18:27:01

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

ab wrote:

No, the sub libraries (e.g. icuuc30.dll) are reported as missing, if you do not change the current library to C:\Blabla
At least with latest stable 2.5.2 I downloaded from official web site.

Should we add the extrected path to the current OS path-settings? We've the same behavior on PostgreSQL. Propose a patch (;


ab wrote:

Our direct classes are still much efficient (SQLite3 reading speed is e.g. 26527 435995 438519).

About transactions, I'm quite sure I've missed something, at least for SQlite3.
Or perhaps the IZConnection.SetAutoCommit(true) does not start a transaction for the SQLite3 provider, whereas it seems to create a transaction for Firebird (as above results may indicate).
Our direct Sqlite3 classes writing speed is 536 525 93687 113527. So here transaction does make a huge difference!

Well i won't battle with with mORMot..

Uh yes, the Transactions do terrible slow down SQLite on insertion speed if AutoCommit = True. I can not find the thread but i made a behavior test with the PreparedStamtents + SQLite:
I hope i'm right 10.000 Inserts
Autocommit = true 10000 inserts 2min?
Autocommit = False + StartTransaction 4 secs
Autocommit = False + StartTransaction + PreparedStatment (7.1) 2 secs

I was also surpriced about the huge difference!
IZConnection.SetTransactionIsolation(tiReadCommitted);

and you would wonder what happens ((: Test it!

ab wrote:

If I use GetBinaryString(), it just does not work, since for SQlite3 it returns always ''.

Surprise again!! Hmpf

ab wrote:

If I'm right:
-  TZRowAccessor.GetString() returns the UTF-8 encoded text in Delphi < 2009;
-  TZRowAccessor.GetString() returns an UnicodeString converted from UTF-8 content for Delphi > 2009.

This is right in your case. UTF8 is optimal, but all othe AnsiString-CP are equal possible to map into the Wide/UnicodeStrings -> ZEncoding.pas no problem. But i'll check your suggstions too.

ab wrote:

The Result := String(TempBlob.GetString); statement in line 1003 of ZDBCCache.pas sounds like an unneeded conversion to me, for Unicode versions of Delphi.
Or should I just not use "controls_cp=CP_UTF8" for Unicode version of Delphi?

Have no sources here, i'll check it. but i thing IZBlob.GetString returns allways RawByteStrings...

Offline

#11 2013-02-18 19:14:34

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

EgonHugeist wrote:

Should we add the extrected path to the current OS path-settings? We've the same behavior on PostgreSQL. Propose a patch (;

Very simple: just previous := getdir + chdir(blabla) + chdir(previous).
Something like that in unit ZPlainLoader:

function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: TFileName; // AB modif
begin
   if FLoaded then
      Self.FreeNativeLibrary;
   FLoaded := False;
   Result := False;
   newpath := ExtractFilePath(Location);
   // AB modif BEGIN
   try
     if newpath<>'' then begin
       temp := GetCurrentDir;
       SetCurrentDir(newpath);
     end;
   // AB modif END

{$IFDEF UNIX}
  {$IFDEF FPC}
        FHandle := LoadLibrary(PAnsiChar(Location));
  {$ELSE}
        FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
  {$ENDIF}
{$ELSE}
        FHandle := LoadLibrary(PChar(Location));
{$ENDIF}
    // AB modif BEGIN
   finally
     if temp<>'' then
       SetCurrentDir(temp);
   end;
   // AB modif END
   if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
   begin
      FLoaded := True;
      FCurrentLocation := Location;
      Result := True;
   end;
end;
EgonHugeist wrote:

Autocommit = False + StartTransaction 4 secs
Autocommit = False + StartTransaction + PreparedStatment (7.1) 2 secs

1. Is my  SetTransactionIsolation() use correct?
See http://synopse.info/fossil/info/0256f1ac15
Now performance is much better for SQlite3:

Insertion speed
        Direct    Batch    Trans    Batch Trans
ZEOS SQlite3    471    485    8529    8751
ZEOS Firebird    1813    1771    7542    7746

Read speed
     By one    All Virtual    All Direct
ZEOS SQlite3    3272    100060    117530
ZEOS Firebird    2529    60101    66367

2. How do Prepared Statements work with ZDBC?
How do you know the provider supports it?

What worries me is that is I reuse a IZPreparedStatement, e.g. in SQlite3, TZEmulatedPreparedStatement.ExecuteQueryPrepared() will inline the parameter (e.g. changing ID=? into ID=1), then call sqlite3_prepare() twice with the sample handle, so it will just fail.
In short: sounds to me that the ZDBC Sqlite3 driver just does not allow proper parameters binding!
sad

With Firebird, the same parametrized query (ID=?) is re-used, and it speeds up the process from x1.5 to x3 big_smile

Insertion speed
        Direct    Batch    Trans    Batch Trans
ZEOS Firebird    2184    2202    19666    22521
Read speed
     By one    All Virtual    All Direct
ZEOS Firebird    4216    68108    85292

Results after commit http://synopse.info/fossil/info/9cc3701b27

EgonHugeist wrote:

Have no sources here, i'll check it. but i thing IZBlob.GetString returns allways RawByteStrings...

So string() will make a conversion into UnicodeString? ??? :s

Offline

#12 2013-02-23 13:31:28

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

I'm sorry for the delay. ):
Currently i've minior time for Zeos. Now it's weekend!

Very simple: just previous := getdir + chdir(blabla) + chdir(previous).
Something like that in unit ZPlainLoader:

function TZNativeLibraryLoader.ZLoadLibrary(Location: String): Boolean;
var newpath, temp: TFileName; // AB modif
begin
   if FLoaded then
      Self.FreeNativeLibrary;
   FLoaded := False;
   Result := False;
   newpath := ExtractFilePath(Location);
   // AB modif BEGIN
   try
     if newpath<>'' then begin
       temp := GetCurrentDir;
       SetCurrentDir(newpath);
     end;
   // AB modif END

{$IFDEF UNIX}
  {$IFDEF FPC}
        FHandle := LoadLibrary(PAnsiChar(Location));
  {$ELSE}
        FHandle := HMODULE(dlopen(PAnsiChar(Location), RTLD_GLOBAL));
  {$ENDIF}
{$ELSE}
        FHandle := LoadLibrary(PChar(Location));
{$ENDIF}
    // AB modif BEGIN
   finally
     if temp<>'' then
       SetCurrentDir(temp);
   end;
   // AB modif END
   if (FHandle <> INVALID_HANDLE_VALUE) and (FHandle <> 0) then
   begin
      FLoaded := True;
      FCurrentLocation := Location;
      Result := True;
   end;
end;

Patch done R2193 \testing-7.1 (SVN) Thank you.

1. Is my  SetTransactionIsolation() use correct?
See http://synopse.info/fossil/info/0256f1ac15
Now performance is much better for SQlite3:

Well done!

2. How do Prepared Statements work with ZDBC?
How do you know the provider supports it?

The DbcConnection of each protocol decides if a PreparedStatment will be created. Theire are known issues with MySQL f.e. : http://dev.mysql.com/doc/refman/5.0/...-problems.html
To force creating the MySQL/PostgreSQL/SQLite RealPrepareds use

DbcConnection.PrepareStatementWithParams(const SQL: string; Info: TStrings): IZPreparedStatement;

and add 'preferprepared=True' to the Info-Strings.

BUT this dosn't solve you SQLite issue with Zeos-7.0.3. The CAPI Statement is supported on \testing-7.1 (SVN). Currently we do think about omiting the emulated Statements of Postgre and SQLite since the implementation is stable and only a case like 'select :p1' is NOT prepareable. If we decide this step than the RealPrepareds are default and no additional parameter must be used.

Nice comment! But IMHO this should work equal with oracle.. Parameter-Binding is propably the fastest way ((:

So string() will make a conversion into UnicodeString? ??? :s

Yes this is right, and makes only sence for the binary cast... I've started to solve the GetBinaryString-Issue which is NOT supported for all cached-resultsets hmpf. Theire was no test available -> didn't see that, sorry. If i'm ready than NO converstions where done for adding the Strings to the heap and reading them with these methodes. All other behavior i'll not change. Hint this comming patch will be only available since 7.1 too.

Is theire a way to update your diagramms after my little Zeos-tutorial?


Michael

Btw. i had a look to SynCommon.pas! Terrific! Seems like you're the better coder and speed optimizer!
What are you doing with different system-codepages (932 chinese f.e.)? My current implementation does support all Windows/MacOS/*Nix codepages. I was starting from the premisse we should support all these possible CP's (since mainly the users did raise bugreports). Here i understand your UTF8 based framework which is neutral. I was also thinking about cases like OS-CP = 1251 and Client-CP = 1251. No convertations where done in this case except for the Unicode-IDE's.

Offline

#13 2013-02-25 06:07:08

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

MBCS code pages will use the OS APIs.
Fixed size charset count (European, Cyrilic, Greek...) have their own optimized process, with a dedicated class.

But for all UTF-8 work, ASCII 7-bit content (e.g. numbers or field names in JSON process) will be handled in an optimized way, even for MBCS charsets.

Latest version also handle UTF-16 diacritics encoding/decoding, and content checking (i.e. invalid UTF-8 content will be identified as such).

Offline

#14 2013-03-15 20:36:27

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

Re: SynDBDataset - using SynDB or mORMot ORM with any database backend

I've just tested the latest ZeosLib/ZDBC unstable sources, from SVN.
Sounds like if SQLite3 ZDBC provider has been enhanced for speed.

Latest results:

Insertion speed
                      Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	486	501	89649	107865
SQLite3 (file off)	913	253	90960	111336
SQLite3 (mem)		82944	103571	98361	123098
TObjectList (static)	272123	419357	278768	399392
TObjectList (virtual)	272821	362266	255649	418410
SQLite3 (ext full)	215	240	93447	117439
SQLite3 (ext off)	337	162	90811	108965
SQLite3 (ext mem)	93294	122970	108511	140536
UniDAC SQlite3		133	163	26155	36272
ZEOS SQlite3		211	224	17455	19163
ZEOS Firebird		2128	2155	17633	21495
UniDAC Firebird		6799	7356	9218	10389
Jet			4289	4393	4749	4947
NexusDB  		5547	5955	7838	8737
Oracle  		344	45178	571	26890
ODBC Oracle		376	379	727	799
ZEOS Oracle		325	311	762	934
UniDAC Oracle		412	423	634	682

Read speed
 			By one	All Virtual	All Direct
SQLite3 (file full)	26785	405580	411793
SQLite3 (file off)	26128	399648	402544
SQLite3 (mem)		114387	403844	407232
TObjectList (static)	269121	627667	627116
TObjectList (virtual)	263949	226193	643997
SQLite3 (ext full)	131623	229389	408697
SQLite3 (ext off)	129819	228341	406173
SQLite3 (ext mem)	131085	229789	394695
UniDAC SQlite3		2251	73777	93859
ZEOS SQlite3		3319	95609	115963
ZEOS Firebird		4057	66520	84275
UniDAC Firebird		2184	65078	90489
Jet			2634	147067	216169
NexusDB			1422	120732	198586
Oracle			757	61626	67463
ODBC Oracle		907	34596	37969
ZEOS Oracle		778	35078	37228
UniDAC Oracle		414	11458	20972

... and some Win64 benchmark!
(compiled under Delphi XE3)

Insertion speed
		 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	482	496	74196	97858
SQLite3 (file off)	941	436	73858	94030
SQLite3 (mem)		67706	91667	82658	105434
TObjectList (static)	273627	464037	275057	459685
TObjectList (virtual)	271665	458043	270153	456121
SQLite3 (ext full)	214	237	82155	108932
SQLite3 (ext off)	339	308	85045	110643
SQLite3 (ext mem)	80468	105511	89072	126218
ZEOS SQlite3		237	220	11394	12548
ZEOS Firebird		1839	2114	16496	18343
Oracle			419	66789	697	10203
ZEOS Oracle		344	310	808	802


Read speed
	 		By one	All Virtual	All Direct
SQLite3 (file full)	25873	440373	440528
SQLite3 (file off)	26415	438442	434707
SQLite3 (mem)		92575	439483	438558
TObjectList (static)	266609	851498	847026
TObjectList (virtual)	267866	376052	829049
SQLite3 (ext full)	104758	258571	429885
SQLite3 (ext off)	100948	265139	429627
SQLite3 (ext mem)	112516	267165	417362
ZEOS SQlite3		3603	92903	115236
ZEOS Firebird		4957	65228	85051
Oracle			754	63811	75971
ZEOS Oracle		798	32525	47152

Sounds like if our local Oracle server has performance issues - batch speed is inconsistent in "Oracle" direct mode.
Some background process is running, I guess.

But we can see that ZEOS does pretty well, and is better than UniDac for some timing. In fact, since our SynDBZeos.pas unit directly access the ZDBC layer, it makes it faster, since there is no conversion to the internal TDataSet buffers.
I'm pretty convinced that this TDataSet component is just a speed bottleneck, when you try to manage best speed possible.
I suspect even UniDAC/AnyDAC/FireDAC, which claim to be the fastest around, would beat our direct access classes, e.g. for SynDBOracle.pas... not bad for free units, with so few lines of code!

Our direct Sqlite3 access are still MUCH faster than other solutions...
I do not think this is due to our SynDB overhead, on the contrary.

And Win64 is working great.
A bit slower than Win32 in some cases, but still pretty good performance, and stability.
The TObjectList numbers are better under Win64 than under Win32. Nice!

Enjoy!

Offline

Board footer

Powered by FluxBB