12.07.2021 7:15, Gabor Boros wrote:
2021.05.20. 10:36 keltezéssel, Gabor Boros írta:
Hi All,
I try to find why the real life application's internal database version changer much slower with 4.0. One slow step is the
procedure altering.
Vlad suggested and index privately: RDB$DEPENDENCIES (RDB$DEPENDENT_NAME, RDB$DEPENDED_ON_NAME, RDB$FIELD_NAME).
I've spend a lot of time looking at case Gabor sent to me privately.
The case content is more than 2200 "CREATE OR ALTER PROCEDURE" statements.
The main reasons for slower execution I found are:
- fb25 update records in RDB$PROCEDURE_PARAMETERS, while fb3 do delete\insert
records in RDB$PROCEDURE_PARAMETERS
- with records in RDB$PROCEDURE_PARAMETERS fb3 delete\insert related records in
RDB$FIELDS, RDB$USER_PRIVILEGES and RDB$SECURITY_CLASSES
- indices on RDB$DEPENDENCIES was changed in ODS12
before ODS 12:
CREATE INDEX RDB$INDEX_27 ON RDB$DEPENDENCIES (RDB$DEPENDENT_NAME);
CREATE INDEX RDB$INDEX_28 ON RDB$DEPENDENCIES (RDB$DEPENDED_ON_NAME);
ODS 12:
CREATE INDEX RDB$INDEX_27 ON RDB$DEPENDENCIES (RDB$DEPENDENT_NAME,
RDB$DEPENDENT_TYPE);
CREATE INDEX RDB$INDEX_28 ON RDB$DEPENDENCIES (RDB$DEPENDED_ON_NAME,
RDB$DEPENDED_ON_TYPE, RDB$FIELD_NAME);
Some (many) internal requests uses both RDB$INDEX_27 and RDB$INDEX_28 with bad
selectivity on RDB$DEPENDENT_NAME and RDB$DEPENDENT_ON_NAME, while combined
index
on (RDB$DEPENDENT_NAME, RDB$DEPENDENT_ON_NAME) have much better selectivity.
I recommend to add such index into next ODS. BTW, fb25 also have very visible
perf boost with such index.
- metadata names is significantly larger in ODS13:
ODS12: CHAR(31) CHARACTER SET UNICODE_FSS 93 bytes
ODS13: CHAR(63) CHARACTER SET UTF8 252 bytes
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
