🚀 go-pugleaf

Hi,

I have a large BCB5 database application that I have just ported over to ADO
from BDE.  For Query's that return small numbers of records it works just
fine, but when a query retrieves over 10,000 records the performance goes
down badly.  I have ported my app to work on Access97, Access2002, and MS
SQL Server with the exact same behaviour, so I think that all the blame lies
in ADO and ADOExpress.

Description of the problem:

I have a simple query on a table with 240,000 records
ie.  "select Field1, Field2, Field3 from SampleTable order by Field4"

The problem I am having is reading through the recordset from the start to
the end (in this century)!

After I read past the 4,000 record, it gets noticably slower (no matter what
ADO parameters I tweak).  This slowdown gets worse every few thousand
records.  With a total of 240,000 records I have never yet had the patience
to reach the end of the recordset. I have tested this on Access97, Access
2002 and MS SQL server with the exact same results (although MS SQL starts
out a little faster).  I have tried adjusting the CacheSize from 1 to
100,000 and set the LockType to ltReadOnly.  I have tried local vs. server
side cursors.  Nothing seems to help.

The VERY annoying part of this situation is that using the BDE with
Access97, I could rip through this test in about 50 seconds...  (Thank god
for progress...NOT! )

If there is anybody out there that can give me tips for optimizing ADO I
would be very grateful to hear from you.

Ron