> using rc9 on winxp
>
> database has around 120 tables, all with pk on ID column.
> database size is around 130 MB
> table sstruk00 has around 240000 records
> and in the field bez, there is typically the name of the referenced table
> (except the two zeros)
>
> it works fine, but for running the procedure it takes around 500MB of

memory

> in the fbserver.exe process (buffers are set to 2000, pagesize 8k)
>
> result is around 100 records who have no table or missing ID in detail
> table, but the memory is limited and we will get more entries in sstruk00
> each day.
>
>
> the procedure checks if all tables and Ids do exist.
>
>
> CREATE PROCEDURE SP2
> RETURNS (
> T varCHAR(8),
> ID DOUBLE PRECISION)
> AS
> declare variable sql varchar(200);
> declare variable ex integer;
> declare variable anz integer;
> begin
> for
> select ID,bez
> from sstruk00 s
> where id>0
> into :id,:t
> do
> begin
> anz=-1;
> ex=-1;
> t=t||'00';
>
> sql='select count(*) from rdb$relations WHERE
> RDB$RELATION_NAME='''||t||'''';
> execute statement :sql into :ex;
> if (ex>0) then
> begin
> sql='select count(*) from '||t||' where ID='||cast(:id as

char(15));

> execute statement :sql into :anz;
> end
> if (ex=0) then
> suspend;
> else
> if (anz=0) then
> suspend;
> end
> end
>
>
>
>
>
> Query Time
> ------------------------------------------------
> Prepare : 50,00 ms
> Execute : 315.925,00 ms
> Avg fetch time: 315.925,00 ms
>
> Memory
> ------------------------------------------------
> Current: 509.643.052
> Max : 509.729.468
> Buffers: 2.048
>
> Operations
> ------------------------------------------------
> Read : 6.031
> Writes : 3
> Fetches: 3.397.277
>
>
> Enchanced Info:
>

+--------------------------+-----------+-----------+---------+---------+----

> -----+
> | Table Name | Index | Non-Index | Updates | Deletes |
> Inserts |
> | | reads | reads | | |
> |
>

+--------------------------+-----------+-----------+---------+---------+----

> -----+
> | SADRES00| 39400 | 0 | 0 | 0 |
> 0 |
> | SANMRK00| 6059 | 0 | 0 | 0 |
> 0 |
> | SANMTY00| 7 | 0 | 0 | 0 |
> 0 |
> | SANRED00| 8 | 0 | 0 | 0 |
> 0 |
> | SANSPR00| 1316 | 0 | 0 | 0 |
> 0 |
> | SARTGR00| 29 | 0 | 0 | 0 |
> 0 |
> | SARTGT00| 3 | 0 | 0 | 0 |
> 0 |
> | SARTMA00| 6 | 0 | 0 | 0 |
> 0 |
> | SARTST00| 5943 | 0 | 0 | 0 |
> 0 |
> | SARTTW00| 9 | 0 | 0 | 0 |
> 0 |
> | SARTVK00| 6 | 0 | 0 | 0 |
> 0 |
> | SAUTGR00| 21404 | 0 | 0 | 0 |
> 0 |
> | SAWDTY00| 3 | 0 | 0 | 0 |
> 0 |
> | SBASIS00| 3 | 0 | 0 | 0 |
> 0 |
> | SBCKVF00| 1 | 0 | 0 | 0 |
> 0 |
> | SBESCB00| 1 | 0 | 0 | 0 |
> 0 |
> | SBNKVB00| 710 | 0 | 0 | 0 |
> 0 |
> | SCLAND00| 96 | 0 | 0 | 0 |
> 0 |
> | SDRFRM00| 5 | 0 | 0 | 0 |
> 0 |
> | SEMAIL00| 658 | 0 | 0 | 0 |
> 0 |
> | SFDAGL00| 2 | 0 | 0 | 0 |
> 0 |
> | SFIRMA00| 1 | 0 | 0 | 0 |
> 0 |
> | SGBTYP00| 5 | 0 | 0 | 0 |
> 0 |
> | SGEBIE00| 237 | 0 | 0 | 0 |
> 0 |
> | SKURZB00| 2 | 0 | 0 | 0 |
> 0 |
> | SLAGER00| 500 | 0 | 0 | 0 |
> 0 |
> ...
>
>
>
> --
> Best Regards
>
> Holger Klemt
>
> CeBIT Hall 3/B19/1 March 18th - 24th Hannover, Germany
> 2. European Firebird Conference, May 16th - 18th, Fulda,Germany
>
> The most Expert for InterBase and Firebird --- www.ibexpert.com
> HK Software - Huntestrasse 15 - 26135 Oldenburg - Germany
> Phone/Fax +49 700 IBEXPERT (42397378) www.h-k.de
> Training and Support for Delphi, InterBase, Firebird, AS/400
>
>