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
