set term ^;
create or alter procedure my_test as begin end^ -- kill dependencies if any
set term ;^
commit;
recreate table
t1(c1 int not null, c2 int not null, c3 int not null, c4 int not null,
    primary key (c1, c4)
  );
create index t1i1 on t1(c1, c3, c2);
recreate global temporary table
  t2 (c1 int not null, c2 int not null, c3 int not null)
  on commit delete rows;
commit;
------------------
set term ^;
execute block as
declare i int= 1000 ;
declare j int;
begin
  while (i> 0 ) do begin
    j= 20 ;
    while (j> 0 ) do begin
      insert into t1(c1, c2, c3, c4)
      values(:i, round( 1000 *rand(), 0 ), round( 1000 *rand(), 0 ), :j);
      j=j- 1 ;
    end
    i=i- 1 ;
  end
end^
set term ;^
commit; -- 20'000 rows = 891 ms
--------------------
set term ^;
create or alter procedure my_test returns(t2_count int)
as
  declare p int;
  declare i int;
  declare j int;
  declare cur_c2 type of column t1.c2;
begin
  delete from t2;
  p = round( 1000  * rand(),  0 );
  i =  1000000 ;
  while (i> 0 ) do
  begin
    j=round( 1000 *rand(), 0 );
    for
      select first  10  c2
      from t1
      where c1 = :p
      order by abs(c3 - :j) desc
      into cur_c2
    do
    begin
      insert into t2 (c1, c2, c3) values(:p, :cur_c2, :j);
      if (cur_c2<>p) then
      begin
        p=cur_c2;
        if (rand()> 0 . 4 ) then
          leave;
      end
    end
    i=i- 1 ;
  end
  t2_count=(select count(*) from t2);
  suspend;
/* initial code:
  set @p = round(1000 * rand(), 0);
  set @i = 1000000;
  while @i > 0 loop
    set @j = round(1000 * rand(), 0);
  
lbl_each_t:
    for each_t as cur_t no scroll cursor for
          select top 10 c2 as @cur_c2
      from t1
      where c1 = @p
      order by abs(c3 - @j) desc
      do
      insert into t2 (c1, c2, c3)  values (@p, @cur_c2, @j);
      if @cur_c2 != @p then
        set @p = @cur_c2;
        if rand() > 0.4 then
          leave lbl_each_t;
        end if;
      end if;
    end for;
    set @i = @i - 1;
  end loop;
*/
end^
set term ;^
commit;

C:\1INSTALL\FIREBIRD\Data>isql -n  localhost/3050:C:\1INSTALL\FIREBIRD\Data\TESTX.fdb
Database:  localhost/3050:C:\1INSTALL\FIREBIRD\Data\TESTX.fdb
SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
        2053

Current memory = 35657984
Delta memory = 123484
Max memory = 35787776
Elapsed time= 28.32 sec
Buffers = 8192
Reads = 406
Writes 35
Fetches = 3061176
SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
        1920

Current memory = 35657652
Delta memory = 8580
Max memory = 35788484
Elapsed time= 27.01 sec
Buffers = 8192
Reads = 4
Writes 28
Fetches = 3056873
SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
         703

Current memory = 35654632
Delta memory = 5560
Max memory = 35788484
Elapsed time= 27.28 sec
Buffers = 8192
Reads = 0
Writes 11
Fetches = 3020616
SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
       10047

Current memory = 35657228
Delta memory = 8156
Max memory = 35788484
Elapsed time= 26.77 sec
Buffers = 8192
Reads = 0
Writes 15
Fetches = 3030428

SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
        3359

Current memory = 35658076
Delta memory = 9004
Max memory = 35788908
Elapsed time= 27.07 sec
Buffers = 8192
Reads = 0
Writes 48
Fetches = 3098956
SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
       13337

Current memory = 35654632
Delta memory = 5560
Max memory = 35788908
Elapsed time= 28.41 sec
Buffers = 8192
Reads = 0
Writes 5
Fetches = 3009588
SQL> set stat on; execute procedure my_test; set stat off; commit;

    T2_COUNT
============
        2100

Current memory = 35657668
Delta memory = 8580
Max memory = 35788908
Elapsed time= 28.22 sec
Buffers = 8192
Reads = 0
Writes 30
Fetches = 3059821