Zeoslib Portal

miab3,
Mark (mdaems) here, from his 'non admin' account.

Concerning the uppercase thing.
Can you please log real functionality bugs in the Ticket system on sourceforge? The broken compilation stuff is nice to have here. They are usually small things that are likely to be solved soon, but this one may take longer.

As far as I know this was an issue that exists at least since 7.0, but I think it was even a problem before. The problem is in the (un)ability of the metadata code to fetch the metadata of the table because in the first case : the quotes around 'DEPT' and in the second case the lower case 'dept'.
I have no clue as to how the dblib protocol works with case. If you know more about it, please have a look at the dbc metadata code. I recently fixed this issue for mysql, postgres and oracle. You could have a look at the code there to get some clues.(rev. 2219)

Mark

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2500:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32).

Michael looked to these two problems which I wrote about
in ZeosLib 7.1 testers-thread:
- with the TableName with space for ado-sqlncli MSSQL,
- with Oracle types NCLOB, CLOB and BFILE.

Michal

miab3,

i made a patch and set BFILE to readonly since i really have no idea how to make a update there. I added all suggested types to our tests. Works fine on my side. Can you check against 7.1 and tell me if there are remaing issues?

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2512:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32) - Windows8-64

Michal

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2564:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32) - Windows8-64

My test after adding ZQuery, Zeos7.2_R2564 DXE2-D32 W8-64:
https://forums.embarcadero.com/thread.j ... 2&tstart=0

'SELECT * FROM TABLE1'
IBQuery: 7,42 sec
IBSQL: 3,94 sec
ADQuery: 20,63 sec
ZQuery: 19,70 sec

'SELECT FIELD1, FIELD2 FROM TABLE1'
IBQuery: 6,71 sec
IBSQL: 3,52 sec
ADQuery: 7,32 sec
ZQuery: 6,66 sec

Michal

miab3,

thanks for the first Benchmark ((:

I've to know some more: the current FireBird FDB CharSet. Is it still 'NONE'? If yes then i'm not surpriced. IBX, IBDAC, UniDac don't handle this case. ADO does it because of the ODBC access. Best practice would be a stable unique charset like UTF8 or CP1520.

Well there are some more imbelievable things we can do: Currently we fetch the data from an interfaced extra SQLDA object. Long times i'm planning to combiate the UpdateSQLDA and ResultSetSQLDA objects with theire stmt/reultset classes. I'm 100% sure this will speed up a huge fetch by some more seconds (5 i think)..

But that's all music of future. First i've to run against the bugtrackers..

Well if you want than i could grant you SVN access and teach you how to use ZTestall where i added the Performance tests.

Some result: i could fetch 10000 rows with 7.2 in a half time against 7.1 for the unicode IDE's (propably there happens the most things because we acces single-byte strings) and SQLite, MySQL, PostgreSQL. Hint the currently used benchmark table "high_load" is a silly thing. No lobs, datetime vals.... Just two fields.

So i would be could to bench 7.1/7.0 against 7.2 in a live environment. Than we can see some more diffs, Michal.

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2569:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32) - Windows8-64

Michal

@EgonHugeist,

Which version ZDbcPostgreSqlResultSet.pas line-706 is correct?:

7.1: if (ResultSetType <> rtForwardOnly) or ((Row >= RowNo) and (Row <= LastRowNo)) then
or
7.2: if (ResultSetType <> rtForwardOnly) or (Row >= RowNo){ and (Row <= LastRowNo +1)) }then

Michal

miab3,

i'm sorry did rollback this commit. The second version is correct. I've commited it to 7.1 (2581) and will merge it to 7.2 after the trunk merge (to be sync with trunk).

This are little bugs the performance tests do show me. You can see them only working directly with the Dbc-layer. Well for the first i'll concentrate me to these tests to get relistic diffs with the branches. Also did i check the IBSQL vs AQuery thread you've linked before. As Dmitry wrote: These are no realistic benchmarks since IBSQL doesn't localize all values. I was also thinking about such an approach in the past! Load lobs only on accessing them (if possible eg.MySQL/SQLite/ADO/MSSQL nope no way but for PG/FireBird/ASA/Oracle we can go this root)..

