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 <m...@lawinegevaar.nl> 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
