Hi,

I'm looking for the best approach regarding speed vs. memory
consumption for the following thing.

I have a table storing metadata information, which looks like:

CREATE TABLE IBLM$COLUMNLOGDEF (
TABLE_NAME VARCHAR(31) NOT NULL,
OPERATION VARCHAR(1) NOT NULL,
COLUMN_NAME VARCHAR(31) NOT NULL,
CONSTRAINT PK_IBLM$COLUMNLOGDEF PRIMARY KEY (TABLE_NAME, OPERATION, COLUMN_NAME)
);

A record usually looks like:

TABLE_NAME OPERATION COLUMN_NAME
--------------------------------------------------------------
TABLE1 D ID
TABLE1 I ID
TABLE1 U ID

I'm building a hierarchical view of all tables, the three operations
DELETE/INSERT/UPDATE for each table and the table columns under each
of the "operation node".

During the creation of the hierarchical treeview, I have to check
for each column and the corresponding operation whether a record
in the above table exists or not.

What's the best way to do that regarding speed, network traffic
and memory consumption on the client (the result set of the above
table can be pretty huge)?

Probably there are different possibilities available:

1) Using a parametrized query and executing each time something
like:

SELECT COUNT(*) AS COLUMN_EXISTS FROM METATABLE
WHERE TABLE_NAME = ? AND OPERATION = ? AND COLUMN_NAME = ?

2) Using a buffered TIB_Query and fetch all records from the
above table to the client und use a Locate() to check the existance
of a record

3) Or any other IBO smarties I'm not aware of ;-)

Any comments/hints appreciated.


Thank you,
Thomas Steinmaurer
IB LogManager 2.1 - The Logging/Auditing Tool for InterBase and Firebird
http://www.iblogmanager.com