ZeosLib 7.1 testers-thread

The official tester's forum for ZeosLib 7.1. Ask for help, post proposals or solutions.
miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 06.08.2013, 15:22

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 09.08.2013, 11:28

@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

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1734
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 09.08.2013, 12:05

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.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 09.08.2013, 13:27

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 09.08.2013, 23:24

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 10.08.2013, 10:49

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 10.08.2013, 12:15

@EgonHugeist,

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

Michal

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1734
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 11.08.2013, 11:46

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.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 14.08.2013, 09:38

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 15.08.2013, 11:16

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Postby miab3 » 19.08.2013, 11:26

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Re: ZeosLib 7.1 testers-thread

Postby miab3 » 20.08.2013, 17:07

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
You do not have the required permissions to view the files attached to this post.

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Re: ZeosLib 7.1 testers-thread

Postby miab3 » 22.08.2013, 11:50

@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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Re: ZeosLib 7.1 testers-thread

Postby miab3 » 22.08.2013, 12:22

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

miab3
Zeos Test Team
Zeos Test Team
Posts: 931
Joined: 11.05.2012, 12:32
Location: Poland

Re: ZeosLib 7.1 testers-thread

Postby miab3 » 22.08.2013, 14:48

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


Return to “ZeosLib 7.1 stable Forum”

Who is online

Users browsing this forum: No registered users and 1 guest