TClientDataSet works VERY SLOW with 100K+ rows

Question

i have problem retrieving data with Delphi TClientDataSet

Code with ADO:

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

Code above returns over 180k rows 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;

Following code returns same amount of rows(over 180k) but within 3-4minutes.

What's wrong with CDS? It's about 100-times slower then using ADO. Is it possible to fix it?

Answer 1

Code above returns over 180k rows in 3-6 seconds when using pure ADO.

For some reasons, I wouldn't expect the code you posted to return every single of the 180k records... I'd expect to see the first "X" records loaded after TADOQuery.Open is called, and then the last "X" records sent when TADOQuery.Last is called. Going while not EoF do instead of ".Last" would probably be better performance test since (I assume) you actually want to browse all the records.

When calling TClientDataset.Last when linked to a DataProvider, it most likely do the equivalent of a while not EoF do on your query, which transfer all the 180k records. Also, TClientDataset Insert/Append operation tend to get slower and slower the more records you have in it. My best guess is that it has to realloc it's memory buffer every now and then. If that's the case, I haven't found a way to tell the TClientDataset: "Hey! Brace yourself, 180k records incoming!"(Akin to TList.SetCapacity).

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