OK folks, I've had time to analyse this a bit more closely. Because the C# app was doing a bit extra I've decided just to time the OLE DB performance as this seemed to be the most interesting aspect.
The VFP app carries out the following in the time-test:
Opens the tables
Queries each of the 7 databases, outputing the result to a temp cursor - END OF TEST
The C# app carries out the following in the time-test:
Queries each of the 7 databases, outputing the result to a DataSet - END OF TEST
Results
175 records returned
Visual FoxPro 8.0 - 1.06 seconds to carry out all queries. Out of 5 tests - Highest/Lowest = 0.13/0.59
Breakdown for each queried DB
0.16, 0.16, 0.15, 0.14, 0.16, 0.14, 0.15
Average of 0.151 seconds
C#.Net - Took 0.87 seconds to carry out all queries. Out of 5 tests - Highest/Lowest = 0.09/0.14
Breakdown for each queried DB
0.09, 0.12, 0.14, 0.13, 0.13, 0.13, 0.13
Average of 0.124 seconds
3759 records returned
Visual FoxPro 8.0 - 7.75 seconds to carry out all queries.
Breakdown for each queried DB
1.13, 1.10, 1.09, 1.10, 1.13, 1.12, 1.08
Average of 1.107 seconds
C#.Net - Took 8.93 seconds to carry out all queries.
Breakdown for each queried DB
1.25, 1.29, 1.25, 1.35, 1.27, 1.30, 1.22
Average of 1.27 seconds
Conclusion...
The OLE DB driver appears to be very efficient - for small result sets, it also peeks higher than the direct Fox query and doesn't dip (ie it's consistent). On larger results however it lags a bit - but bear in mind (in C#) this query is not only retrieving the data, it has to construct the DataSet/DataTable and stuff the data into it - and given the heirarchy of ADO that would appear more work than constructing a temporary cursor - or am I wrong?
Bottom line - the differences are minimal but interesting.....
Kev
PS: VFP CodeLOCAL laOffice(1)
CLEAR
SET ANSI OFF
SET EXACT OFF
SET DELETED ON
SET TALK OFF
USE Z:\Useful\Offices
SELECT *;
FROM Offices;
ORDER BY 1;
INTO ARRAY laOffice
USE IN Offices
laOffice(1, 2) = "z:\ccms\site1"
laOffice(2, 2) = "z:\ccms\site2"
laOffice(3, 2) = "z:\ccms\site3"
laOffice(4, 2) = "z:\ccms\site4"
laOffice(5, 2) = "z:\ccms\site5"
laOffice(6, 2) = "z:\ccms\site6"
laOffice(7, 2) = "z:\ccms\site7"
lnSecs = SECONDS()
CREATE CURSOR Results(Cl_Ref C(8))
FOR o = 1 TO ALEN(laOffice, 1)
laOffice(o, 2) = ALLTRIM(laOffice(o, 2))
secs = SECONDS()
USE (laOffice(o, 2) + "\client") IN 0
USE (laOffice(o, 2) + "\property") IN 0
USE (laOffice(o, 2) + "\foxguids") IN 0
SELECT DISTINCT Guid, Cl_Ref, *;
FROM Client;
LEFT JOIN Property ;
ON Client.Pr_Code = Property.Pr_Code;
LEFT JOIN FoxGuids ON FoxGuids.Table = 'Client' ;
AND FoxGuids.PKey = Client.Cl_Ref;
ORDER BY Cl_Sname, Cl_Fname, Cl_Dob;
WHERE UPPER(Cl_Sname) = "LAWRENCE";
INTO CURSOR temp
?SECONDS() - secs
USE IN Client
USE IN Property
USE IN FoxGuids
ENDFOR
?SECONDS() - lnSecs
