We are testing two scenarios and have unexpected results. Here is some background...

Three tables on SQL Server (2k)

ph - 990,000 records
gh - 1,245,000 records
gd - 5,355,000 records

There are many gh records for 1 ph records. There are many gd records for each gh record. One (ph) to many (gh) to many (gd).

The desired result is to have the same result cursor of the three tables joined in VFP

Two tests...
Test 1 - Multiple selects in VFP
Execute stored procedure (SQL Server) to bring back all ph records for a key (or list of keys)
Using returned set, create XML string of id's for gh table
Execute stored procedure (SQL Server) to bring back all gh records passing list of id's
Using returned set, create XML string of id's for gd table
Execute stored procedure (SQL Server) to bring back all gd records passing list of id's
Join all tables together in VFP to make one result set

Test 2 - One select in SQL Server
Execute stored procedure (SQL Server) to bring back one result set for a key or list of keys - passed as an XML string
Join all tables in SQL Server
Return result set

Our test results show that option 1 is faster than option 2 even with the extra overhead of multiple trips, string manipulation, etc.

We have non-clustered indexes on all fields involved. We are having SQL Server analyze and suggest indexes and are trying to see what is causing this. But in general, this seems to be the opposite of what you should expect.

BTW, the server is a multi-processor box with 8gb RAM. GB networking, etc.