Hi Alexander and others, I have been sitting at Andre's shoulder and
following this.
We created the indexes as suggested by Alexander and the results are
worse for no apparant reason


Query Time
------------------------------------------------
Prepare : 1,933
Execute : 801
Avg fetch time: 801.00 ms

Memory
------------------------------------------------
Current: 2.948429E7
Max : 3.001046E7
Buffers: 2,048

Operations
------------------------------------------------
Read : 0
Writes : 7
Fetches: 982

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| 1 | 0 | 0 | 0
| 0 |
| VISITS| 1 | 0 | 0 | 0
| 0 |
+--------------------------+-------+-----------+---------+---------+--
--------+

---what now?? :-(


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

> 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@h...]
> 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=17
051153

> 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]