Zoes7.2 upgrade

EgonHugeist · 2014-08-21 07:53:15

Daniel,

could you debug function ZDbcInterbaseUtils.pas function procedure TZParamsSQLDA.UpdatePAnsiChar(const Index: Integer; const Value: PAnsiChar; const Len: Cardinal);

which field type is determined by ISC and are my conversion function correctly (you'll see this in if function UpdateDateTime(Index, TempTimeStamp); is called).

What i see is a minor bug with my last commit, ReadFormatSettings should be replaced by WriteFormatSettings. But AFAIK should they be binary equal for mORMot.

danielkuettner · 2014-08-21 08:20:18

Now I have it:

My request calls

(2328) TempTimeStamp := RawSQLTimeToDateTime(Value, Len, ConSettings^.ReadFormatSettings, Failed);
After that TempTimeStamp is '30.12.1899' and thats the problem.

He looks for a time, but there is only a Date (the Type he found is SQL_TIMESTAMP).

If I do a my request so:

http://localhost:8180/root/?sql=select+RowID,*+from+kontoauszug+where+BTAG>'2014-01-01 00:00:00'+limit+10

then it works.

If you need futher information please let me know.

Last edited by danielkuettner (2014-08-21 08:24:15)

EgonHugeist · 2014-08-21 08:36:34

Thank you, i did commit a patch R3270 \testing-7.2 (SVN).

Hope we got these little buggas now

danielkuettner · 2014-08-21 08:53:00

It was a pleasure for me. Thanks for the quicky (support).

danielkuettner · 2014-08-21 09:23:03

Now it works! Also under Win64.

EgonHugeist · 2014-08-24 09:30:17

Thanx Daniel.

Arnaud today i commit my own idea about Batch insertiation with FireBird. see http://sourceforge.net/p/zeoslib/code-0/commit_browser R3275 \testing-7.2.

I added two size checks (correct me if i'm wrong): command should fit in 32KB range and whole allocated mem must fit in 64KB limit per execution.
Enabled SupportsArrayBindings indicator for Firebird in R3276.

Nice NO changes required in you framework!

I don't use fb-embedded(should perform much better) here, tested with FBCLIENT.DLL:

{
  "Engine": "ZEOS Firebird",
  "CreateTableTime": "137.45ms",
  "NumberOfElements": 5000,
  "InsertTime": "3.72s",
  "InsertRate": 1342,
  "InsertBatchTime": "122.62ms",
  "InsertBatchRate": 40776,
  "InsertTransactionTime": "354.58ms",
  "InsertTransactionRate": 14101,
  "InsertBatchTransactionTime": "123.99ms",
  "InsertBatchTransactionRate": 40323,
  "ReadOneByOneTime": "548.43ms",
  "ReadOneByOneRate": 9116,
  "ReadAllVirtualTime": "43.60ms",
  "ReadAllVirtualRate": 114676,
  "ReadAllDirectTime": "33.96ms",
  "ReadAllDirectRate": 147210,
  "ClientCloseTime": "4.82ms"
}

As i wrote: have no other component to compare the performance. Feel free to test the results I would be interested to know how we pereform agains Uni/FireDac now.

OpenSource rocks! Yes it does!

Edit:

ab wrote:

Nice!
I wonder why my implementation is more than 20% faster than your initial version.
Sounds just like a small refactoring from your initial patch.

No idea the changes are not related to the results. It looks to me if my system falls into standby mode and wakes up then... OCI is still tired than

Last edited by EgonHugeist (2014-08-24 09:34:18)

ab · 2014-08-24 13:29:24

Great addition!

Resulting performance boost is impressive.
Now FireDAC has no advantage over ZDBC when inserting such statements.
And all DB.pas / TDataSet based libraries (like FireDAC or UniDAC) are actually slower than ZDBC direct access, since TDataSet is a true bottleneck!

Thanks a lot for sharing!

EgonHugeist · 2014-10-21 21:51:50

@all

finally our official Beta-tag is done.
Download 7.2-Beta here: http://sourceforge.net/projects/zeoslib … p/download

@Arnaud

i would be happy if you could update your performance diagramms.

OpenSource ROCKS!

Last edited by EgonHugeist (2014-10-21 21:52:13)

miab3 · 2014-10-21 22:03:32

@Arnaud

Could you add to the "30 - MVC Server" connection for ZEOS?.

Michal

Last edited by miab3 (2014-10-21 22:11:35)

ab · 2014-10-22 07:17:14

@EgonHugeist

Now direct Zeos/ZDBC access is clearly the fastest way of using external SQL databases with mORMot.
For both writing and reading - and especially at reading, where by-passing DB.pas and its TDataSet bottleneck makes ZDBC 2.5 times faster than the expensive FireDAC!

Insertion speed

		 	Direct	Batch	Trans	Batch Trans
SQLite3 (file full)	439	28715	67945	170259
SQLite3 (file off)	2060	87587	87556	194099
SQLite3 (file off exc)	24311	191000	88328	201661
SQLite3 (mem)		71484	228958	91606	225794
TObjectList (static)	280835	439521	269396	435502
TObjectList (virtual)	273972	439908	266880	438865
SQLite3 (ext full)	498	12472	84984	151666
SQLite3 (ext off)	2271	42367	93231	172205
SQLite3 (ext off exc)	23557	150132	94655	171291
SQLite3 (ext mem)	81559	205187	95796	205482
ODBC SQLite3		575	11936	33537	75480
ZEOS SQlite3		488	13143	44595	81663
FireDAC SQlite3		18684	38913	37886	106107
ODBC Firebird		980	15392	10051	14874
ZEOS Firebird		10395	54686	22504	52468
FireDAC Firebird	20150	49276	20291	49057
MSSQL2012 local		4234	42164	12292	57905
ODBC MSSQL2012		4054	7737	11171	9083
FireDAC MSSQL2012	3788	5729	9565	40822
ZEOS PostgreSQL		3029	30152	7837	45173
ODBC PostgreSQL		2842	29689	3862	32583
FireDAC PostgreSQL	2922	28586	7157	29408

Read speed
 			By one	Virtual	Direct
SQLite3 (file full)	108811	480030	484496
SQLite3 (file off)	109361	478285	444049
SQLite3 (file off exc)	108429	476917	460744
SQLite3 (mem)		110392	478377	479984
TObjectList (static)	264466	831393	826993
TObjectList (virtual)	261684	359195	821692
SQLite3 (ext full)	115711	224024	464727
SQLite3 (ext off)	113273	228050	477600
SQLite3 (ext off exc)	115364	228550	477920
SQLite3 (ext mem)	115489	225032	475059
ODBC SQLite3		16297	113345	163089
ZEOS SQlite3		35073	168947	254439
FireDAC SQlite3		7951	76751	103887
ODBC Firebird		2015	60124	84109
ZEOS Firebird		21014	81447	109356
FireDAC Firebird	2227	53680	60292
MSSQL2012 local		8721	184311	292431
ODBC MSSQL2012		12064	116273	195472
FireDAC MSSQL2012	3908	91830	131337
ZEOS PostgreSQL		7296	131748	181310
ODBC PostgreSQL		7633	66365	92448
FireDAC PostgreSQL	1889	54675	73219

Clearly, 7.2 branch did achieve a huge performance boost.
You did a great job!

EgonHugeist · 2014-10-22 08:30:04

Thanks Arnaud!

i was thinking about updating this link: http://blog.synopse.info/post/2013/11/0 … ing-MS-SQL
IFAIU is Oracle/FB not changed in the diagramm, or am i wrong?

Note: I'm one with red/green colors (eyes)issues. So reading/comparing the diagramms is ... hard/impossible for me, sorry. Is there a way you change the colors for the Access-layers?
Something like Blue = mMORot, Green = Zeos, Gray = x etc? That would help me very much.

I would like to use the link as reference, Arnaud.

Cheers, Michael

Last edited by EgonHugeist (2014-10-22 09:01:38)

ab · 2014-10-22 10:10:41

This blog post was run on another computer.
And I do not have access to this Oracle DB instance anymore...

The reference are the numbers in the SAD 1.18 pdf documentation, not those blog posts, which are pretty transient.

mORMot Open Source

#51 2014-08-21 07:53:15

Re: Zoes7.2 upgrade

#52 2014-08-21 08:20:18

Re: Zoes7.2 upgrade

#53 2014-08-21 08:36:34

Re: Zoes7.2 upgrade

#54 2014-08-21 08:53:00

Re: Zoes7.2 upgrade

#55 2014-08-21 09:23:03

Re: Zoes7.2 upgrade

#56 2014-08-24 09:30:17

Re: Zoes7.2 upgrade

#57 2014-08-24 13:29:24

Re: Zoes7.2 upgrade

#58 2014-10-21 21:51:50

Re: Zoes7.2 upgrade

#59 2014-10-21 22:03:32

Re: Zoes7.2 upgrade

#60 2014-10-22 07:17:14

Re: Zoes7.2 upgrade

#61 2014-10-22 08:30:04

Re: Zoes7.2 upgrade

#62 2014-10-22 10:10:41

Re: Zoes7.2 upgrade

Board footer