--- In firebird-support@yahoogroups.com, "Andre du Plessis"
<andre@i...> wrote:

> | RDB$RELATION_CONSTRAINTS| 0 | 6,646 |

Most of the time were spent for natural scan of
RDB$RELATION_CONSTRAINTS because of amount of FKs. Firstly, FB1.5 have
additional indices on system tables so in this situation we can wait
significant speed up of the queries on such a tables. On FB1 you can
create such an indices yourself:

Create INDEX AddSysInd1 ON RDB$RELATION_CONSTRAINTS(RDB$RELATION_NAME,
RDB$CONSTRAINT_TYPE)
Create INDEX AddSysInd2 ON RDB$RELATION_CONSTRAINTS(RDB$INDEX_NAME)
Create INDEX AddSysInd3 ON RDB$INDICES(RDB$FOREIGN_KEY)

note they'll not survive backup/restore, so you need to create them
each time you restore database. Secondly, what about to reduce their
amount? Look for example:

ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_17 FOREIGN KEY
(HOSPITAL_ID,
LOCATION_ID, LOCATION_SUB_ID) REFERENCES LOCATION_SUBS (HOSPITAL_ID,
LOCATION_ID, LOCATION_SUB_ID);

ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_18 FOREIGN KEY
(HOSPITAL_ID,
YEAR_ID, MONTH_ID) REFERENCES FINANCIAL_YEAR_DATES (HOSPITAL_ID,
YEAR_ID,
MONTH_ID);

ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_20 FOREIGN KEY
(HOSPITAL_ID,
MEDICAL_STAFF_ID_SCHEDULE, SCHEDULE_ID) REFERENCES
IC_HOSPITAL_STAFF_SCHEDULE (HOSPITAL_ID, MEDICAL_STAFF_ID,
SCHEDULE_ID);

ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_2 FOREIGN KEY
(HOSPITAL_ID)
REFERENCES IC_HOSPITALS (HOSPITAL_ID);

last FK is definitely superfluos - you have references by HOSPITAL_ID
to tables which definitely have references to IC_HOSPITALS
(HOSPITAL_ID) so if this references allows such a HOSPITAL_ID in this
table - this mean such a HOSPITAL_ID exists. I can't analyze necessity
of another references, but I hope analyzing data structure from this
point of view you can drop many references to "root" tables when there
exists references through "intermediate" ones to this "root". The same
about every "cross-level" references if there exists indirect
reference through "middle-level". Hope this concept expressed with my
English did'nt make you to feel giddy :)

Best regards,
Alexander.