Hi again,

I have the issue below now solved with using a JOIN instead
of EXISTS. This works for me now (don't ask me why), and I
can fetch any number of records from the client application.
Just to let you all know ;-).


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

> -----Original Message-----
> From: Thomas Steinmaurer [mailto:ts@...]
> Sent: Friday, November 08, 2002 12:22 AM
> To: Ib-Support
> Subject: [ib-support] Fetching records and CPU utilization
>
>
> 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
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>