Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>>I have two tables, PERSON (21000 records, index on Person_Id), PERSON_SOURCE (22500 records, index on Person_id, Source_id), on which I do the following SELECT:
>>
>>SELECT DISTINCT Person.*;
>> FROM cavr!person_source INNER JOIN cavr!person ;
>> ON Person_source.person_id = Person.person_id;
>> WHERE Person_source.source_id = '00031' INTO CURSOR XXXX
>>
>>I run this SELECT in the command window and it takes about 0.3 seconds.
>>
>>When I run this in the INIT of a form:
>>
>>lnStartTime = SECONDS()*1000
>>
>>SELECT DISTINCT Person.*;
>> FROM cavr!person_source INNER JOIN cavr!person ;
>> ON Person_source.person_id = Person.person_id;
>> WHERE Person_source.source_id = '00031' INTO CURSOR XXXX
>>
>>WAIT WINDOW 'done loading all people' +STR(SECONDS()*1000-lnStartTime)
>>
>>
>>it takes almost 5 seconds. Any reason why this could happen?
>>
>>TIA,
>>
>>Ken
>
>As Larry said it might be exclusivity.
>Another reason might be you're not using the same database and tables from IDE and form.
>ie: In IDE you have a set path to local data folder but in form you've a DE or from load you use a network data folder.
>
>PS: What's the person_source's contribution to this query other than checking if exists there too ?
>If you don't need it really do not use distinct as it slows down the query.
>Cetin
Cetin,
Checked the exclusivity and they are the same in both IDE and form.
I also doubled checked and the tables are both running from the same local folder.
I do need to get a distinct list (Person-Person_Source 1:M). I removed the distinct and replaced with a Group By Person_id and it now runs fine and fast. (so problem solved at one level)
Still I am baffled why the same query in the command window and form have such drastic time differences under (apparently) the same enviornment conditions just because of the Distinct.
Ken
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