optimizing a database application using DBE and MSAccess

[amarti44]

Hi:

I have an application accesing a MSAccess database in a LAN. The database is 35 MB in size with 40000 records in the main table. I'm using BDE to access the data store in the file. Every time I do a manipulation with the database a huge amount of data is tranfered (18 MB) back and foward between the computer running the application and the computer where the file is stored. I know that all the data is first copied into a local cache of the BDE in order to be processed. Is there a way to restrict the amount of data that the BDE saves in cache (i.e, saving only indexes) everytime you use the Table Active property?
I know that a good solution will be to move to a SQL client/server application, but besides that, is there a way to optimize this to lower the amount of data transfered through the network? I'm running Delphi 5 in Windows 2000, and for now I'm stuck with MSAccess.

Any input will be greatly appreciated.

Thanks in Advance,

Alberto

 

[TonHu]

You could create your own client/server app using the TCP/IP components available, or use the client/server sample app supplied with D5 for more info. Quickest though would be to switch to SQL-server. If you made it DB-independent, MySQL could be a more 'low-cost' alternative.;-)

HTH
TonHu

 

[VintageWine]

Hi Alberto,

As a first step I suggest that you swap your TTable for a TQuery, or better still a TADODataset (use in TQuery like mode). If you go to a c/s database you will then need to swap your ttables for tqueries to realise the benefit.

Depending on the workings of your database engine and how appropriately your tables are indexed, this action alone will acheive much of the benefit. I have a 35MB non-c/s database (Foxpro actually) working through the BDE in this way with good performance.

The point about TQuery is that you use a SELECT statement to only fetch the records you will need at the time. A TTable fetches the entire table.

Good luck
Simon