Increasing memory usage [CORE2904]

[firebird-automations]

Submitted by: Grzegorz Skoczylas (gskoczylas)

Attachments: DATABASE.FDB

Votes: 2

Initial size of the database (167 records imported into empty database): 5,4 MB.

SQL: SELECT * FROM T1 WHERE UPPER(P2) LIKE '%'

After every execution of above SQL statement size of the database file is increasing. Even if I commit transaction after every execution of the SQL statement, the database file is still increasing.

The database has been successfully (fully) validated.

[firebird-automations]

Commented by: Grzegorz Skoczylas (gskoczylas)

The test database with test data imported into it.

[firebird-automations]

Modified by: Grzegorz Skoczylas (gskoczylas)

Attachment: DATABASE.FDB [ 11588 ]

[firebird-automations]

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.

[firebird-automations]

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.

[firebird-automations]

Commented by: @hvlad

Are you sure you have no stuck OST ?

[firebird-automations]

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.

[firebird-automations]

Commented by: @hvlad

Reproduced and investigating

[firebird-automations]

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

[AlbertMulder-dev]

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