Commented by: Grzegorz Skoczylas (gskoczylas)

The test database with test data imported into it.

Commented by: @asfernandes

This is because UPPER(blob) creates a temporary blob on the database. Temporary blobs could be created on temp space too.

If temporary blobs are switched to temp space, an operation like "update t1 set p2 = upper(p2)" will be slow.

Commented by: Grzegorz Skoczylas (gskoczylas)

OK, I understand that, but:
- the initial database size is 5,4 MB
- after first execution of above SQL statement the database size is 6,9 MB (size is increased because of temporary blobs)
- transaction commited, next SQL executed again: dabase size is 8,8 MB (why? Whether the server should not reuse the memory of the previous questions?)
- transaction commited, SQL executed once more: database size is 9,9 MB
- These repeated actions continue to cause further growth of the database file: 11,8 MB, 13,4 MB, 15,1 MB, 16 MB, 18,1 MB, 19,2 MB, etc.

After every execution of above SQL statement the database file size is growing. It seems that the server is never used again working memory from the previous query, even if the transaction is commited.

Commented by: @hvlad

Are you sure you have no stuck OST ?

Commented by: Grzegorz Skoczylas (gskoczylas)

I have no idea what does it means "stuck OST". I'm sorry.

I have Firebird 2.5 RC2 SuperServer (standard installation). Both Firebird and client application are on the same computer, Windows Vista Business. Tested both with Local protocol and with remote TCP protocol (host=127.0.0.1).

I'm using IBExpert Personal for testing, but if I'm using own simple application written with Delphi 2007, using InterBase Express components, the database file is growing too. With ISQL (local protocol) - the database file is growing too.

The InterBase has been never installed in my OS.

The test database and description of the problem have been sent to me by the new user of the Firebird with request for help. He is using the Firebird 2.1.3 SuperServer.

Commented by: @hvlad

Reproduced and investigating

We have created a UDF functions which resolves the problem, really much faster, eg:

function CiContains(const AText, ASubText: PAnsiChar): Integer; cdecl;
{ declare external function CiContains
cstring(254), cstring(254)
RETURNS Integer by value
ENTRY_POINT 'CiContains' module_name 'LoyaltyIbxFunctions';
// example|: select CiContains('Mulder', 'mul') from rdb$database
}

implementation

function CiContains(const AText, ASubText: PAnsiChar): Integer; cdecl;
begin
if ContainsText(AText, ASubText) then
Result := 1
else
Result := 0;
end;

Albert Mulder,
Loyaltygroup
The Netherlands