Faster execution of indexed STARTING WITH with UNICODE collation

[asfernandes]

Initial execution time of indexed STARTING WITH lookup with UNICODE collation is very slow.

Test cases with timings using the debug build:

-- UTF8 without collation

recreate table t1 (c1 varchar(10) character set utf8)!
create index t1_idx on t1 (c1)!

execute block
as
    declare n integer = 0;
    declare v type of column t1.c1;
begin
    while (n < 100000)
    do
    begin
        select 1 from t1 where c1 starting with 'x' into v;
        n = n + 1;
    end
end!

-- Elapsed time = 0.422 sec

-- WIN1252 collation WIN_PTBR

recreate table t1 (c1 varchar(10) character set win1252 collate win_ptbr)!
create index t1_idx on t1 (c1)!

execute block
as
    declare n integer = 0;
    declare v type of column t1.c1;
begin
    while (n < 100000)
    do
    begin
        select 1 from t1 where c1 starting with 'x' into v;
        n = n + 1;
    end
end!

-- Elapsed time = 0.440 sec

-- UTF8 collation UNICODE

recreate table t1 (c1 varchar(10) character set utf8 collate unicode)!
create index t1_idx on t1 (c1)!

execute block
as
    declare n integer = 0;
    declare v type of column t1.c1;
begin
    while (n < 100000)
    do
    begin
        select 1 from t1 where c1 starting with 'x' into v;
        n = n + 1;
    end
end!

-- Elapsed time = 6.498 sec

[hvlad]

Adriano, could you look at https://groups.google.com/g/firebird-support/c/VCXnWp0IZVw ?
It looks like another incarnation of this issue.

[asfernandes]

Adriano, could you look at https://groups.google.com/g/firebird-support/c/VCXnWp0IZVw ?
It looks like another incarnation of this issue.

This does not happen only with UTF8/UNICODE.

The problem with some characters is that they are part (start of) contractions that generate sort keys to order them in different place.

So the last character of a key that is the start of a contraction must be excluded from the key, otherwise the lookup will not work.

This issue is about how to verify that contractions faster than before.

[javihonza]

Hi,
unfortunately this fix did not solve the speed problem
https://groups.google.com/g/firebird-support/c/VCXnWp0IZVw