>Hi,
>
>I have a question that would probably get a wider audience in the "views" category, but I would rather get MM specific answers...
>
>I've been experimenting with views a lot since the ones I'm using tend to combine 3 or more tables and in spite of vfp's impressive speed, it seems like rushmore is not kicking in.
>
>Let me give you an example:
>
>If I do:

>select requests.iid, providers.lname, lawfirms.name from ;
>	requests, case_def, lawfirms, providers ;
>	where requests.iid = 123456 and ;
>		providers.iid = requests.iproviderid and ;
>		case_def.iid = requests.icasedefid and ;
>		lawfirms.iid = case_def.lawfirm ;
>	into cursor c0
>
>it takes 0.8 seconds to retrieve the row of data I'm looking for.
>
>If instead I break this single select into THREE:
>1. Join requests and providers into cursor c1
>2. Join c1 and case_def into cursor c2
>3. Join c2 and lawfirms into cursor c3
>
>The whole enchilada takes only 0.04 seconds (20 times faster!!!!!).
>
>Remember that over a network, those 0.8 seconds become roughly 8 seconds, which isn't something my users will be thrilled about.
>
Try changing the Select statement to the following:

select requests.iid, providers.lname, lawfirms.name from ;
     requests INNER JOIN Providers ON providers.iid = requests.iproviderid ;
     INNER JOIN Case_def ON case_def.iid = requests.icasedefid ;
     INNER JOIN lawfirms ON lawfirms.iid = case_def.lawfirm ;
  where requests.iid = 123456 ;
  into cursor c0

I like to use the ON clause with Joins because FoxPro doesn't get as confused about the order of how the statements are processed.  FoxPro does each join then moves to the next join.  See the time this takes.  Make sure providers.iid, requests.iproviderid, case_def.iid, requests.icasedefid, lawfirms.iid, and case_def.lawfirm all have indexes set for them.  You can also change around the order of the joins to see if it helps with speed.  You want to make the bigiest cut down on records first.  See how this works for you.

-----------------------------------------

Cathi Gero, CPA
Prenia Software & Consulting Services
Microsoft C# / .NET MVP
Mere Mortals for .NET MVP
cgero@prenia.com
www.prenia.com
Weblog: blogs.prenia.com/cathi