I'm using an MS Access 2003 front end to access the data on a network drive owned by a Visual Foxpro 9 application via odbc, and it seems to be unbelievably slow - there's about 1400 records in one particular table, and it takes a good minute to load up on the Access interface, although when using a basic DBF viewer, or the application that the tables belong to, it's instantaneous. When querying the data, it's quicker than opening the whole table view, but still much slower than I'd expect; the machine it's accessing is on the same network, and it's currently slower to access through the application than a web-hosted SQLServer with easily 10 times that number of records on it.
Any suggestions as to things that I can try with the VFP ODBC link to speed it up that aren't obvious would be appreciated.
Thanks Dave, At present, I'm just playing with a query trying to get it working within Access' query tab...
Query as it stands is taking about 4 minutes to run.. It returns a crosstab containing 10 rows of information, based on input criteria of approximately 230 records - I've tried it without the joins and fields from the Group table, but the speed difference is negligible.
Just to put some perspective on it:
Allsales contains about 50 fields and 15,000 records Stock contains about 100 fields and 500 records Groups contains 3 fields and 8 records.
SELECT allsales.type, allsales.branch, allsales.terminal, allsales.date, Sum(allsales.totalprice) AS SumOftotalprice, Sum(allsales.discamount) AS SumOfdiscamount, Sum(allsales.tender1) AS SumOftender1, Sum(allsales.tender2) AS SumOftender2, Sum(allsales.tender3) AS SumOftender3, Sum(allsales.tender4) AS SumOftender4, Sum(allsales.tender5) AS SumOftender5, Sum(allsales.tender6) AS SumOftender6, Sum(allsales.tender7) AS SumOftender7, Sum(allsales.tender8) AS SumOftender8, Sum(allsales.tender9) AS SumOftender9, Sum(allsales.tender10) AS SumOftender10, Sum(allsales.tender11) AS SumOftender11, Sum(allsales.tender12) AS SumOftender12, Sum(allsales.loypoints) AS SumOfloypoints, Count(allsales.type) AS Fuzz, groups.desc
FROM (stock RIGHT JOIN allsales ON stock.plu = allsales.plu) LEFT JOIN groups ON stock.cgroupid = groups.id
GROUP BY allsales.type, allsales.branch, allsales.terminal, allsales.date, groups.desc, allsales.reportdate
HAVING (((allsales.reportdate)=#3/1/2010#));
Turn off logging in ODBC driver configuration.