Zeoslib Portal

This is the official forum of ZeosLib providing support to all ZeosLib users.
https://zeoslib.sourceforge.io/

ZeosLib 7.1 testers-thread

https://zeoslib.sourceforge.io/viewtopic.php?t=3661

Page 8 of 12

Posted: 06.08.2013, 15:22
by miab3
@EgonHugeist, @mdaems,

Zeos7.1-(beta) branches_ testing_R2607:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.1/
compiles and runs on (I have tested):

- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++(32),
- Lazarus Windows7-64 1.1.0/fpc 2.7.1 32/64 (CodeTyphon ver 4.40),
(For Linux currently I have no way to test)

Firebird 2.5.3, MySQL 5.5.18, MariaDB 5.5.29, PostgreSQL 9.2.2, MSSQL 2008 R2 Express(mssql, FreeTDS, ado-sqlncli), *.mdb-ado-Jet.OLEDB.4.0, Oracle Database Express Edition 11g Release 2, SQLite 3.7.17.

ADO-jet-mdb works.
A tiny change in the line:433 ZDbcAdoStatement.pas:

Code: Select all

if VarIsClear(P.Value) or (( not ( SQLType = stBytes )) and (P.Value <> V)) then //Check if Param is cleared, unasigned or different
To me, now suitable for stable version or at least a Release Candidate

Michal

Posted: 09.08.2013, 11:28
by miab3
@EgonHugeist,

Do you know how to speed up the update in SQLite?
So far, it is the slowest of all available in ZEOS dbengines and dbservers.
Although there is a local.

Michal

Posted: 09.08.2013, 12:05
by EgonHugeist
miab3,
ADO-jet-mdb works.
A tiny change in the line:433 ZDbcAdoStatement.pas:
Code: [Erweitern] [mehr anzeigen] [Verkleinern] [Alles ausw?hlen]
if VarIsClear(P.Value) or (( not ( SQLType = stBytes )) and (P.Value <> V)) then //Check if Param is cleared, unasigned or different
Done in a different way. I completely rewrote the whole ADO statement. It was a "ad hock" implementation before. I've changed the Prepare/unprepare sequence, brambinding, ControlsCodePage support for UTf8 etc...

Please test it.
To me, now suitable for stable version or at least a Release Candidate
7.0.5-stable will be released at end of this month. 7.1.1-rc too.
Do you know how to speed up the update in SQLite?
So far, it is the slowest of all available in ZEOS dbengines and dbservers.
Although there is a local.
Yes. Do not use AutoCommit and TransactionIsolationLevel tiNone. Try to update as much as possible in one Transaction.

Some personal test with 10000 simple rows:
AutoCommit + tiNone: 2 minutes
No AutoCommit + toReadCommited(or something else (there is only StartTransaction available for SQLite)) 2 seconds

