TClientDataSet works VERY SLOW with 100K + rows

Question

I have a problem getting data using Delphi TClientDataSet

Code with ADO:

ADOQuery1.SQL.Text:='SELECT * FROM Table1 WITH (NoLock)'; DataSource1.DataSet:=ADOQuery1; DataSource1.DataSet.Open; DataSource1.DataSet.Last; 

The code above returns more than 180 thousand lines in 3-6 seconds when using pure ADO.

Same code with TClientDataSet:

 ADOQuery1.SQL.Text:='SELECT * FROM Table1 WITH (NoLock)'; CDS1.SetProvider(ADOQuery1); DataSource1.DataSet:=CDS1; DataSource1.DataSet.Open; DataSource1.DataSet.Last; 

The following code returns the same number of lines (more than 180 thousand), but within 3-4 minutes.

What happened to CDS? This is about 100 times slower than using ADO. Can this be fixed?

Answer 1

The code above returns more than 180 thousand lines in 3-6 seconds when using pure ADO.

For some reason, I would not expect the code you sent to return each of the 180 thousand records ... I expect to see the first "X" records loaded after calling TADOQuery.Open, and then the last "X" records, Sent when TADOQuery.Last is called. Going while not EoF do instead of ".Last" is likely to be the best performance test, since (I suppose) you really want to see all the records.

When you call TClientDataset.Last when it is associated with a DataProvider, it most likely fulfills the while not EoF do equivalent in your request, which passes all 180k entries. In addition, the add / add operation of TClientDataset tends to slow down and slow down, the more records you have. My best guess is that it should reallocate the memory buffer from time to time. If so, I have not found a way to tell TClientDataset: "Hey, pick yourself up, 180k records your inbox!" (Alkin TList.SetCapacity).

If you have an older version of delphi, one thing that might help is Midas Speed ​​Fix .

Answer 2

Try setting the CDS1.LogChanges property to False before loading the data. This must be done in code, as this is not a published property.

From the help file: For large datasets, True for LogChanges can greatly affect application performance.

You can then enable it after the initial boot.

Answer 3

It's quite old, but there are many new Delphi programmers right now. Here is a little scoop.

When using CDS in delphi, you actually create a memory table. Your request has probably changed hell.

To get the most out of CDS, use DBX components to capture data. These are the so-called “fast” cursors that do not create a temporary table with a cursor in the database. forward just doesn't do the fancy stuff that ADO does. If you need massive datasets with general update notifications and full control, use ADO. If you need to plow a ton of data in a hurry with a small load on the server where the CDS / DBX is lit.

The DBX method is more complicated. This is just a dragster. The first and next is the only thing that works for them. No updates, no obligations, just quick one-way relationships. Connect the combo DBX / provider / CDS, and you have everything. Speed ​​and ability to edit. Use the version number to find another user who does something with the data during editing. Examine your provider’s options to learn how to maximize flexibility. It is almost as complicated as in Delphi.