Database file grows when Blob variables used in Stored Procedure [CORE5114] #5398

firebird-automations · 2016-02-23T21:24:29Z

Submitted by: Holger Klemt (klemmo)

Attachments:
db1.fbk

Votes: 3

Today, I demonstrated to some customer on our Firbeird softwaretraining held in munich some technologies they should use to improve their work and some they should avoid. I know that using blob as variables or params inside a stored procedure have some really big disadvantages, but i was not aware of some part of the result.

the facts: We used the ibexpert demo database and created 10000 records as testdata in table products. To demonstrate the bad way, we added this stored proc:
using blob params and manipulate them for every found record. This is slow and needs a lot of memory, basic reasons for this problems are known from my side. Procedure execution time was around 45 seconds even not a single record should be written to disk.

The really bad thing that right after executing this stored proc, the database filesize changed from about 25MB to more than 3GB. The SP does not a single update or insert .... You can also see from statistics that the engine wrote about 190000 pages.

create or alter procedure GETPRODUCTS
returns (
RES blob sub_type 1 segment size 80)
as
declare variable ACTOR blob sub_type 1 segment size 80;
begin
res='';
for
select product.actor
from product
into :actor
do
begin
res=res||'
'||actor;
end
suspend;
end

------------------------------------------------
Prepare : 16,00 ms
Execute : 44.906,00 ms
Avg fetch time: 44.906,00 ms

Memory
------------------------------------------------
Current: 96.756.864
Max : 106.806.792
Buffers: 5.000

Operations
------------------------------------------------
Read : 85
Writes : 189.558
Fetches: 799.256
Marks : 583.503

Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads | | | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT | 0 | 0 | 10000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Workaround for users

This is a procedure i demonstrated as a workaround, which does basically the same, but collects the data using varchar variables as log as it fits and only copy them to the result when there is no more free space in varchar. The execution takes only 350 ms and does not affect the size of the database, since it does not a single page write (as expected).

Basically the first SP is definitively a valid way to write an SP, but there must be something very strange going on inside Firebird.

The test was made with a firebird 255 on windows 64 and also with firebird 3 rc1, both had exactly the same strange behaviour extending the size of the database to 3GB without any real acceptable reason.

create or alter procedure GETPRODUCTS
returns (
RES blob sub_type 1 segment size 80)
as
declare variable ACTOR varchar(200);
declare variable Z varchar(32000);
begin
res='';
z='';
for
select product.actor
from product
into :actor
do
begin
z=z||'
'||actor;
if (char_length(z)>31000) then
begin
res=res||z;
z='';
end
end
if (z<>'') then
begin
res=res||z;
z='';
end
suspend;
end

Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 359,00 ms
Avg fetch time: 359,00 ms

Memory
------------------------------------------------
Current: 87.044.576
Max : 87.230.256
Buffers: 5.000

Operations
------------------------------------------------
Read : 79
Writes : 0
Fetches: 20.751
Marks : 365

Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads | | | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT | 0 | 0 | 10000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

firebird-automations · 2016-02-23T21:25:50Z

Commented by: Holger Klemt (klemmo)

attached the fb25 backup file i used for the test

firebird-automations · 2016-02-23T21:25:50Z

Modified by: Holger Klemt (klemmo)

Attachment: db1.fbk [ 12905 ]

firebird-automations · 2016-02-24T01:24:29Z

Commented by: Sean Leyne (seanleyne)

Holger,

IIRC, blobs don't really have an in-memory only representation, they always have an on-disk footprint. Further, when a blob is revised, a new object is actually allocated, the existing object is never re-used. The result is that each intermediate blob which is created, currently, is written to disk. Which is why the database size explodes.

The 2nd SP does not change the database size, since no single return value ever got larger than VARCHAR(32000), so no intermediate Blob page was created. (the engine recognized that a VARCHAR converted to a blob does not need intermediate storage since it has a max size)

firebird-automations · 2016-02-24T01:28:37Z

Modified by: Sean Leyne (seanleyne)

description: today i demonstrated to some customer on our Firbeird softwaretraining held in munich some technologies they should use to improve their work and some they should avoid.
I know that using blob as variables or params inside a stored procedure have some really big disadvantages, but i was not aware of some part of the result.

the facts: We used the ibexpert demo database and created 10000 records as testdata in table products. To demonstrate the bad way, we added this stored proc:
using blob params and manipulate them for every found record. This is slow and needs a lot of memry, basic reasons for this problems are know from my side
Procedure execution time was around 45 seconds even not a single record should be written to disk.

The really bad thing that right after executing this stored proc, the database filesize changed from about 25MB to more than 3GB. The SP does not a single
update or insert .... You can also see from statistics that firebird wrote about 190000 pages.

create or alter procedure GETPRODUCTS
returns (
RES blob sub_type 1 segment size 80)
as
declare variable ACTOR blob sub_type 1 segment size 80;
begin
res='';
for
select product.actor
from product
into :actor
do
begin
res=res||'
'||actor;
end
suspend;
end

------------------------------------------------
Prepare : 16,00 ms
Execute : 44.906,00 ms
Avg fetch time: 44.906,00 ms

Memory
------------------------------------------------
Current: 96.756.864
Max : 106.806.792
Buffers: 5.000

Operations
------------------------------------------------
Read : 85
Writes : 189.558
Fetches: 799.256
Marks : 583.503

Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads | | | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT | 0 | 0 | 10000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

Workaround for users

this is a procedure i demonstrated as a workaround, which does basically the same, but collects the data
using varchar variables as log as it fits and only copy them to the result when there is no more free space in
varchar. The execution takes only 350 ms and does not affect the size of the database, since it does not a single
page write (as expected).

basically the first stored proc is definitly a valid way to write a sp, but there must be something very strange
going on inside Firebird.

The test was made with a firebird 255 on windows 64 and also with firebird 3 rc1, both had exactly the same
strange behaviour extending the size of the database to 3GB without any real acceptable reason.

create or alter procedure GETPRODUCTS
returns (
RES blob sub_type 1 segment size 80)
as
declare variable ACTOR varchar(200);
declare variable Z varchar(32000);
begin
res='';
z='';
for
select product.actor
from product
into :actor
do
begin
z=z||'
'||actor;
if (char_length(z)>31000) then
begin
res=res||z;
z='';
end
end
if (z<>'') then
begin
res=res||z;
z='';
end
suspend;
end

Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 359,00 ms
Avg fetch time: 359,00 ms

Memory
------------------------------------------------
Current: 87.044.576
Max : 87.230.256
Buffers: 5.000

Operations
------------------------------------------------
Read : 79
Writes : 0
Fetches: 20.751
Marks : 365

Enchanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads | | | | | | |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|PRODUCT | 0 | 0 | 10000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

=>