Hi guys sorry if the below info Is too much, but I thought it might help:
Here is a performance analasys
The select that we test with was ONLY on the primary key where visit_id =
‘’

Below is a Performance Analysis from quickdesk.

Query Time
------------------------------------------------
Prepare : 591
Execute : 221
Avg fetch time: 221.00 ms

Memory
------------------------------------------------
Current: 2.925631E7
Max : 2.974222E7
Buffers: 2,048

Operations
------------------------------------------------
Read : 0
Writes : 5
Fetches: 14,418

Plan:
------------------------------------------------
PLAN (VISITS INDEX (RDB$PRIMARY325))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+--------
--+
| Table Name | Index | Non-Index | Updated | Deleted |
Inserted |
| | reads | reads | | |
|
+--------------------------+-------+-----------+---------+---------+--------
--+
| RDB$FIELDS| 123 | 0 | 0 | 0 |
0 |
| RDB$INDEX_SEGMENTS| 1 | 0 | 0 | 0 |
0 |
| RDB$RELATION_FIELDS| 123 | 0 | 0 | 0 |
0 |
| RDB$RELATIONS| 2 | 0 | 0 | 0 |
0 |
| RDB$RELATION_CONSTRAINTS| 0 | 6,646 | 0 | 0 |
0 |
| VISITS| 1 | 0 | 0 | 0 |
0 |
+--------------------------+-------+-----------+---------+---------+--------
--+

Here is a DDL Fragment of the Table:
Makes use of domains.
Foreign Keys and Primary keys are both Varchar(10);
The Others are VAR_TWENTY = VARCHAR(20) , names are descriptive of their
sizes.

