Submitted by: @hvlad

The case below shows bad performance of SUBSTRING for UTF8 comparing with (legacy) UNICODE_FSS

a) UNICODE_FSS

execute block
as
declare str1 varchar(8000) character set unicode_fss;
declare str2 varchar(10) character set unicode_fss;
declare n int = 100000;
begin
str1 = LPAD('abcd', 8000, '--');
while (n > 0) do
begin
str2 = SUBSTRING(str1 from 1 FOR 10);
n = n - 1;
end
end

Execute time = 62ms

b) UTF8

execute block
as
declare str1 varchar(8000) character set utf8;
declare str2 varchar(10) character set utf8;
declare n int = 100000;
begin
str1 = LPAD('abcd', 8000, '--');
while (n > 0) do
begin
str2 = SUBSTRING(str1 from 1 FOR 10);
n = n - 1;
end
end

Execute time = 983ms

The case is simplified and based on end-user report. In user case the same query on the system tables run much longer with FB4 than with FB3
(test database was restored from the same backup). Origin of the problem is that FB4 uses UTF8 for metadata while FB3 uses UNICODE_FSS.

The SUBSTRING implementation for UNICODE_FSS (internal_fss_substring()) is straigthforward and logical - it skips POSITION characters
from the start of the source string first and then copy LENGTH chars into dest string.

The UTF8 implementation (MultiByteCharSet::substring()) convert whole source string into UTF16 and only then get substring of UTF16 string.
This is simple but very inefficient especially for a long strings and small POSITION values.

Commits: 9c566c0 3334128 f1fe0ee ac2532f b5407c3