Subject [firebird-support] Execution of prepared statements consumes a lot of memory
Author Christian G├╝tter
Hi,

in one of our business apps, we are using a master-detail-detail
report to generate invoices. Generation of the invoices and their
booking all happens within one transaction. As report generator,
we use Fastreport 2.51. In general, we use Firebird 1.50 and
IBObjects 4.3Aa.

The report is generated using four prepared statements (TIB_Cursor)
which only read data, but do not update anything. These statements
are connected using the IBObjects MasterLink functionality.
A fifth prepared statement (dInsertCreditDoc, TIB_DSQL) is used to
save the generated report to a blob.

The problem is that with each execution of one of the four read-only
statements, the memory usage of the server process is increased.
When debugging the application, I made really sure that it is the
execution of these four statements which makes the server process
memory usage increase. Another effect I observed is that the more rows
a table contains, the more memory is used by the read-only statements,
even when the number of resulting rows is the same.

As each read-only statement is executed several hundreds of times,
the server consumes hundreds of megabytes of memory which is only freed
when the transaction commits or rolls back.

I cannot believe that this high memory usage is acceptable.
So I wonder if there is some fault in my logic or if there is a
way doing the same consuming less memory.

I have attached the code which shows the way the four IB_Cursors are
used. If you need more information, please tell me.

Thanks in advance,


Christian




PS:
Here is the code. A name beginning with a small "c" indicates a
IB_Cursor, a name beginning with a small "d" an IB_DSQL.


cBrokerNumbers.Prepare;
cCSBrokers.Prepare;
cCSBrokerPos.Prepare;
cCSBrokerPos.Prepare;
dInsertCreditDoc.Prepare;
dInsertCreditDoc.ParamByName('PA_ID').Value := FCS_ID;
cInfo.Prepare;
cInfo.ParamByName('PA_ID').Value := FCS_ID;
cInfo.First;
cBrokerNumbers.ParamByName('PA_ID').Value := FCS_ID;
cBrokerNumbers.First;
while not cBrokerNumbers.Eof do
begin
cCSBrokers.ParamByName('PA_ID').Value := FCS_ID;
cCSBrokers.ParamByName('Personal_ID').Value :=
cBrokerNumbers.FieldByName('ID').Value;
cCSBrokers.First;
cCSBrokerPos.ParamByName('PA_ID').Value := FCS_ID;
cCSBrokerPos.First;
cCSBrokerPos2.First;
rCSBrokers.PrepareReport;
// Erstellung unkomprimierter Stream und BlobStream
UnCompressedStream := TMemoryStream.Create;
BlobStream := dInsertCreditDoc.CreateBlobStream(
dInsertCreditDoc.ParamByName('Dok'), bsmWrite);
BlobStream.Position := 0;
try
// Save the FR pages
rCSBrokers.EMFPages.SavetoStream(UncompressedStream);
// Komprimierung des unkomprimierten Stream in den Blobstream
UncompressedStream.Position := 0;
ZCompressStream(UnCompressedStream, BlobStream, zcMax);
dInsertCreditDoc.ParamByName('Personal_ID').Value :=
cBrokerNumbers.FieldByName('ID').Value;
dInsertCreditDoc.Execute;
cBrokerNumbers.Next;
// Delete Stream
finally
UncompressedStream.Free;
BlobStream.Free;
end;
end;
finally
cCSBrokers.Close;
cCSBrokerPos.Close;
cCSBrokerPos2.Close;
cInfo.Close;
cBrokerNumbers.Close;
end;