Improving performance with MS Access files

Normand P.

Delphi Developer

Wed, 28 Jan 2004 00:43:32 GMT

Improving performance with MS Access files

We have a client/server application that uses MS Access 2000 files through an ADO connection (using Delphi 5.0 and Diamond ADO) and we are looking for solutions to improve the reading speed. We need to improve the speed at wich we can browse and read though the entire database (200,000 records, but rapidly growing):

TAdoTable1.First
Repeat
TAdoTable1.Next
Until TadoTable1.Eof

and especially when browsing through a filtered data base.

Any idea how speed can be improved? We are also considering moving to SQL 2000 . Will this solve the performance issue?

Normand P

Delphi Developer

Wed, 28 Jan 2004 00:43:21 GMT

Re:Improving performance with MS Access files

We have a client/server application that uses MS Access 2000 files through an ADO connection (using Delphi 5.0 and Diamond ADO) and we are looking for solutions to improve the reading speed. We need to improve the speed at wich we can browse and read though the entire database (200,000 records, but rapidly growing):

TAdoTable1.First
Repeat
TAdoTable1.Next
Until TadoTable1.Eof

and especially when browsing through a filtered data base.

Any idea how speed can be improved? We are also considering moving to SQL 2000 . Will this solve the performance issue?

Shiv Kuma

Delphi Developer

Fri, 30 Jan 2004 07:52:30 GMT

Re:Improving performance with MS Access files

SQL Server 2000 will most definitely boost your speed if the number of
records is as large as you mention. Besides this (I don't know is Access
will allow these things), you can try playing with using ADOCommand, client
and server side cursors. I suggesting reading up on the ADO documentation to
better understand some of these aspects. ADO with SQL server 2000 is really
fast. You just need to "fine tune" your app and or database (indexes etc.).

Basically, stay away from "Tables". Use "Queries" instead. In terms of ADO,
that will mean - do not use ADOTable.

--
Shiv Kumar
The Delphi Apostle
http://www.matlus.com
http://www.delphisoap.com

Other Threads

1. TechTips: Improving Performance in Paradox/Delphi, or, beating MS-Access at its Own Game

2. Help, Improving BDE Performance...

3. Improving BDE performance.

4. Improving BDE Performance against ORACLE

5. Improving BDE Performance against ORACLE

6. MS Access Performance with new driver in 3.0?

7. How to improve performance

8. How to improve image display performance

9. How improve performance on MSSQL?

10. Improving my program's performance