Tim,

The procedure shown after my signature is extremely slow. I killed EDB
Manager after 15 minutes and only 14000 rows had been inserted.

Ole Willy Tuv

create procedure sp_ATable
(
)
begin
 declare ts timestamp;
 declare i integer;
 declare stmt statement;

 set ts = current_timestamp;

 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''ATABLE''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'drop table ATable';
 end if;
 execute immediate
 '
   create table ATable
   (
     ID integer generated by default as identity not null,
     SField1 varchar(20) collate enu default ''SField1 row ''||cast(ID as
varchar(7)),
     SField2 varchar(20) collate enu default ''SField2 row ''||cast(ID as
varchar(7)),
     SField3 varchar(20) collate enu default ''SField3 row ''||cast(ID as
varchar(7)),
     SField4 varchar(20) collate enu default ''SField4 row ''||cast(ID as
varchar(7)),
     SField5 varchar(20) collate enu default ''SField5 row ''||cast(ID as
varchar(7)),
     SField6 varchar(20) collate enu default ''SField6 row ''||cast(ID as
varchar(7)),
     SField7 varchar(20) collate enu default ''SField7 row ''||cast(ID as
varchar(7)),
     SField8 varchar(20) collate enu default ''SField8 row ''||cast(ID as
varchar(7)),
     Stamp timestamp,
     constraint pk_ATable primary key (ID)
   )
 ';
 execute immediate 'create index ix_ATable_SField1 on ATable (SField1)';

 prepare stmt from 'insert into ATable (Stamp) values(?)';
 set i = 0;
 repeat
   execute stmt using ts;
   set i = i+1;
 until i = 10
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 10
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 100
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 1000
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 10000
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 unprepare stmt;
end

Tim,

<< I killed EDB Manager after 15 minutes and only 14000 rows had been
inserted. >>

Executing the REPEAT blocks in the context of a transaction doesn't seem to
help much.

However, executing plain INSERT statements within transaction blocks is
pretty fast. The following procedure inserts 100000 rows in 12 seconds using
a commit interval of 10000:

create procedure sp_ATable
(
 inout rows integer,
 in commit_interval integer
)
begin
 declare ts timestamp default current_timestamp;
 declare rc integer default 0;
 declare i integer;
 declare stmt statement;

 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''ATABLE''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'drop table ATable';
 end if;
 execute immediate
 '
   create table ATable
   (
     ID integer generated by default as identity not null,
     SField1 varchar(20) collate enu default ''SField1 row ''||cast(ID as
varchar),
     SField2 varchar(20) collate enu default ''SField2 row ''||cast(ID as
varchar),
     SField3 varchar(20) collate enu default ''SField3 row ''||cast(ID as
varchar),
     SField4 varchar(20) collate enu default ''SField4 row ''||cast(ID as
varchar),
     SField5 varchar(20) collate enu default ''SField5 row ''||cast(ID as
varchar),
     SField6 varchar(20) collate enu default ''SField6 row ''||cast(ID as
varchar),
     SField7 varchar(20) collate enu default ''SField7 row ''||cast(ID as
varchar),
     SField8 varchar(20) collate enu default ''SField8 row ''||cast(ID as
varchar),
     Stamp timestamp,
     constraint pk_ATable primary key (ID)
   )
 ';
 execute immediate 'create index ix_ATable_SField1 on ATable (SField1)';

 prepare stmt from 'insert into ATable (Stamp) values(?)';

 while (rc < rows) do
   set i = 0;
   start transaction;
   repeat
     execute stmt using ts;
     set i = i+1;
   until i = commit_interval
   end repeat;
   commit;
   set rc = rc+i;
 end while;

 unprepare stmt;
 set rows = rc;
end

Ole Willy Tuv

Ole,

<< The procedure shown after my signature is extremely slow. I killed EDB
Manager after 15 minutes and only 14000 rows had been inserted. >>

Most likely the change detection is kicking in for every insert.  I'll see
what I can find.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

Okay, the issue was that the INSERT was using an insensitive (canned) query
for the sub-query, which was taking a long time.  This has been fixed for
1.03 B2.

It takes around 12 secs here with the fix.

--
Tim Young
Elevate Software
www.elevatesoft.com