How to prevent Delphi ADO from loading the entire table into memory?

Question

I am not a Delphi programmer, but I I got an old Delphi 7 application that I need to fix and it is using ADO.

The database table (MS Accesss) contains +100,000 rows and when I set the ADOTable.Active=true it starts to load the entire table into RAM and that takes a lot of memory and time.

How can I prevent ADO to load the entire table? I tried to set the MaxRecords but it does not help.

Basically all we do is att program startup:

// Connect to database
DataModule.MyADOConnection.Connected:=true;

DataModule.MeasurementsADOTable.MaxRecords:=1;

// Open datatables
DataModule.MeasurementsADOTable.Active:=true;                  

After setting Active=true it starts to load the entire measurements into RAM and it takes TIME!

We are using the MSDASQL.1 provider. Perhaps it does not support the MaxRecords property?

How do I add some limiting query into this data object to only "load TOP 1 * from Measurements" ?

Answer 1

You could use TADOQuery to limit the result set with a sql query. Or you could use TADOTable and set the CursorLocation to a Server side cursor to prevent the client loading the complete resultset in memory.

Answer 2

You could use that adoTable with an Server OpenForwardOnly cursor and an TCLientDataset with PacketRecords set to nonzero value. Worked wonderfully when I had to write an app to pump data from MSSQL to Oracle on a customized way with tables with millions of records.

EDIT -> It would be something on the lines of this:

procedure ConfigCDSFromAdoQuery(p_ADOQ: TADOQuery; p_CDS: TClientDataset; p_Prov: TDatasetProvider);
begin
  If p_ADOQ.Active then p_ADOQ.Close;
  p_ADOQ.CursorLocation := clServer;
  p_ADOQ.CursorType := ctOpenForwardOnly;
  p_Prov.Dataset := p_ADOQ;
  p_CDS.SetProvider(p_Prov);
  p_CDS.PacketRecords := 100;
  p_CDS.Open; 
end ;

I've done this all by code, but most of that you can do in design-time.

Answer 3

This article is BDE specific, but applies to ADO or most client data access libraries.

http://dn.codegear.com/article/28160

I would recommend using TADODataSet (it's "closer" to the ADO layer than TADOQuery) and selecting only the data the client needs by providing a custom search form (date range, list of specific items, etc)

Good luck