#1 2013-11-18 21:24:43

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) :


    "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;
    tmp: Types.TByteDynArray;


procedure TSQLDBZEOSStatement.ExecutePrepared;
    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 smile



#2 2013-11-19 15:02:13

From: France
Registered: 2010-06-21
Posts: 14,359

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).


#3 2013-11-20 23:03:17

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.

ab wrote:

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.

ab wrote:

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.

ab wrote:

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):


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).


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.

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


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.


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.

ps. open souce rocks! big_smile

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)


Board footer

Powered by FluxBB