Hi all,

pretty strange this one ...

Using Firebird v1.0 Final and different client apps like
InterBase Workbench, QuickDesk, IB Export and IBO based
applications. Connecting to a local database without using
the TCP/IP loop back.

I have the following two tables.

/* OPERATIONLOG: IBLM$OPERATIONLOG */
CREATE TABLE IBLM$OPERATIONLOG (
ID INTEGER NOT NULL,
DATUMUHRZEIT TIMESTAMP NOT NULL,
BENUTZER VARCHAR(15) NOT NULL,
TABELLE VARCHAR(31) NOT NULL,
OPERATION VARCHAR(6) NOT NULL,
PKEY1 VARCHAR(31),
PKEY1_VALUE VARCHAR(20),
PKEY2 VARCHAR(31),
PKEY2_VALUE VARCHAR(20),
PKEY3 VARCHAR(31),
PKEY3_VALUE VARCHAR(20),
PKEY4 VARCHAR(31),
PKEY4_VALUE VARCHAR(20),
PKEY5 VARCHAR(31),
PKEY5_VALUE VARCHAR(20),
CONSTRAINT PK_IBLM$OPERATIONLOG PRIMARY KEY (ID));
COMMIT WORK;


/* COLUMNLOG: IBLM$COLUMNLOG */
CREATE TABLE IBLM$COLUMNLOG (
ID INTEGER NOT NULL,
LOGTABLE_ID INTEGER NOT NULL,
SPALTE VARCHAR(31),
ALTERWERT VARCHAR(255),
NEUERWERT VARCHAR(255),
ALTERWERTBLOB BLOB SUB_TYPE 0,
NEUERWERTBLOB BLOB SUB_TYPE 0,
CONSTRAINT PK_IBLM$COLUMNLOG PRIMARY KEY (ID),
CONSTRAINT FK_IBLM$COLUMNLOG FOREIGN KEY (LOGTABLE_ID) REFERENCES IBLM$OPERATIONLOG(ID) ON DELETE CASCADE);
COMMIT WORK;


IBLM$OPERATIONLOG has 141.023 records.
IBLM$COLUMNLOG has 1.833.299 records.

For export purposes, I have to transfer records for the given
query to the client application.

select id from iblm$columnlog
where
exists(
select id from iblm$operationlog
where iblm$operationlog.id=iblm$columnlog.logtable_id and iblm$operationlog.ID<10000
)

The last condition iblm$operationlog.ID<10000 is a bit misleading in this context,
because the whole statement can be built in a different way then, but the last part
is dynamically, so it can be another condition on IBLM$OPERATIONLOG as well.

Using EXISTS instead of IN makes the query pretty fast. Fetching now all rows
to the client application (to export them into different formats), the CPU
utilizitation for both, the client application and the ibserver.exe process
is about 45% for each. But after approx. 115.000 fetched rows, the CPU
utilizitation for the client application falls to 0% and for the ibserver.exe
to 99%. The only way to bring down the ibserver.exe process is to kill
the client application.

This happens for different client applications.


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