.DBF vs .MDB 1

[Yazster]

Need advice on this one.

I've designed several database applications for an intranet environment. There are many source databases, Foxpro (DBF) format, maintained and indexed by our MIS department. In most cases, these databases include over 500,000 records each.

I'm not using the data control, but I am using DAO (database object), as well as SQL to perform queries.

To keep it simple, most of our queries extract multiple records, using SQL.

My question is this: Why is it so !@^&# slow when querying a DBF format VS an MDB format? I had a DBF database (500,000+ records), performed a query with a moderately complex sql statement, and it takes over 1 minute to return results. I tried importing this database into an MDB, and performed the same query. It took about 2 seconds to return the same results.

With the amount of database work we do, we can't afford to always duplicate the databases in an MDB format.

Is there something I'm doing wrong?

 

[Karl Blessing]

My guess would be the way you are connecting to it, DAO works great with access using the JET driver, however I am uncertain how DAO works with FoxPro, try using ADO with foxpro and see if that helps increase speed.
Karl
kb244@kb244.com

http://www.kb244.com

Experienced in : C++(both VC++ and Borland),VB1(dos) thru VB6, Delphi 3 pro, HTML, Visual InterDev 6(ASP(WebProgramming/Vbscript)

http://www.brainbench.com/transcript.jsp?pid=629151