How can I make this SQL execute faster? 2.5 seconds is way too long. To solve the NATURAL (Slow) access of records via SELECT, I usually just create some appropriate indexes and retry, until FB is fast-as-lighting again. Some how, I'm tied up with this SQL which I cannot seem to get going fast, no matter what indexing I try.
----------------------------------------------- Here is the SQL I need to execute quickly: SELECT DISTINCT a.FLD_NM, b.FLD_DSPLY_NM, d.PAR_GUID FROM JET_CHG_LOG a LEFT JOIN JET_FLD_NMS b ON a.TBL_NM = b.TBL_NM AND a.FLD_NM = b.FLD_NM LEFT JOIN JET_SAV_PNT d ON a.PAR_GUID = d.OBJ_GUID WHERE d.PAR_GUID = 'C8B3B24AC7214A8084849D427F631102' ORDER BY b.FLD_DSPLY_NM ----------------------------------------------- Here is the resulting plan: Field #01: JET_CHG_LOG.FLD_NM Alias:FLD_NM Type:STRING(39) Field #02: JET_FLD_NMS.FLD_DSPLY_NM Alias:FLD_DSPLY_NM Type:STRING(50) Field #03: JET_SAV_PNT.PAR_GUID Alias:PAR_GUID Type:STRING(39) PLAN SORT (SORT (JOIN (JOIN (A NATURAL, B INDEX (UNQ_JET_FLD_NMS_2)), D INDEX (PK_JET_SAV_PNT)))) Executing... Done. 2102781 fetches, 6 marks, 84645 reads, 6 writes. 0 inserts, 0 updates, 0 deletes, 319031 index, 159695 seq. Delta memory: 9984 bytes. Total execution time: 2.531s Script execution finished. ------------------------------------------------ Here are the existing indexes: JET_CHG_LOG: ... CONSTRAINT PK_JET_CHG_LOG PRIMARY KEY (OBJ_GUID) ... ALTER TABLE JET_CHG_LOG ADD CONSTRAINT FK_JET_CHG_LOG_0 FOREIGN KEY (PAR_GUID) REFERENCES JET_SAV_PNT (OBJ_GUID) ON UPDATE CASCADE ON DELETE CASCADE; CREATE INDEX IDX_JET_CHG_LOG1 ON JET_CHG_LOG (CMTS,PAR_GUID); CREATE INDEX IDX_JET_CHG_LOG2 ON JET_CHG_LOG (FLD_NM); CREATE INDEX IDX_JET_CHG_LOG3 ON JET_CHG_LOG (PAR_GUID); JET_FLD_NMS: ... CONSTRAINT PK_JET_FLD_NMS PRIMARY KEY (OBJ_GUID), CONSTRAINT UNQ_JET_FLD_NMS_1 UNIQUE (PAR_GUID,FLD_NM), CONSTRAINT UNQ_JET_FLD_NMS_2 UNIQUE (TBL_NM,FLD_NM) ... ALTER TABLE JET_FLD_NMS ADD CONSTRAINT FK_JET_FLD_NMS_0 FOREIGN KEY (PAR_GUID) REFERENCES JET_TBL_NMS (OBJ_GUID) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE JET_FLD_NMS ADD CONSTRAINT FK_JET_FLD_NMS_1 FOREIGN KEY (TBL_NM) REFERENCES JET_TBL_NMS (TBL_NM) ON UPDATE CASCADE ON DELETE CASCADE; CREATE INDEX IDX_JET_FLD_NMS1 ON JET_FLD_NMS (FLD_DSPLY_NM); CREATE INDEX IDX_JET_FLD_NMS_2 ON JET_FLD_NMS (FLD_DSPLY_NM); JET_SAV_PNT: ... CONSTRAINT PK_JET_SAV_PNT PRIMARY KEY (OBJ_GUID) CREATE INDEX IDX_JET_SAV_PNT1 ON JET_SAV_PNT (TBL_NM,REC_ACT,PAR_GUID,CMTS); CREATE INDEX IDX_JET_SAV_PNT2 ON JET_SAV_PNT (PAR_GUID); CREATE DESCENDING INDEX IDX_JET_SAV_PNT3 ON JET_SAV_PNT (CMTS); CREATE DESCENDING INDEX IDX_JET_SAV_PNT4 ON JET_SAV_PNT (SAV_PNT); ----------------------------------------------- Thanks in advance for any help you can provide.
