Try with Interbase PLANalizer instead of quickdesk, maybe some ovehead is
introduced by quickdesk itself.

Paolo Fainelli

----- Original Message -----
From: "Andre du Plessis" <andre@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, October 15, 2003 11:12 AM
Subject: RE: [firebird-support] Re: FB Size Limitations/Performance
Questions


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