Hi Holger!
I have a few comments regarding the code, even though it is not a
thorough answer to your question. You'll find the comments within the
code.

> > 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

This is OK, but if there is an index on id and the vast majority has
an id above 0, then this will waste a little time using the index.

> > 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;

This seems strange to me, what is the reason for using execute
statement in this case?

> > 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

In total, this procedure presumably executes about 480000 queries
created on the fly! I do not know how any housekeeping is done, but I
am not shocked by this procedure demanding lots of memory.

Set
- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation