Walter Meester
HoogkarspelPays-Bas
Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Bridget,
>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
Check, if the following indexes exist:
regular index on table1.element
regular index on table2.element
regular index on table1.beg_odometer
regular index on table2.Beg_odometer
regular index on table1.end_odometer
regular index on table2.end_odometer
regular index on table2.survey_year
Walter,
>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!
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement