mORMot Open Source

moctes

Member

From: Mexico

Registered: 2013-05-11

Posts: 129

ZEOS + PostgreSQL follow up

Hi to all,

I decided to discard UNIDAC testing, and instead test ZeosDBO 7.2 alpha, I was told by someone on the Zeos team that I can achieve better results with the alpha, so here are the results (previous and current) :

7.1

{
    "Engine": "ZEOS",
    "CreateTableTime": "58.21ms",
    "NumberOfElements": 5000,
    "InsertTime": "2.09s",
    "InsertRate": 2386,
    "InsertBatchTime": "1.97s",
    "InsertBatchRate": 2532,
    "InsertTransactionTime": "1.22s",
    "InsertTransactionRate": 4087,
    "InsertBatchTransactionTime": "1.23s",
    "InsertBatchTransactionRate": 4041,
    "ReadOneByOneTime": "1.27s",
    "ReadOneByOneRate": 3932,
    "ReadAllVirtualTime": "51.69ms",
    "ReadAllVirtualRate": 96728,
    "ReadAllDirectTime": "36.01ms",
    "ReadAllDirectRate": 138819,
    "ClientCloseTime": "4.06ms"
}

7.2 alpha

{
	"Engine": "ZEOS",
	"CreateTableTime": "37.51ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.71s",
	"InsertRate": 2909,
	"InsertBatchTime": "1.82s",
	"InsertBatchRate": 2741,
	"InsertTransactionTime": "1.01s",
	"InsertTransactionRate": 4937,
	"InsertBatchTransactionTime": "987.51ms",
	"InsertBatchTransactionRate": 5063,
	"ReadOneByOneTime": "1.01s",
	"ReadOneByOneRate": 4924,
	"ReadAllVirtualTime": "35.17ms",
	"ReadAllVirtualRate": 142150,
	"ReadAllDirectTime": "24.66ms",
	"ReadAllDirectRate": 202708,
	"ClientCloseTime": "3.73ms"
}

The changes proposed by Delphinium on : http://synopse.info/forum/viewtopic.php?pid=8972#p8972 are needed, also I had to change:

procedure TSQLDBZEOSStatement.ExecutePrepared;
var
    tmp: Types.TByteDynArray;

to

procedure TSQLDBZEOSStatement.ExecutePrepared;
var
    tmp: TArray<System.Byte>;

I suppose is because of a change on Zeos side, don't know if can be added in a way that doesn't break code, for now I'm done with this  benchmarkig stuff; the numbers show an improvement and are good enough for my needs so I  will go the Zeos route, I have a project to start which will keep me asking for more of the mORMot

Regards

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: ZEOS + PostgreSQL follow up

Thanks for the feedback.
Nice speed increase, in fact!
ZDBC are realy prime host to mORMot.

What about this patch: http://synopse.info/fossil/info/c3394485b2

I suspect this will work with all branches of Zeos 7.x, and all Delphi compilers (I hope).

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: ZEOS + PostgreSQL follow up

Hi Arnaud,

i'm sorry for the long delay. And I'm sorry for the promissed speed patches don 7.1. They did never happen on 7.1. I was moving house so there was no time for that.

Thanks for the feedback.
Nice speed increase, in fact!
ZDBC are realy prime host to mORMot.

But not top of data, Arnaud. I played and added a loads of code to the dbc-layer. Propose you check 7.2? Even if we're not ready yet. Mark is working on a patch to suppress the logging processes.

I changed the String-Getter&Setter and the Lob-handling is much more powerfull now. And the statments also have a newly type which should hit your goals a bit more.

What about this patch: http://synopse.info/fossil/info/c3394485b2

It should work. Zeos automatically try's to check the encoding since we had too much bugreports in this domain. Hint on 7.2 i differ between TZAbstract-BLOB and CLOB.
The Clobs do support some spezial getter & Setter equal to the newly introduced one of the ResultSets.

I suspect this will work with all branches of Zeos 7.x, and all Delphi compilers (I hope).

Nope, sorry. I changed this code because of a incomplatibility to the TField (which i'm writing Zeos-owned once to speed up the DataSets)

You'll find a {ZEOS72UP} define in Zeos.inc to chage your framework without breaking support for older Zeos-revs.

Long times ago you wrote Zeos is doing to much String-conversions. In fact you had been right. on 7.2 i've been trying to avoid this, where ever possible.
I used a loads of code of the FastCode-Project. User Miab3 did point me to this code and horray we can work with native unconverted values with the Unicode-IDE's too!

And there are some options for the Statments you create (add them to the Info strings):

'cachedlob=Boolean'

Current default=False

This means to localize all lob's on fetching the rows.
Supported protocols(actually) for non-cached lobs:
FireBird, MySQL, Oracle, PostgresSQL. For ADO, DBLib, SQLite it doesn't make sence, since the values are already fetched or we cant't scroll in the resultset-api (forward-only).

'chunk_size=Integer'

Current default=4096

This value controls the package-size for sending/retrieving lob's. It's a question about your local network speed which package size is usefull.

Supported protocols: Oracle, MySQL+Real-Prepared, PostgresSQL
FireBird is always adapting to the Blob-Segment-Size.

'prefetch_count=Integer'
Oracle only. This sets the OCI_ATTR_PREFETCH_ROWS. Current default: 1000. If you select many rows than increase this value.

'ForceNativeResultSet=Boolean'

Current default='false'
Purpose: AVOID creating cached resultsets and you get access to the native resultset.
A pure SQLite option only. which is working forward only and as long a fetch is incomplete -> table is locked.

'BindDoubleDateTimeValues=Boolean'

Current default='false'
A pure SQLite option only.
Purpose: force Zeos to bind double values instead of the ISO-DateTime-Formated strings we actually use. Since 7.2 milliseconds are added to the ISO-Format.

I'm sure my incomplete but actually very successfull refactoring should increase the performance again for the most plains. I'm not ready here an there so a stable candidate won't happen the next months, i think.

Michael
ps. open souce rocks!

Prose you open a thread in the Zeos-Forum and i'll guid you through the changes. So everybody who is interested to use ZDBC can read it too..

Last edited by EgonHugeist (2013-11-20 23:28:14)