Subject | Stumped on SQL Indexing/Bad Plan-ing |
---|---|
Author | red_october2009 |
Post date | 2011-08-09T19:56:05Z |
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.
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.