Serious TZTable problem with (possible) soullution

[ccezar]

Hello,

While playing a bit with ZeosDBO 6.6.1 I've found something which really hurt performance. Steps to reproduce problem:

1) put TZConnetion, TZTable and DataSource components on the form
2) configure for your database (in my example it's called 'newsy') - do NOT connect any dataware components!
3) TZConnection.Connected := true; TZTable.Active := true;
4) ZeosDBO will generate the following queries (trace from TZSQLMonitor):

Code: Select all

2007-09-13 16:46:05 cat: Other, msg: Create, errcode: 38, error: 
2007-09-13 16:46:05 cat: Connect, proto: postgresql-8, msg: CONNECT TO "docdb" AS USER "docdb"
2007-09-13 16:46:17 cat: Execute, proto: postgresql-8, msg: SELECT * FROM newsy

2007-09-13 16:46:17 cat: Execute, proto: postgresql-8, msg: SELECT version()
2007-09-13 16:46:17 cat: Execute, proto: postgresql-8, msg: SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,def.adsrc,dsc.description  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relname LIKE 'newsy' AND a.attname LIKE '%' ORDER BY nspname,relname,attnum 
2007-09-13 16:46:17 cat: Execute, proto: postgresql-8, msg: SELECT oid, typname, typbasetype FROM pg_type WHERE oid<10000 OR typbasetype<>0 ORDER BY oid

the real problem is third line - 'select * from newsy'. It extremally expensive! In my example 'newsy' table contains two BYTEA fields filled with large portions of data - so in effect activation time for TZTable is counted in tens of seconds (using 1GB link to DB server).

Possible sollution: adding 'LIMIT 1' to the query.

Cezar

[btrewern]

Cezar,

Other possible solution: don't use TZTable.

I've always been told not to use TTable or similar components when using RDBMSs for just this very reason. It may be that some TDataset descendants use a cursor or similar to page through the data returned from the server, but Zeos has never had this functionality.

It would be especially nice for Zeos to not load large blobs from a table and only load them on demand, but I don't think there is anything like that currently.

Regards,

Ben

[ccezar]

Jeah, sure! But I'm migrating to Zeos quite big BDE based application (162.000 lines of code) and in few places it uses TTable, mainly for data insertion. I'm planning migration to pure-sql, but as for now I must have it up and running ASAP.

[zippo]

Try to change the TTable ASAP after setting up the application. I had the same problem and after digging a little bit on the code I had a massife speedup - i mean REALLY extreme. I suggest you to take some time on this..

[btrewern]

If you just want to insert into a table use a TZQuery with a

Code: Select all

select * from newsy WHERE 1 = 0;

as your SQL. You don't even download one record and it works with any server.

Regards,

Ben