I'm wondering if it would be possible to make the PSQL compiler a little
bit more intelligent and do such optimizations automatically.
Mark
On 23-08-2020 03:39, Adriano dos Santos Fernandes wrote:
I have created RDB$BLOB_UTIL package pull request:
https://github.com/FirebirdSQL/firebird/pull/281
Here is a test comparing how problematic is the current approach (which
has exponential time complexity and space storage) of creating blobs in
PSQL with the RDB$BLOB_UTIL approach.
Current:
------
Takes 0m12,772s and resulting database has 117334016 size
isql -term !
create database 'b.fdb'!
create table t (b blob sub_type binary)!
execute block
as
declare b blob;
declare s varbinary(10000) = '0';
declare i integer;
begin
i = 1;
while (i < 10000)
do
begin
s = s || '0';
i = i + 1;
end
b = s;
i = 1;
while (i < 150)
do
begin
b = b || s;
i = i + 1;
end
insert into t (b) values (:b);
end!
commit!
------
RDB$BLOB_UTIL:
------
Takes 0m0,502s and resulting database has 3383296 size.
isql -term ! -ch utf8
create database 'b.fdb'!
create table t (b blob sub_type binary)!
execute block
as
declare bh integer;
declare b blob;
declare s varbinary(10000) = '0';
declare i integer;
begin
bh = rdb$blob_util.new(false, false);
i = 1;
while (i < 10000)
do
begin
s = s || '0';
i = i + 1;
end
i = 0;
while (i < 150)
do
begin
execute procedure rdb$blob_util.append(bh, s);
i = i + 1;
end
insert into t (b) values (rdb$blob_util.make_blob(:bh));
end!
commit!
------
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