Each commit seems to rewrite the whole file again (i don't know that exacty but i can hear my harddisk extreamly work for each commit )

So commit 1 update is equal fast as commiting 10000 updates.

Hope this helps you a bit.

Posted: 09.08.2013, 13:27
by miab3
@EgonHugeist,

Can you throw some minimal example(SQLite), update a series of records.
I can not exceed 200 updeate per second.
Except that I do read and update such:

Code: Select all

   
   stmp:=0;
   li:=0;
   while NOT Ztable1.EOF do
     begin
     stmp:=stmp+Ztable1.Fields[5].AsFloat;
     Ztable1.Edit;
     Ztable1.Fields[5].AsFloat:=Ztable1.Fields[5].AsFloat+0.1;
     li:=li+1;
     Ztable1.Next;
     end;
Michal

Posted: 09.08.2013, 23:24
by miab3
@EgonHugeist,

For Delphi XE2 in ZDbcAdoUtils.pas line601 must be:

Code: Select all

ParamCount: ULONG_PTR; //  was UINT; ( ULONG_PTR = NativeUInt;)
(For Delphi 2007up may be)

Michal

Posted: 10.08.2013, 10:49
by miab3
@EgonHugeist, @mdaems,

Zeos7.1-(beta) branches_ testing_R2614:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.1/
compiles and runs on (I have tested):

- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++(32),
- Lazarus Windows7-64 1.1.0/fpc 2.7.1 32/64 (CodeTyphon ver 4.40),
(For Linux currently I have no way to test)

Firebird 2.5.3, MySQL 5.5.18, MariaDB 5.5.29, PostgreSQL 9.2.2, MSSQL 2008 R2 Express(mssql, FreeTDS, ado-sqlncli), *.mdb-ado-Jet.OLEDB.4.0, Oracle Database Express Edition 11g Release 2, SQLite 3.7.17.

NEW ADO-jet-mdb works.

Something's up with the ROWID(SQLite).
http://sourceforge.net/p/zeoslib/tickets/36/
http://www.sqlite.org/autoinc.html
I've added a 'select rowid, * from Table1' and update when rowide as the key is faster.

Michal

Posted: 10.08.2013, 12:15
by miab3
@EgonHugeist,

Zeos7.1-(beta) branches_ testing_R2616(maybe R2615):
It looks like it is slower.

Michal

Posted: 11.08.2013, 11:46
by EgonHugeist
miab3,

I'm sorry Michal. I was talking about insertiation speed. Updating is another thing.
Zeos7.1-(beta) branches_ testing_R2616(maybe R2615):
It looks like it is slower.
I can't confirm this right now. You need a huge average and certain runs to be sure we're slower or faster. Currently i'm working out a common table for our performance tests. What i didn't expect are the many remaing isssues i found. Propose you switch to 7.2 where i know some fundamential things are more optimal. But don't expect to much. 7.2 is a start. Currently i couldn't going on because of bugfixing 7.1/7.0 in preparation of the next relases.

Posted: 14.08.2013, 09:38
by miab3
@EgonHugeist, @mdaems,

Zeos7.1-(beta) branches_ testing_R2642:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.1/
compiles and runs on (I have tested):

- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++(32),
- Lazarus Windows7-64 1.1.0/fpc 2.7.1 32/64 (CodeTyphon ver 4.40),
(For Linux currently I have no way to test)

Firebird 2.5.3, MySQL 5.5.18, MariaDB 5.5.29, PostgreSQL 9.2.2, MSSQL 2008 R2 Express(mssql, FreeTDS, ado-sqlncli), *.mdb-ado-Jet.OLEDB.4.0, Oracle Database Express Edition 11g Release 2, SQLite 3.7.17.

Michal

Posted: 15.08.2013, 11:16
by miab3
@EgonHugeist, @mdaems,

Zeos7.1-(beta) branches_ testing_R2644:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.1/
compiles and runs on (I have tested):

- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++(32),
- Lazarus Windows7-64 1.1.0/fpc 2.7.1 32/64 (CodeTyphon ver 4.40),
(For Linux currently I have no way to test)

Firebird 2.5.3, MySQL 5.5.18, MariaDB 5.5.29, PostgreSQL 9.2.2, MSSQL 2008 R2 Express(mssql, FreeTDS, ado-sqlncli), *.mdb-ado-Jet.OLEDB.4.0, Oracle Database Express Edition 11g Release 2, SQLite 3.7.17.

Michal

Posted: 19.08.2013, 11:26
by miab3
@EgonHugeist, @mdaems,

Zeos7.1-(beta) branches_ testing_R2659:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.1/
compiles and runs on (I have tested):

- D2006,
- D2007 -> C++,
- DXE2 32/64 -> C++(32),
- Lazarus Windows7-64 1.1.0/fpc 2.7.1 32/64 (CodeTyphon ver 4.40),
(For Linux currently I have no way to test)

Firebird 2.5.3, MySQL 5.5.18, MariaDB 5.5.32, PostgreSQL 9.2.2, MSSQL 2008 R2 Express(mssql, FreeTDS, ado-sqlncli), *.mdb-ado-Jet.OLEDB.4.0, Oracle Database Express Edition 11g Release 2, SQLite 3.7.17.

Michal

Re: ZeosLib 7.1 testers-thread

Posted: 20.08.2013, 17:07
by miab3
Referring to:
http://sourceforge.net/p/zeoslib/tickets/44/

I get an error as attached when I try to add a field TABLE_CATALOG to dataset :
tab_cat.png
+ $41[03BA0268]{dbrtl100.bpl} DB.DB.DatabaseError (Line 2478, "DB.pas" + 2) + $41
+ $15[03BA0305]{dbrtl100.bpl} DB.DB.DatabaseErrorFmt (Line 2485, "DB.pas" + 1) + $15
+ $6C[16EC13A6]{ZComponent100.bpl} ZAbstractRODataset.TZAbstractRODataset.CheckFieldCompatibility (Line 3561, "..\..\src\component\ZAbstractRODataset.pas" + 4) + $6C
+ $17[03BAFB76]{dbrtl100.bpl} DB.DB.DoBindFields (Line 9547, "DB.pas" + 26) + $17
+ $7[03BAFC85]{dbrtl100.bpl} DB.DB.TDataSet.BindFields (Line 9572, "DB.pas" + 4) + $7
+ $5[16EBE4EB]{ZComponent100.bpl} ZAbstractRODataset.TZAbstractRODataset.InternalOpen (Line 1787, "..\..\src\component\ZAbstractRODataset.pas" + 47) + $5
+ $4[03BAF069]{dbrtl100.bpl} DB.DB.TDataSet.DoInternalOpen (Line 9259, "DB.pas" + 2) + $4
+ $9[23026363]{dcldb100.bpl} DSDesign.DSDesign.TFieldsEditor.CreateFields (Line 656, "DSDesign.pas" + 31) + $9
+ $C[23026961]{dcldb100.bpl} DSDesign.DSDesign.TFieldsEditor.DoAddFields (Line 836, "DSDesign.pas" + 28) + $C
+ $2[230267B9]{dcldb100.bpl} DSDesign.DSDesign.TFieldsEditor.AddFields (Line 798, "DSDesign.pas" + 1) + $2
+ $5[5204F0F9]{vcl100.bpl } Menus.Menus.TPopupList.MainWndProc (Line 3374, "Menus.pas" + 2) + $5
+ $0[51F60BC0]{rtl100.bpl } Classes.Classes.StdWndProc (Line 11572, "classes.pas" + 8) + $0
+ $1CC[75F1C4E4]{USER32.dll } gapfnScSendMessage + $1CC
+ $2CA[75F1C5E2]{USER32.dll } gapfnScSendMessage + $2CA
+ $8FC[75F1CC14]{USER32.dll } gapfnScSendMessage + $8FC
+ $A[75F12E3C]{USER32.dll } DispatchMessageA + $A

Re: ZeosLib 7.1 testers-thread

Posted: 22.08.2013, 11:50
by miab3
@EgonHugeist,

Zeos 7.1 and 7.2 rev 2668;
Somewhere after your last change slowed opening in MySQL (MariaDB) tables with blobs (and the non-linear).
Time to open:
select tint, tstring from table1 LIMIT ?
-----------------------
Limit 50000 0,26 sec
Limit 100000 0,49 sec
Limit 150000 0,73 sec
Limit 200000 0,98 sec
Limit 250000 1,21 sec
Limit 300000 1,45 sec
Limit 350000 1,69 sec
Limit 400000 1,94 sec
Limit 450000 2,18 sec
Limit 500000 2,41 sec


select tint, tstring, tblob from table1 LIMIT ?
-----------------------
Limit 50000 0,44 sec
Limit 100000 1,14 sec
Limit 150000 2,54 sec
Limit 200000 4,28 sec
Limit 250000 6,08 sec
Limit 300000 11,43 sec
Limit 350000 20,48 sec
Limit 400000 25,43 sec
Limit 450000 39,43 sec
Limit 500000 50,91 sec
Michal

Re: ZeosLib 7.1 testers-thread

Posted: 22.08.2013, 12:22
by miab3
Continuation.
Without Blob is sometimes just as bad, and also non-linear.


select * from rob1p LIMIT ?
--------------------------------
Limit 50000 1,87 sec
Limit 100000 6,05 sec
Limit 150000 17,91 sec
Limit 200000 43,76 sec
Limit 250000 80,76 sec
Limit 300000 119,68 sec



CREATE TABLE rob1p (
NRKOL Integer(11),
`DATA` DateTime,
FIRMA VarChar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
TYP Double,
NR VarChar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
SYMBOL VarChar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
NAZWA VarChar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
CENA_J Double,
ILOSC Double,
J_MIARY VarChar(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
KOD_DOST VarChar(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
PODATEK Double,
C_J1 Double,
S_WART Double,
S_VAT Double,
CENA_DOST Double,
CENA_ZAK Double,
NIP VarChar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
WNETT Double,
VAT7 Double,
PVAT7 Double,
VAT22 Double,
PVAT22 Double
) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Horror

Michal

Re: ZeosLib 7.1 testers-thread

Posted: 22.08.2013, 14:48
by miab3
Continuation.
For comparison, the same for PostgreSQL:

select tint,tstring from "table1" LIMIT ?
-----------------------
Limit 50000 0,16 sec
Limit 100000 0,30 sec
Limit 150000 0,45 sec
Limit 200000 0,59 sec
Limit 250000 0,74 sec
Limit 300000 0,88 sec
Limit 350000 1,02 sec
Limit 400000 1,18 sec
Limit 450000 1,31 sec
Limit 500000 1,44 sec

select tint,tstring, tblob from "table1" LIMIT ?
-----------------------
Limit 50000 0,22 sec
Limit 100000 0,42 sec
Limit 150000 0,63 sec
Limit 200000 0,80 sec
Limit 250000 1,04 sec
Limit 300000 1,23 sec
Limit 350000 1,42 sec
Limit 400000 1,63 sec
Limit 450000 1,84 sec
Limit 500000 2,03 sec

select * from "ROB1P" LIMIT ?
----------------------
Limit 50000 2,09 sec
Limit 100000 4,19 sec
Limit 150000 6,13 sec
Limit 200000 8,16 sec
Limit 250000 10,27 sec
Limit 300000 11,68 sec

Michal
All times are UTC+01:00
Page 8 of 12

Powered by phpBB® Forum Software © phpBB Limited