CREATE TABLE VISITS (
VISIT_ID VAR_PK_VISIT NOT NULL,
HOSPITAL_ID VAR_PRIMARY_KEY NOT NULL,
ACCOUNT_NUMBER VAR_FOREIGN_KEY NOT NULL,
PATIENT_ID VAR_FOREIGN_KEY NOT NULL,
ACTIVE_FLAG CHAR_ACTIVE DEFAULT 'Y' NOT NULL,
DATE_CREATED CREATED_ON DEFAULT 'NOW' NOT NULL,
USER_CREATED CREATED_BY DEFAULT USER NOT NULL,
DATE_DELETED DATE_NULL,
USER_DELETED VAR_TWENTY,
UPLOAD_FLAG CHAR_ONE DEFAULT 'N' NOT NULL,
ACCOUNT_NO VAR_TWENTY,
DATE_MODIFIED DATE_VALUE DEFAULT 'NOW',
DATE_ADMITTED DATE_NULL,
DATE_IOD DATE_NULL,
DATE_DISCHARGED DATE_NULL,
DATE_RELEASED DATE_NULL,
MEDICAL_AID_NO VAR_THIRTY,
DEPENDANT_NO VAR_TEN,
PRE_ADMISSION_FLAG CHAR_ONE DEFAULT 'N' NOT NULL,
MEDICAL_AID_ID VAR_FOREIGN,
VISIT_TYPE_CODE VAR_FOREIGN,
ACCOUNT_STATUS_CODE VAR_FOREIGN,
ACCOUNT_TYPE_CODE VAR_FOREIGN,
REMARKS BLOB_ICD,
STATEMENT_PERIOD INT_VALUE DEFAULT 0,
CURRENT_BALANCE NUM_FIFTEEN DEFAULT 0,
MEDICAL_AID_TYPE CHAR_ONE DEFAULT 'P' NOT NULL,
MED_STAFF_ID_ATTEND VAR_FOREIGN,
MED_STAFF_ID_REFER VAR_FOREIGN,
MED_STAFF_ID_ANAEST VAR_FOREIGN,
MEDICAL_PACKAGE_ID VAR_FOREIGN,
VISIT_FEE_ID VAR_FOREIGN,
VAT_RATE_ID VAR_FOREIGN,
LENGTH_OF_STAY_BILLED NUM_FIFTEEN DEFAULT 0,
AUTHORISATION_NO VAR_TWENTY,
OCCUPANCY_TYPE_CODE VAR_FOREIGN,
ATTEND_NAME VAR_EIGHTY,
CPT_CODE VAR_TWENTY,
DIAGNOSIS_DESC VAR_TWO_FIFTY,
DIAGNOSIS_ID VAR_FOREIGN,
ICD_CODE VAR_TWENTY,
LOCATION_ID VAR_FOREIGN,
LOCATION_SUB_ID VAR_FOREIGN,
PROCEDURE_DESC VAR_TWO_FIFTY,
PROCEDURE_ID VAR_FOREIGN,
REFER_NAME VAR_EIGHTY,
TEL_NO VAR_TWENTY,
MEDICAL_AID_NAME VAR_FIFTY,
MEDICAL_PACKAGE_NAME VAR_FIFTY,
HEALTH_CARE_ID VAR_FOREIGN,
HEALTH_CARE_NAME VAR_FIFTY,
DATE_LAST_ELIGIBILITY DATE_NULL,
LOCATION_DESC VAR_FIFTY,
LOCATION_SUB_DESC VAR_FIFTY,
MEMBER_ELIGIBLE CHAR_ONE DEFAULT 'N',
ICD_CODE_TYPE CHAR_ONE,
ICPC_CODE VAR_FOREIGN,
ICPC_DESC VAR_TWO_FIFTY,
MEDICAL_CARD_IMAGE BLOB_IMG,
MEDICAL_STAFF_ID_SCHEDULE VAR_FOREIGN,
SCHEDULE_ID VAR_FOREIGN,
YEAR_ID INT_ID,
TMP_ACCOUNT_NO VAR_TWENTY,
COMPLAINT_NOTE BLOB_ICD,
FOLLOW_UP_NOTE BLOB_ICD,
TARIFF_GROUP_ID VAR_FOREIGN,
VISIT_TYPE_DESC VAR_EIGHTY,
ACCOUNT_STATUS_DESC VAR_EIGHTY,
ACCOUNT_TYPE_DESC VAR_EIGHTY,
VISIT_FEE_DESC VAR_EIGHTY,
OCCUPANCY_TYPE_DESC VAR_EIGHTY,
ICD_DESC VAR_TWO_FIFTY,
CPT4_DESC VAR_TWO_FIFTY,
CPT4_CODE VAR_FOREIGN,
GROUP_DESC VAR_EIGHTY,
LENGTH_OF_STAY_ACTUAL NUM_FIFTEEN,
VISIT_STATUS_DESC VAR_EIGHTY,
UPDATE_VERSION_NO INT_VALUE NOT NULL,
TAX_PERCENT NUM_FIFTEEN,
SWITCH_STATUS CHAR_ONE,
ANAEST_NAME VAR_EIGHTY,
BILLED_MEDICAL_AID NUM_FIFTEEN,
BILLED_PRIVATE NUM_FIFTEEN,
RAD_REQUESTS INT_VALUE,
PATH_REQUESTS INT_VALUE,
VISIT_SEARCH VAR_TWO_FIFTY,
AUTH_LOS NUM_FIFTEEN,
AUTH_TOTAL NUM_FIFTEEN,
ACTUAL_LOS NUM_FIFTEEN,
ACTUAL_TOTAL NUM_FIFTEEN,
MONTH_ID INT_VALUE,
AUTH_REASON_ID VAR_FOREIGN,
AUTH_REASON_DESC VAR_EIGHTY,
SITE_NAME VAR_EIGHTY,
PAT_AGE_YEARS INT_VALUE,
FOLLOW_UP CHAR_ONE DEFAULT "N" NOT NULL,
USER_MODIFIED VAR_THIRTY,
UNRELEASED_FLAG CHAR_ONE DEFAULT 'N',
LOCATION_DATE_IN DATE_VALUE,
HLD_TITLE_ID VAR_TWENTY,
HLD_INITIALS VAR_TWENTY,
HLD_FIRST_NAME VAR_EIGHTY,
HLD_LAST_NAME VAR_EIGHTY,
HLD_IDENTITY_NO VAR_TWENTY,
HLD_DATE_OF_BIRTH DATE_VALUE,
HLD_MEDICAL_RECORD_NO VAR_TWENTY,
HLD_NATION_DESC VAR_EIGHTY,
PAT_TITLE_ID VAR_TWENTY,
PAT_INITIALS VAR_TWENTY,
PAT_FIRST_NAME VAR_EIGHTY,
PAT_LAST_NAME VAR_EIGHTY,
PAT_BLOOD_GROUP VAR_TEN,
PAT_IDENTITY_NO VAR_TWENTY,
PAT_DATE_OF_BIRTH DATE_VALUE,
PAT_LANGUAGE_DESC VAR_EIGHTY,
PAT_CONTACT_NO_WORK VAR_TWENTY,
PAT_MEDICAL_RECORD_NO VAR_TWENTY,
PAT_NATION_DESC VAR_EIGHTY,
PAT_GENDER CHAR_ONE,
PAT_MARITAL_STATUS CHAR_ONE,
DATE_MVA DATE_NULL,
VISIT_STATUS_CODE VAR_FOREIGN,
DATE_RELEASE_AUDIT DATE_NULL);


