Why do you left join to JET_SAV_PNT? I would do a regular join from JET to JET_SAV_PNT and then if you need a left join to JET_FLD_NMS so be it. Your where clause indicates that you always expect a value in JET_SAV_PNT so just get rid of the left join for that table.
-steve -- Steve Wiser President Specialized Business Software 6325 Cochran Road, Unit 1 Solon, OH 44139 www.specializedbusinesssoftware.com www.docunym.com (440) 542-9145 - fax (440) 542-9143 Toll Free: (866) 328-4936 On Tue, Aug 9, 2011 at 3:56 PM, red_october2009 < [email protected]> wrote: > ** > > > 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. > > > > > This message and any files transmitted with it may contain information that > is privileged, confidential, and exempt from disclosure under applicable > law. They are intended solely for the use of the intended recipient. If > you are not the intended recipient, distributing, copying, disclosing, or > reliance on the contents of this communication is strictly prohibited. If > this has reached you in error, kindly destroy this message and notify the > sender immediately. Thank you for your assistance. > > We attempt to sweep harmful content (e.g. viruses) from e-mail and > attachments, however we cannot guarantee their safety and can accept no > liability for any resulting damage. The recipient is responsible to verify > the safety of this message and any attachments before accepting them. [Non-text portions of this message have been removed] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
