>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!

Two experiments you could try: 1) move the things in the WHERE expression to the JOIN expression, creating a non-equi-join. That would likely be slower than what you have, but you could try it anyway. 2) break it into two queries, each on containing one part of the OR expression, and stick them together with a UNION SELECT. You could try that as one big UNION query or make the two queries separately and UNION them together with a third query. UNION queries are not the fastest things, but for all I know they might go faster than a query with an OR expression in the WHERE condition.