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 <
kevin.wendy.morris@...> 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]