Here are the Constraints:
OK so maybe it is not 50, but for the purpose of expression 50 means A LOT :
-).

ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS FOREIGN KEY (VISIT_STATUS_CODE)
REFERENCES ACCOUNT_STATUSES (ACCOUNT_STATUS_CODE);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_1 FOREIGN KEY (ACCOUNT_NUMBER,
PATIENT_ID) REFERENCES ACCOUNT_HOLDER_PATIENTS (ACCOUNT_NUMBER, PATIENT_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_10 FOREIGN KEY
(MED_STAFF_ID_REFER) REFERENCES MEDICAL_STAFF (MEDICAL_STAFF_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_12 FOREIGN KEY
(MED_STAFF_ID_ANAEST) REFERENCES MEDICAL_STAFF (MEDICAL_STAFF_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_13 FOREIGN KEY (VAT_RATE_ID)
REFERENCES TAX_RATES (TAX_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_14 FOREIGN KEY (CPT_CODE)
REFERENCES CPT4_CODES (CPT4_CODE);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_15 FOREIGN KEY (DIAGNOSIS_ID)
REFERENCES DIAGNOSIS (DIAGNOSIS_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_16 FOREIGN KEY (ICD_CODE)
REFERENCES ICD10_CODES (ICD_CODE);
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_19 FOREIGN KEY
(TARIFF_GROUP_ID) REFERENCES TARIFF_GROUPS (TARIFF_GROUP_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_2 FOREIGN KEY (HOSPITAL_ID)
REFERENCES IC_HOSPITALS (HOSPITAL_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_21 FOREIGN KEY (PROCEDURE_ID)
REFERENCES PROCEDURES (PROCEDURE_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_22 FOREIGN KEY (ICPC_CODE)
REFERENCES ICPC_CODES (ICPC_CODE);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_3 FOREIGN KEY (MEDICAL_AID_ID,
MEDICAL_PACKAGE_ID) REFERENCES MEDICAL_AID_PACKAGES (MEDICAL_AID_ID,
MEDICAL_AID_PACKAGE_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_4 FOREIGN KEY (VISIT_TYPE_CODE)
REFERENCES VISIT_TYPES (VISIT_TYPE_CODE);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_5 FOREIGN KEY
(ACCOUNT_TYPE_CODE) REFERENCES ACCOUNT_TYPES (ACCOUNT_TYPE_CODE);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_7 FOREIGN KEY
(OCCUPANCY_TYPE_CODE) REFERENCES OCCUPANCY_TYPES (OCCUPANCY_TYPE_CODE);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_8 FOREIGN KEY (VISIT_FEE_ID)
REFERENCES VISIT_FEE_TYPES (VISIT_FEE_ID);
ALTER TABLE VISITS ADD CONSTRAINT FK_VISITS_9 FOREIGN KEY
(MED_STAFF_ID_ATTEND) REFERENCES MEDICAL_STAFF (MEDICAL_STAFF_ID);


CREATE INDEX VISITS_13 ON VISITS (PATIENT_ID);
CREATE INDEX VISITS_14 ON VISITS (PAT_LAST_NAME);
CREATE INDEX VISITS_15 ON VISITS (ACCOUNT_NO);

Obviously it is a large design, with lots of references, I don’t expect
any1 to try and optimize it, but identify what is causing slowness and maybe
a suggestions

Thanks to all who attempts in helping


_____

From: Alexander V.Nevsky [mailto:ded@...]
Sent: Wednesday, October 15, 2003 10:55 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: FB Size Limitations/Performance Questions

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

> have over 100 fields in the table, there are MANY indexes probably

more than

> 50, selecting a specific record based on the PK is really slow, so I

was

> wondering if the problem has anything to do with too many fields

and/or too

> many indexes.

Andre
1. Are you sure specific record accessing slow queries is really based
on PK? Did you examined plan used by optimizer?
2. If some indices have the same first segment, FB1 optimizer use all
of them when access condition is specified on this column.
3. If table have many indices, optimizer must to analyze all of them
to choice which to use. Perhaps there is "magic" number when algorithm
of this analysis become unefficient, I think only Arno Brinkman can
answer. I hardly can imagine 50 indices on one table, in my (rather
complex) database max is 23 with system ones and 14 counting my
indices only. And I should say this is most old tables, created when I
started to study IB and SQL at all. If I had time I would re-design
this part of database and applications, at least half of this indices
are superfluos but they are used in explicitly planned queries which
are developed 8 years ago and live in five applications resident in
100 departments.

Best regards,
Alexander.





Yahoo! Groups Sponsor


<http://rd.yahoo.com/M=259395.3614674.4902533.1261774/D=egroupweb/S=17051153
86:HM/A=1524963/R=0/SIG=12o885gmo/*http:/hits.411web.com/cgi-bin/autoredir?c
amp=556&lineid=3614674∝=egroupweb&pos=HM>


<http://us.adserver.yahoo.com/l?M=259395.3614674.4902533.1261774/D=egroupmai
l/S=:HM/A=1524963/rand=741185745>

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.


[Non-text portions of this message have been removed]