You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
dyemanov
changed the title
Implementation of SUBSTRING for UTF8 character set is inefficient [CORE6542]
More efficient implementation of SUBSTRING for UTF8 character set [CORE6542]
Nov 8, 2021
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
The text was updated successfully, but these errors were encountered: