Rectangle 27 0

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


  • Analyze/change all references in code from MeasurementsADOTable to MeasurementsADOQuery
  • Edit the actual query to be executed by setting the SQL property of MeasurementsADOQuery. (In runtime before opening: Measurements.SQL.Text := 'select top 10 * from measurements order by whatever')
  • I'm also assuming that you are displaying a grid or otherwise using a DataSource - change the DataSource component's "DataSet" property from MeasurementsADOTable to MeasurementsADOQuery
  • On your datamodule where "MeasurementsADOTable" currently resides, drop a TADOQuery and name it "MeasurementsADOQuery"
  • Set the Connection property of MeasurementsADOQuery to MyADOConnection (assuming this is the case based on the little code snippet provided.)
Note
Rectangle 27 0

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


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.

Note
Rectangle 27 0

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


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 ;

@Vlad: The process is always the same: connect TClientDataset(CDS) with a TDatasetProvider(DSP), after that point the property DSP.DataSet property to the ADOQuery you're bringing the data. In Delphi section on About.com you can find thousands of examples and in the embarcadero docwiki too.

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

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

Thanks, I will try to make a sample project, but still a demo with "Server (cursor location?) OpenForwardOnly cursor and an TCLientDataset with PacketRecords set to nonzero value" would be great! ;)

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.

how do I connect the TCLientDataset with my MSACCESS? Do you have some sample code you can share?

Note
Rectangle 27 0

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


Dont make the adotable active on startup and turning it true later is one way but still not really gonna help....use a adodataset and populate that rather as needed during runtime with your connection text. Only relevant data will be retrieved making it much faster.

Note
Rectangle 27 0

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


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)

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

Note
Rectangle 27 0

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


I have found ADO + Access w/Delphi to be painfully slow, for lots of things (big table reads like you're describing, but also inserts as well, etc). My answer became "Quit using ADO and Access altogether." Never did understand why it performed so poorly, especially when earlier technologies seemed not to.

Note