Firebird database grows from 107 MB to 10.38 GB with queries alone

[Luciano Rodrigues Nunes Mendes]

Hi Guys,

I have the following Stored Procedure in a Firebird 3.0.11 database where its function is to convert a query in multiple tables (TestCases, Commands, Params) into a Blob Text returning an XML that represents the test case with its commands and parameters. This Stored Procedure is basically only queries. The issue is that if the test case is relatively large (more than 2500 commands) for some reason the database grows from 107MB to 10.38GB. This growth is not due to the addition of data as a backup and restore of the base returns it to the previous 107MB. Could you tell me what is happening and what I can do to avoid it?
The Blob Text that the database returns for this test case example is not that big: 1.16MB.

I have attached an example of a test case with a few commands,TestCase_45.xml, so that you can better understand the output format of this stored procedure.

ALTER PROCEDURE M_TESTCASES_EXPORT (USER_ID TYPE OF COLUMN USERS.ID,
ID TYPE OF COLUMN TESTCASES.ID)
RETURNS (XML BLOB CHARACTER SET WIN1252)
AS
DECLARE V_COMMAND TYPE OF COLUMN COMMANDS.COMMAND;
DECLARE V_TEST_CASE_COMMAND_ID TYPE OF COLUMN TESTCASECOMMANDS.ID;
BEGIN
SELECT '<TestCase><Id>'||ID||'</Id><Name><![CDATA['||TEST_CASE||']]></Name><Description>'||COALESCE('<![CDATA['||DESCRIPTION||']]>','')||'</Description><Commands>' FROM TESTCASES WHERE TESTCASES.ID=:ID AND TESTCASES.TEAM_ID=M_USER_TEAM_ID(:USER_ID) AND TESTCASES.VISIBLE INTO :XML;
IF (XML IS NOT NULL)
  THEN BEGIN
       FOR SELECT ID,COMMAND,:XML||'<Command><Name>'||COMMAND||'</Name><Description>'||COALESCE('<![CDATA['||DESCRIPTION||']]>','')||'</Description><Params>' FROM M_TESTCASECOMMANDS_SEARCH(:USER_ID,:ID) INTO :V_TEST_CASE_COMMAND_ID,:V_COMMAND,:XML DO
         XML=XML||COALESCE((SELECT LIST(PARAM_XML,'') FROM (SELECT '<Param><Name>'||PARAM||'</Name><Value>'||COALESCE('<![CDATA['||PARAM_VALUE||']]>','')||'</Value><Description>'||COALESCE('<![CDATA['||DESCRIPTION||']]>','')||'</Description></Param>' AS PARAM_XML FROM M_TESTCASECOMMANDPARAMS_SEARCH(:USER_ID,:V_TEST_CASE_COMMAND_ID,:V_COMMAND))),'')||'</Params></Command>';
       XML=XML||'</Commands></TestCase>';
       SUSPEND;
       END
END^

BEFORE RUN THE STORED PROCEDURE

AFTER RUN THE STORED PROCEDURE

Thanks in advanced,

Luciano

[Franky Brandt]

Not sure about this at all but i would set the XML variable to NULL before the SELECT statement.

Op dinsdag 30 april 2024 om 20:32:51 UTC+2 schreef luro...@gmail.com:

[Dimitry Sibiryakov]

Luciano Rodrigues Nunes Mendes wrote 30.04.2024 20:32:
> Could you tell me what is happening and what I can do to avoid it?

Your procedure creates a lot of exponentially growing temporary BLOBs. They
are resided in the database blowing it up.
With Firebird 3 you have two choices:

1) Use function LIST() instead of your procedure.
2) Output separate strings and perform final concatenation on client.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

Thank you very much for your prompt reply!

Output separate strings and perform final concatenation on the client. I think it wouldn't be a good solution for high latency networking, right?

You mentioned these would be the solutions for Firebird 3. Are there other better solutions for Firebird 4 or 5?

thanks in advance,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/94278d76-e72f-41ef-9120-1f5ebc2c633e%40ibphoenix.com.

[Dimitry Sibiryakov]

Luciano Rodrigues Nunes Mendes wrote 30.04.2024 23:10:
> Output separate strings and perform final concatenation on the client. I think
> it wouldn't be a good solution for high latency networking, right?

Wrong. BLOBs are known to have bad performance on high latency network.
Strings win.

> You mentioned these would be the solutions for Firebird 3. Are there other better solutions for Firebird 4 or 5?

Yes: BLOB_APPEND function was made to workaround exactly this problem.

--
WBR, SD.

[Luciano Rodrigues Nunes Mendes]

Hi Dimitry,

I updated the database to Firebird 4.0 and using the BLOB_APPEND function the issue simply disappeared!

Once again, thank you very much for your support.

Best Regards,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/8650e2df-8921-41b6-850e-b8cce5885aec%40ibphoenix.com.