Using COLLATE UNICODE_CI_AI in WHERE clause (not indexed) is extremely slow
---------------------------------------------------------------------------
Key: CORE-4125
URL: http://tracker.firebirdsql.org/browse/CORE-4125
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.2 Update 1
Environment: Microsoft Windows Server 2008 (Spanish)
Reporter: Toni Martir
Executing a select with UNICODE_CI_AI collation is very slow (4000 rows table ):
SELECT UTF8CL FROM TEST_UTF8 WHERE UTF8CL COLLATE UNICODE_CI_AI LIKE '%8%'
- Using LIKE or CONTAINING have the same effect.
Sorting is fast: SELECT UTF8CL FROM TEST_UTF8 ORDER BY UTF8CL COLLATE
UNICODE_CI_AI (30 miliseconds)
Using UNICODE_CI is also fast SELECT UTF8CL FROM TEST_UTF8 WHERE UTF8CL COLLATE
UNICODE_CI LIKE '%8%'
So accent insensitive Unicode comparisons are extermely slow. Defining the
column as UNICODE_CI_AI has the same problem (but there is no need to specify
COLLATE in WHERE clause).
I don't know if it's related, but creating foreign keys with domains defined as
UNICODE_CI_AI fails (incompatible field types).
I provide script to easily reproduce the bug (create empty database first)
CREATE TABLE TEST_UTF8(UTF8CL VARCHAR(20) CHARACTER SET UTF8 COLLATE UNICODE);
CREATE PROCEDURE FILLTEST_UTF8
AS
DECLARE VARIABLE COUNTER INTEGER;
BEGIN
COUNTER=4000;
WHILE (COUNTER>0) DO
BEGIN
INSERT INTO TEST_UTF8 (UTF8CL) VALUES (CAST(:COUNTER AS VARCHAR(10)));
COUNTER=COUNTER-1;
END
END
EXECUTE PROCEDURE FILLTEST_UTF8;
COMMIT;
SELECT UTF8CL FROM TEST_UTF8
WHERE UTF8CL COLLATE UNICODE_CI_AI CONTAINING '8'
--
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
------------------------------------------------------------------------------
This SF.net email is sponsored by Windows:
Build for Windows Store.
http://p.sf.net/sfu/windows-dev2dev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
