Thanks for the sample code.
>Hi Rich
>
>I found this particularly when doing a selection of records to populate a mover list, when I needed the zones not yet in a zone group, in one side, and those already in the group on the other side. The "where" query took a considerable length of time whereas the join was > 10 times faster. So consider 0.7 seconds - vs - 7 seconds, for instance.
>
>SELECT DISTINCT ALLTRIM( STR( ALLZONES.ZONE)), ALLZONES.DESC ;
>FROM MCLMPFAC!ALLZONES ;
>INTO ARRAY .aZones ;
>WHERE ALLZONES.ZONE NOT IN ( SELECT ZONEGRPZ.ZONE ;
> FROM MCLMPFAC!ZONEGRPZ ;
> RIGHT OUTER JOIN MCLMPFAC!ALLZONES ;
> On ZONEGRPZ.ZONE == ALLZONES.ZONE;
> WHERE ALLTRIM( ZONEGRPZ.GROUP) == .cGroupCode) ;
>ORDER BY ALLZONES.DESC
>
>After this, the thing that took most of the time was the populating of the list boxes
>
>Terry
>
>>Terry:
>>
>>A while ago I posted a message "WHERE vs JOIN" in which I asked about this. The consensus of the replies (if I've understood correctly) is that JOIN produces code which is more readable, more flexible, and can produce more accurate results when using outer joins. But by the time the query optimizer was finished, there were only marginal differences in performance. Can you give me some information on what conditions might lead to an order of magnitude difference in speed using JOIN rather than WHERE?
>>
>>Thanks.........Rich
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only