Hi, Bridget

Since you say there may be several sections in table2 that fall at least partly within a single section in table1, a SQL statement may actually NOT be the fastest way to analyze the data. The reason for this is that when a DISTINCT clause is used, VFP first does a regular join on the tables and then filters out only the unique records.

Try this solution:

Make sure that the following indexes exist:

Table2.Element
Table2.Survey_Year
Table2.Lane_Number
Table2.Beg_Odometer
Table2.Iri_End_Odometer


LOCAL lnLane

*!* USE and ZAP or CREATE table data\my_test
*!* Note: This table needs indexes on both section_id and lane_number

SELECT Element, Section_ID, ;
MIN(Beg_Odometer) AS Beg_Odometer, ;
MAX(End_Odometer) AS End_Odometer ;
FROM Table1 ;
GROUP BY 1,2 ;
ORDER BY 2 ;
INTO CURSOR Sections

*!* Note: If only one record exists for each Element and Section_ID, you don't need this query (and you should use Table1 & set order to section_id in place of Sections in the following code)

SELECT Table2
SET ORDER TO Lane_Number
SELECT Sections
SCAN
lnLane = 0
SELECT Table2
LOCATE FOR .T.
DO WHILE !EOF()
LOCATE FOR Element = Sections.Element ;
AND Survey_Year = cYear AND Lane_Number > lnLane ;
AND (BETWEEN(Beg_Odometer,Sections.Beg_Odometer,Sections.End_Odometer) ;
OR BETWEEN(Iri_End_Odometer,Sections.Beg_Odometer,Sections.End_Odometer))
IF FOUND()
SELECT My_Test
LOCATE FOR Section_ID = Sections.Section_ID ;
AND Lane_Number = Table2.Lane_Number
IF !FOUND()
INSERT INTO My_Test VALUES(Table1.Section_ID,Table2.Lane_Number)
ENDIF
SELECT Table2
ENDIF
lnLane = Lane_Number
ENDDO
ENDSCAN

SELECT Table1
SET ORDER TO
SELECT Table2
SET ORDER TO


I think this will be faster... I hope you'll let us know the results!


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