>Here is another SQL that is running slow for me. I think I'm probably still >missing >something that you are trying to teach me. Can you spot my error? > >SELECT FIRST 25 SKIP 0 DISTINCT > s.OBJ_GUID, s.CMTS, s.CTK, s.REC_DSC, s.REC_ACT, s.NOTE, l.NEW_VAL, > f.FLD_DSPLY_NM, > t.TBL_DSPLY_NM >FROM > JET_SAV_PNT s > JOIN JET_CHG_LOG l ON l.PAR_GUID = s.OBJ_GUID > JOIN JET_FLD_NMS f ON f.TBL_NM = l.TBL_NM AND f.FLD_NM = l.FLD_NM > JOIN JET_TBL_NMS t ON t.TBL_NM = f.TBL_NM >WHERE s.CBY = 'JMA' >ORDER BY s.CMTS DESC > >This takes 3.5 seconds to run. I changed the order of the joins and also made >them >regular joins instead of LEFT joins. Those improvements brought the execution >time >down to 3.5 seconds from > 9 seconds.
No, I cannot see any obvious "error", well, unless you call it an error not telling us the PLAN or something about the tables/indexes involved (no point in checking indexes used on tiny tables, nor to use an index for CBY if 50% contain the value JMA in that field) ;o) I'd expect a plan similar to PLAN SORT(JOIN(JOIN(JOIN(s INDEX CBY_INDEX, l INDEX PAR_GUID_INDEX), f INDEX(FLD_NM_INDEX), t INDEX(TBL_NM_INDEX)) Of course, there might be an index also on f.TBL_NM - I've no idea whether that one will slow down or increase your performance. You can easily check that by changing to JOIN JET_FLD_NMS f ON f.TBL_NM || '' = l.TBL_NM AND f.FLD_NM = l.FLD_NM By the way, I think the order of the JOINs is irrelevant if only plain (INNER) JOINs like your query above is used. It's once words like LEFT/RIGHT are added that the order becomes relevant. HTH, Set
