Please check this recent discussion, that internally is about the same thing:
http://tracker.firebirdsql.org/browse/CORE-5940 Also, please change UNICODE_CI_AI to WIN_PTBR (with WIN1252 charset) in your test. I believe the problem will not show on this condition, as it's a MULTI-LEVEL=0 collation. Adriano On 17/10/2018 15:02, Luis Forra wrote: > With unicode there is 2 fetchs in the detail table, with unicode_ci_ai > it fetchs 2000, problem is that with millions of records in there is > millions of fetches. collation unicode don't have the same problem. > > Thank you > > regards > > Mark Rotteveel <[email protected] <mailto:[email protected]>> > escreveu no dia quarta, 17/10/2018 à(s) 18:57: > > Can you please explicitly describe the problem, instead of expecting us > to interpret the output and try to figure out what problem you see here? > > Mark > > On 17-10-2018 19:47, Luis Forra wrote: > > After discussing in firebird-suport I believe that this is > relevant to > > this list > > > > The databases that I have migrate to utf8 with colation unicode_ci_ai > > are much slower in use, the problem is the indexes with various > varchar > > fields. > > > > example of the problem > > > > > > CREATE TABLE M_UNICODE ( > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE, > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE > > ); > > CREATE TABLE D_UNICODE ( > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE, > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE > > ); > > CREATE TABLE M_CI_AI ( > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI, > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI > > ); > > > > CREATE TABLE D_CI_AI ( > > S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI, > > S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI > > ); > > INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'A'); > > INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'B'); > > > > INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'A'); > > INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'B'); > > > > set term ^ ; > > > > execute block > > as > > declare variable i bigint = 1000; > > begin > > while (i > 0) do > > begin > > insert into d_unicode (s1,s2) values ('A','A'); > > insert into d_ci_ai (s1,s2) values ('A','A'); > > i = i-1; > > end > > insert into d_unicode (s1,s2) values ('A','B'); > > insert into d_ci_ai (s1,s2) values ('A','B'); > > end^ > > > > set term ; ^ > > > > ALTER TABLE M_UNICODE ADD CONSTRAINT M_U PRIMARY KEY (S1, S2); > > ALTER TABLE M_CI_AI ADD CONSTRAINT M_CI_AI PRIMARY KEY (S1, S2); > > commit work; > > > > ALTER TABLE D_UNICODE ADD CONSTRAINT D_U FOREIGN KEY (S1, S2) > REFERENCES > > M_UNICODE (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE; > > ALTER TABLE D_CI_AI ADD CONSTRAINT D_CI_AI FOREIGN KEY (S1, S2) > > REFERENCES M_CI_AI (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE; > > commit work; > > > > Query > > ------------------------------------------------ > > update m_ci_ai set s2 = 'C' where s1 = 'A' and s2 = 'B'; > > > > Operations > > ------------------------------------------------ > > Read : 9 > > Writes : 0 > > Fetches: 2 070 > > Marks : 6 > > > > > > Enchanced Info: > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > | Table Name | Records | Indexed | > Non-Indexed | > > Updates | Deletes | Inserts | Backouts | Purges | Expunges | > > | | Total | reads | > reads | > > | | | | | | > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > |D_CI_AI | 0 | 2002 | > 0 | > > 1 | 0 | 0 | 0 | 0 | 0 | > > |M_CI_AI | 0 | 2 | > 0 | > > 1 | 0 | 0 | 0 | 0 | 0 | > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > > Query > > ------------------------------------------------ > > update m_unicode set s2 = 'C' where s1 = 'A' and s2 = 'B'; > > Operations > > ------------------------------------------------ > > Read : 0 > > Writes : 0 > > Fetches: 43 > > Marks : 8 > > > > > > Enchanced Info: > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > | Table Name | Records | Indexed | > Non-Indexed | > > Updates | Deletes | Inserts | Backouts | Purges | Expunges | > > | | Total | reads | > reads | > > | | | | | | > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > |D_UNICODE | 0 | 2 | > 0 | > > 1 | 0 | 0 | 0 | 0 | 0 | > > |M_UNICODE | 0 | 2 | > 0 | > > 1 | 0 | 0 | 0 | 0 | 0 | > > > > +-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+ > > > > > > I appreciate any help to solve this problem, thank you > > > > Best regards > > > > Luis Forra > > --- > > > > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > > > > > -- > Mark Rotteveel > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > > > > > > Firebird-Devel mailing list, web interface at > https://lists.sourceforge.net/lists/listinfo/firebird-devel > Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
