SELECT 
RCI.fpartno,
RCI.fpartrev,
RCI.fdescript,
RCL.fdexpdate,
INM.fleadtime
  FROM dbo.rclotc RCL 
	inner join dbo.rcitem RCI on RCL.fcrcitmkey = RCI.freceiver + RCI.fitemno
	inner join dbo.inmast INM on RCI.fpartno = INM.fpartno and RCI.fpartrev = INM.frev
 WHERE year(RCL.fdexpdate) > 1901

SELECT RCI.fpartno, 
       RCI.fpartrev, 
       RCI.fdescript, 
       RCL.fdexpdate, 
       INM.fleadtime 
  FROM dbo.rclotc RCL 
       CROSS JOIN dbo.inmast INM 
       INNER JOIN dbo.rcitem RCI 
          ON RCI.fpartno = COALESCE (INM.fpartno , INM.fpartno)              AND INM.frev = RCI.fpartrev 
             AND RCL.fcrcitmkey = RCI.freceiver + RCI.fitemno 
 WHERE year(RCL.fdexpdate) > 1901

quote:

---

Originally posted by DavidChel

Anybody?

---

quote:

---

The CROSS JOIN was not the actual intention but changing the join path is. In the original SQL, the join order is first on dbo.rclo and dbo.rcitem, then the results of it is then join with dbo.inmast. In the optimization, we would change the order tables are joined and try different permutation. The alternative SQL has a different join order: First joining dbo.rclotc and dbo.inmast, then use the result to join with dbo.rcitem. Speed to process the different orders of joins are different depending on the data in your tables.



As for the COALESCE, it is a trick to force database using (or not using) an index. This is particular useful in joining condition where both sides of the condition are indexed. Take this SQL as an example:



SELECT * FROM TABLEA INNER JOIN TABLEB ON TABLEA.COLA = TABLEB.COLB



Assumed that both TABLEA.COLA and TABLEB.COLB are indexed. For a NESTED LOOP join, database may choose to use the value of TABLEA.COLA to search the index on TABLEB.COLB, or vice versa. However, if we want to enforce the use of index on TABLEA.COLA, we can rewrite the condition to: TABLEA.COLA = COALESCE(TABLEB.COLB, TABLEB.COLB) so that the use of index on TABLEB.COLB is not feasible.

---