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" ?

score 10 · Answer 1 · answered Dec 05 '08 at 12:00

10

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.

answered Dec 05 '08 at 12:00

Lars Truijens

42,837
6
126
143

Fabricio Araujo · Answer 2 · 2012-10-29T17:49:33.883

5

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.

edited Oct 29 '12 at 17:49

answered Dec 10 '08 at 16:34

Fabricio Araujo

3,810
3
28
43

how do I connect the TCLientDataset with my MSACCESS? Do you have some sample code you can share? – Vlad Oct 26 '12 at 17:45
1

@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. – Fabricio Araujo Oct 26 '12 at 18:23
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! ;) – Vlad Oct 26 '12 at 18:31

score 1 · Answer 3 · answered Dec 13 '08 at 10:58

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

score 0 · Answer 4 · answered Oct 25 '12 at 18:05

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.

score 0 · Answer 5 · answered Dec 07 '08 at 22:00

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.)
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
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')
Analyze/change all references in code from MeasurementsADOTable to MeasurementsADOQuery

Shahram Banazadeh · Answer 6 · 2017-12-30T21:51:01.867

0

use adoquery If you do not need any row and just want insert new row use sql command like this 'select * from myTable where id=-1' Since Id is autonumber no rows will return . or 'select * from myTable where 1=-1' But I think it is not good way for Insering data. Using adocommand is sure much better.

if you want X rows 'select top X * from myTable '

edited Dec 30 '17 at 21:51

answered Dec 30 '17 at 11:19

Shahram Banazadeh

500
1
5
15

sure with preventing load entire table in memory!!! using adoquery instead of adotable , whit a false condition statment < will prevent loading even a single row.if some one wants some of data using select top X will do it. But you are right I must edit post to explain what i meant better – Shahram Banazadeh Dec 30 '17 at 21:42

score 0 · Answer 7 · answered Mar 10 '22 at 05:43

In furthering Fabrico's answer above, I have legacy application which has a table with 177000 rows and 212 columns. Upon trying to open this table I get the error 'table already open' and no records are available for update. setting Table.CursorLocation := clUseServer; fixed this issue for me.

score -1 · Answer 8 · answered Dec 13 '08 at 10:06

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.

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

8 Answers8

Linked