TableInfoCache & NoTableInfoCache flag

[jaco]

Hi.
By default TableInfoCache is enabled. This casues many additional queries like:

Code: Select all

select pc.relname, pns.nspname, pa.attnum, pa.attname from pg_catalog.pg_class pc join pg_catalog.pg_namespace pns on pc.relnamespace = pns.oid join pg_catalog.pg_attribute pa on pa.attrelid = pc.oid where pc.oid = 44411 and pa.attnum > 0

after first execution of main query.
If main query is complicated (many tables) I have many, many selects from pg_catalog. And it takes long time on slow connection...

Even if TableInfoCache is disabled (NoTableInfoCache=True), data like relname, nspname, attname is loaded by TZAbstractResultSetMetadata.LoadColumns. So I do not understand what is this cache for.

I do not know Delphi and it is hard to analize sources for me. Can you tell what is the benefit of using TableInfoCache? When can I observe it?

Thanks for help.

ZEOS rev. 3628 from branch testing-7.2, Postgresql 9.3

[marsupilami]

Hello :-)

The problem here is that Zeos has two kinds of caches. There is one for the Component level that queries metadata. This is not the TableInfoCache and possibly is used to query metadata even if you disable the TableInfoCache. This probably is the reason for metadata queries even if you disable the TableInfoCache.

Some time ago there was a bug in Zeos where the case of the table name in a query had influence on wether you could write new data to that Query. "select * from xyz" lead to a writable dataset whereas "select * from XYZ" resulted in a read only dataset. To circumvent this problem I added code to the postgresql driver that uses information returned by libpq to fetch the metadata. Since I use PostgreSQL over the internet, I tried to only query metadata when it really is needed - for example when you request a read write dataset. Also I added a cache that would cache the retrieved information so the metadata has to be fetched only once per session.

So my advice for using PostgreSQL on slow lines depends on how your application works. If your database structure only changes rarely and if you want to keep work to a minimum, keep everything set to the default. The TableInfoCache queries the metadata for each table only once per session. Afterwards it has no impact on Performance.
If your database structure changes often during runtime and during the session of your program, disable the TableInfoCache.
If you want the maximum performance, disable the TableInfoCache and also the UseMetadata flag of the TZConnection object. By using this approach, next to no metadata should be queried. All your queries will be read only. If you need an uldateable query, you will have to create a TZUpdateSql-Object for your query, where you have to provide update, insert and delete statements that will be used by Zeos to carry out the work on the database. Be careful becaus this will require you to alwas change these statements if your select statement changes.

With best regards,

Jan