Like i wrote: First we need better test than start coding to see the diffs clearly.

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2645:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32) - Windows8-64

Michal

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2662:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32) - Windows8-64

Michal

@EgonHugeist,

Zeos7.2-alpha branches_ testing_R2678:
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
compiles and runs on (I have tested):

- DXE2 32/64 -> C++(32) - Windows8-64

Still in MySQL nonlinear slow opening table.

Michal

@EgonHugeist,

In addition to non-linear slowing down for MySQL in ZEOS7.1 and ZEOS7.2 I wrote about:

Recap:

CREATE TABLE test12 (
tint Integer(11),
tstring NVarChar(100) COLLATE utf8_general_ci,
tblob BLOB
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

select tint, tstring from test12 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 test12 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


In zeos7.2 arrived:

ZEOS7.2 select tint, tstring from test12 ZEOS7.2 select tint, tstring, tblob from test12 ZEOS7.1 select tint, tstring, tblob from test12 All for Delphi XE2-32 Win7-64/Win8-64.

Michal

Got the little bugga.

Well my local working copy looks quite different inbetween. Very experimental. What i've testet too: Write a Interbase resultset without interface access. I was sure this should speed up FireBird a bit more.. but that was a wrong impression. No Performance increase to see after using a direct XSQLDA access. But what i can clearly say: Firbird isn't the fastest protocol we do support. Right on the moment as lob's came to shove we've a performance drop i didn't expect before. Did compare our lob access with other compoenents and i can't see any differences...


May i ask for "non linear" queryies? What exaclty do mean?

@EgonHugeist,

May i ask for "non linear" queryies? What exaclty do mean?

I write about it week in both threads 7.1 and 7.2.

For MySQL(last Zeos7.1 and Zeos7.2).

Linear:
select tint, tstring from test12 LIMIT ?
-----------------------
Limit 50000 0,26 sec
+50000 +0,24 sec
Limit 100000 0,49 sec
+50000 +0,24 sec
Limit 150000 0,73 sec
+50000 +0,25 sec
Limit 200000 0,98 sec
+50000 +0,23 sec
Limit 250000 1,21 sec
+50000 +0,24 sec
Limit 300000 1,45 sec
+50000 +0,26 sec
Limit 350000 1,69 sec
+50000 +0,25 sec
Limit 400000 1,94 sec
+50000 +0,24 sec
Limit 450000 2,18 sec
+50000 +0,23 sec
Limit 500000 2,41 sec

Non-linear:
select tint, tstring, tblob from test12 LIMIT ?
-----------------------
Limit 50000 0,44 sec
+50000 +0,70 sec
Limit 100000 1,14 sec
+50000 +1,40 sec
Limit 150000 2,54 sec
+50000 +1,74 sec
Limit 200000 4,28 sec
+50000 +1,80 sec
Limit 250000 6,08 sec
+50000 +5,35 sec
Limit 300000 11,43 sec
+50000 +9,05 sec
Limit 350000 20,48 sec
+50000 +5,38 sec
Limit 400000 25,43 sec
+50000 +14,00 sec
Limit 450000 39,43 sec
+50000 +11,43 sec
Limit 500000 50,91 sec
A scandalous slowness


The same for Firebird.

Linear:
select tint,tstring from table1 ROWS ?
---------------------
Limit 50000 0,40 sec
Limit 100000 0,73 sec
Limit 150000 1,03 sec
Limit 200000 1,42 sec
Limit 250000 1,75 sec
Limit 300000 2,08 sec
Limit 350000 2,40 sec
Limit 400000 2,85 sec
Limit 450000 3,11 sec
Limit 500000 3,46 sec

Linear:
select tint,tstring, tblob from table1 ROWS ?
---------------------
Limit 50000 0,40 sec
Limit 100000 0,78 sec
Limit 150000 1,16 sec
Limit 200000 1,54 sec
Limit 250000 1,96 sec
Limit 300000 2,34 sec
Limit 350000 2,71 sec
Limit 400000 3,13 sec
Limit 450000 3,44 sec
Limit 500000 3,81 sec


The same for PostgreSQL.

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

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

Michal