Hi Bridget,
Yes I would put an index on end_odometer.

For me, JOINS present performance issues as I believe they have to evaluate more of the data. Maybe Garrett's article will shed some light on this. For that reason, if I am only using INNER JOINS, I don't use them and change them to WHERE clauses. Try the following:

SELECT DISTINCT table1.section_id, table2.lane_number ;
   FROM data\table1, data\table2 ;
   WHERE table2.element = table1.element AND ;
      table2.survey_year = cYear AND ;
      (BETWEEN(table2.beg_odometer,table1.beg_odometer,table1.end_odometer) OR ;
      (table2.iri_end_odometer>=table1.beg_odometer AND table2.end_odometer<=table1.end_odometer)) ;
   INTO TABLE data\my_test DATABASE data\temp

Put the AND conditions first. VFP will evaluate the left side and if .F., will not go further in the evaluation.

HTH.

>Hi Larry,
>
>>1. Issue the command =sys(3054,1) and then run your query. Sys(3054) reports the level of Rushmore Optimization that is being used.
>
>Rushmore Optimization is "none" for table1 and "full" for table2. The index on survey_year is being used for optimization.
>
>>2. Check the indexes that you have for table1 and table2. It looks like you should have one on element, beg_odometer and end_odometer.
>
>Table1 has only its PK index, and table2 has regular indexes on survey_year, element, and beg_odometer, but not end_odometer. Do you mean it should have one index all three fields, like str(element)+str(beg_odometer)+str(end_odometer) ?
>
>Thanks,
>Bridget
>