LJ Archive

Listing 2. The Same Query Optimized for Speed

SELECT salesrepid, color, od.buildabikeid, o.orderdate,
           bab.colorid, framename, reps.rep, bab.frame,
           o.custid, o.orderid, partid, o.shipdate,
           bab.bikestate
FROM reps, bikes_basicframetypes b_bft, orders as o,
           orderdetails as od,
           bikes_colorsavailable as b_co_a,
           buildabike as bab
WHERE repid = salesrepid
      AND od.buildabikeid = bab.buildabikeid
      AND b_bft.frametypeid = bab.frametypeid
      AND b_co_a.colorid = bab.colorid
      AND o.orderid = od.orderid
      AND bab.custid=[Forms]![Customers/Contacts]![ID]
      AND  entrydate = (
              SELECT MAX(entrydate)
                 FROM buildabike as b2
                WHERE bab.frame = b2.frame
                )    ;
LJ Archive