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

Question

I'm not a Delphi programmer, but I have an old Delphi 7 application that I need to fix and it uses ADO.

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

How can I prevent ADO from loading the entire table? I tried installing MaxRecords, but that does not help.

Basically, all we do is run the program:

// Connect to database DataModule.MyADOConnection.Connected:=true; DataModule.MeasurementsADOTable.MaxRecords:=1; // Open datatables DataModule.MeasurementsADOTable.Active:=true; 

After setting Active = true, it starts loading all measurements into RAM and TIME is required!

We use the provider MSDASQL. 1. Perhaps this does not support the MaxRecords property?

How to add some restrictive query to this data object only for "loading TOP 1 * from dimensions"?

Answer 1

You can use TADOQuery to limit the result set with an SQL query. Or you can use TADOTable and set CursorLocation to a server-side cursor so that the client does not load the full result set in memory.

Answer 2

You can use this adoTable with the Server OpenForwardOnly cursor and TCLientDataset with PacketRecords set to a non-zero value. It worked wonderfully when I had to write a custom data transfer application from MSSQL to Oracle with tables with millions of records.

EDIT -> This would be something like that:

 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 did all this with code, but most of this can be done during development.

Answer 3

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

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

I would recommend using TADODataSet (it is "closer" to the ADO layer than TADOQuery) and select only the data that the client needs, providing a custom search form (date range, list of specific elements, etc.).

Good luck.

Answer 4

• In your datamodule where "MeasurementsADOTable" is currently located, drop TADOQuery and name it "MeasurementsADOQuery"
• Set the Connection MeasurementsADOQuery property for MyADOConnection (if so, based on a small piece of code.)
• I also assume that you are showing a grid or otherwise using a DataSource - change the DataSource "DataSet" property from MeasurementsADOTable to MeasurementsADOQuery
• Edit the actual query that will be executed by setting the SQL MeasurementsADOQuery property. (At runtime, before opening: Measurements.SQL.Text: = 'select top 10 * from the measurement order in any way)
• Analysis / change of all links in the code using MeasurementsADOTable for MeasurementsADOQuery

Answer 5

Do not make adotable active at startup and turning it into truth later, this is one way, but still will not help ... use adodataset and fill it as needed at runtime with the text of your connection. Only relevant data will be received, which will make it much faster.

Answer 6

use adoquery If you don’t need a row and just need to insert a new sql sql command, like this 'select * from myTable, where id = -1' Since Id is autonumber, rows are not returned. or 'select * from myTable, where 1 = -1' But I think this is not very good for Insering data. Using adocommand is much better.

if you want X lines of 'select top X * from myTable'

Answer 7

I found that ADO + Access w / Delphi is painfully slow for many things (a large table is read as you describe, but it also inserts, etc.). My answer was "Stop using ADO and Access in general."

I never understood why it worked so badly, especially when technology did not seem to be before.