Implementation of SUBSTRING for multi-byte character sets is inefficient
-------------------------------------------------------------------------
Key: CORE-6542
URL: http://tracker.firebirdsql.org/browse/CORE-6542
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 4.0 RC 1, 3.0.7
Reporter: Vlad Khorsun
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
