Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
VFP8 OUTER JOIN evaluated before WHERE
Message
From
11/11/2002 17:29:48
 
 
To
11/11/2002 15:33:17
General information
Forum:
Visual FoxPro
Category:
Other
Miscellaneous
Thread ID:
00720072
Message ID:
00721317
Views:
8
Well, ok, I don't know that a feature has been "turned off". I don't think it's intentional, for one thing. But it's an extremely good guess. It's not that JOINs are slow period, it's that they are not optimized before they are done. Like this:
* Call: 500,000 records
* Client: 10,000 records

SELE ALL call.desccall AS desc ;
FROM CALL ;
LEFT OUTER JOIN CLIENT ON Call.client_Num = Client.client_Num;
WHERE CALL.DATEOFCA BETWEEN {^2000-01-01} AND {^2000-02-01};
INTO CURSOR Temp

*(takes >6 seconds, in 8, and .3 (first time), and .03 (any time after) in 7)

* This returns, say, 2000 records. My suspition is that all of
* the records are getting joined, instead of filtering on DATEOFCA first.

*However, this is fast (both in VFP7 and 8):

SELE ALL desccall AS desc, Client_Num FROM CALL;
WHERE DATEOFCA BETWEEN {^2000-01-01} AND {^2000-02-01};
INTO CURSOR TempCALL NOFILTER

SELE ALL call.Desc ;
FROM TempCALL AS CALL ;
LEFT OUTER JOIN CLIENT ON Call.client_Num = Client.client_Num;
INTO CURSOR Temp

*Don't blame me for the field names ;)
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform