Strange Stored Proc Behaviour with Blob Blob Variables
------------------------------------------------------
Key: CORE-5114
URL: http://tracker.firebirdsql.org/browse/CORE-5114
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.5, 3.0 RC1
Environment: Windows 10, fb255x64, fb30rc1x64
Reporter: Holger Klemt
Attachments: db1.fbk
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 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
