>Hi Bridget,
>Without knowing the data environemtn it is impossible to determine if Rushmore is being used or not. Can you do two things?
>
>1. Issue the command =sys(3054,1) and then run your query. Sys(3054) reports the level of Rushmore Optimization that is being used.
>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.
>
>>Hi, all.
>>
>>The goal is for the resulting table to have one record for each lane that exists in each section of highway. The sections in table2 (which has the lane data) are smaller than those in table1, so there may be several sections in table2 that fall at least partly within a single section in table1. Here is my SELECT, which produces correct results:
>>
>>SELECT DISTINCT table1.section_id, table2.lane_number FROM data\table1 ;
>> INNER JOIN data\table2 ON table2.element = table1.element ;
>> WHERE ((table2.beg_odometer>=table1.beg_odometer AND table2.beg_odometer<=table1.end_odometer) OR ;
>> (table2.iri_end_odometer>=table1.beg_odometer AND table2.end_odometer<=table1.end_odometer)) AND ;
>> table2.survey_year = cYear INTO TABLE data\my_test DATABASE data\temp
>>
>>I am just wondering if this can be refined to run faster, or if there is some other better approach. In my testing so far, I have about 350 records in table1 and 1.5 million records in table2, and it takes an average of 80 seconds. The full size of table1 will be about 32,000 records... so now we're talking hours. This may be acceptable if it's the fastest way, but I'm hoping that isn't the case. (I haven't worked with tables this large before, so I'm not sure what is a reasonable time to expect.)
>>
>>Thanks in advance for any advice!

If you have keyword DISTINCT in your SQL, this SQL would be non-optimizable by Rushmore.