When joining more than two tables, you cannot control the order tables are joined, and only two at a time will be joined at a time. So, there is a high probability that temp indeces will be used.
>We have the following query:
>SELECT Custname, wono, pno.part_no, pno.revision, to_dept_id, CountToDate, shipdate;
> FROM crspartno pno;
> INNER JOIN inventor ON pno.uniq_key = inventor.int_uniq;
> INNER JOIN crsCustsSel cust ON inventor.custno = cust.custno;
> ORDER BY custname, pno.part_no, to_dept_id;
> INTO CURSOR crsmovedfinal READWRITE
which runs very slowly. The query plan shows
>
>Rushmore optimization level for intermediate result: none
>Rushmore optimization level for table inventor: none
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and table inventor using temp index
>Joining intermediate result and intermediate result using temp index>
>The problem is that the inventor table has an index on int_uniq but Rushmore doesn't seem to recognize it. When the query get split into two queries the optimization level improves dramatically
>SELECT Custno, wono, pno.part_no, pno.revision, to_dept_id, CountToDate, shipdate;
> FROM crspartno pno;
> INNER JOIN inventor ON pno.uniq_key = inventor.int_uniq;
> INTO CURSOR crsmovedfinal READWRITE
>
>Rushmore optimization level for intermediate result: none
>Rushmore optimization level for table inventor: none
>Joining intermediate result and table inventor using index tag Int_Uniq>SELECT Custname, wono, part_no, revision, to_dept_id, CountToDate, shipdate;
> FROM crsmovedfinal mv;
> INNER JOIN crsCustsSel cust ON mv.custno = cust.custno;
> ORDER BY custname, part_no, to_dept_id;
> INTO CURSOR crsmovedfinal READWRITE
>
>
>Rushmore optimization level for intermediate result: none
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and intermediate result using temp index>
>No matter how I re-arranged the pieces of the query, I still couldn't get the "proper" tag to be used.
>
>I've never found a construction such as
>INNER JOIN myTable ON condition USING INDEX TAGS
but is there some way to structure the query so that Rushmore will do a better job selecting which tags can be used?
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer