Updating Tclientdataset with dbExpress

[Karen99]

My code :

cds.close;
cds.commandtext := 'select * from a';
cds.open;
cds.first;
counter := 0;

while not cds.eof do
begin
inc(counter);
cds.edit;
cds.fieldbyname('counter').Asinteger := counter;
cds.post;
cds.next;
end;

if cds.ChangeCount > 0 then
cds.ApplyUpdates(0);

My problem :

CDS = TClientDataSet. Table A have 66410 records to update. The ApplyUpdates takes very long. I stopped my application after 12 min. Any way to speed this process up or are there another method to do this ?

I am using D7 and DbExpress.

thanks
Karen

 

[towerbase]

66410 records is a lot of records to update. How fast can your server process updates?

I would imagine that ApplyUpdates is causing a separate SQL request to be sent to the server for each of the 66410 updates.

If you had defined 'counter' as an auto_increment field when the table was created then there would be no need to do this updating.

Sorry, I guess this is not the answer you were looking for.

Andrew

Andrew
Hampshire, UK

 

[hilfy]

Another way to handle this would be to use ApplyUpdates in the loop, every so many records. For example, if you wanted to ApplyUpdates every 1000 records you would do something like this:

while not cds.eof do
begin
  inc(counter);
  cds.edit;
  cds.fieldbyname('counter').Asinteger := counter;
  cds.post;
  cds.next;
  if (counter mod 1000) = 0 then
    cds.ApplyUpdates(0);
end;

Be sure to do a final ApplyUpdates when you leave the loop to make sure that the final "set" of records gets updated.

-D

 

[Karen99]

Now, I have the problem that when I do a select with a Tsqlquery it works fine. But if I do that same select through a TClientDataSet I'm getting the message 'Insufficient memory for this operation'. Does a clientset use so much more memory than a sqlquery ? I want to use the TClientDataSet, because I am updating the data. I have closed and opened my delphi, restarted my pc etc. because I now that if you stop an app it does not release all the memory. But I don't think my problem is there.

Karen
Pretoria, South Africa

 

[towerbase]

TClientDataSet stores all the data in memory. So if you have a large dataset then this may not be practical both in terms of memory used and the time taken to load the records from disk.

I would suggest that 60,000 records is (far) too many.

Is the update you have described simply a test or do you have a real requirement to put sequence numbers on your data? If it is a real requirement then there seems to me to be a flaw in your design.

I don't have MS SQL Server so I don't know if this would work for you but in MySQL it is possible to add an auto_increment column which would be populated with numbers using one SQL statement (which is in the spirit of SQL).

ALTER TABLE a ADD counter INT AUTO_INCREMENT NOT NULL PRIMARY KEY

If table a already has a primary key then you would need to drop the primary key first.

ALTER TABLE a DROP PRIMARY KEY

This doesn't delete the primary key column but simply drops the primary key attribute from the column.



Andrew
Hampshire, UK

 

[Karen99]

It is not just a simple incremental. I just simlified it.
Substitute inc(counter) with :

PosFeaturePts := PosFeaturePts + 4 * SizeOf(Smallint) +
(NrFeatureParts - 1) * SizeOf(Integer) +
NrFeaturePoints * SizeOf(TPoint);

PosFeaturePts get saved to the table.

I am working with FileStreams etc. The code is already written, I am just changing it to work on Sql Server. It is currently running on Paradox.

So it is not so simple.

 

[Karen99]

It is not just a simple incremental. I just simlified it.
Substitute inc(counter) with :

PosFeaturePts := PosFeaturePts + 4 * SizeOf(Smallint) +
(NrFeatureParts - 1) * SizeOf(Integer) +
NrFeaturePoints * SizeOf(TPoint);

PosFeaturePts get saved to the table.

I am working with FileStreams etc. The code is already written, I am just changing it to work on Sql Server. It is currently running on Paradox.

So it is not so simple.

But thanks anyway.

 

[towerbase]

It's sometimes dangerous to simplify things too much because you can change the nature of the question and people waste time answering the wrong question.

You still haven't really supplied enough information but if PosFeaturePts, NrFeatureParts and NrFeaturePoints are columns in table A then there is no need to use TClientDataset and the table can be updated with just one SQL statement using TSQLDataset and it will probably only take a few seconds.



Andrew
Hampshire, UK

 

[VintageWine]

How about using a SQL Server stored procedure?

It might even be possible using a single update statement.

Delphi (bless its cotton socks) is not good at writing large record sets to databases. Reason is simple: there is no standard interface that does it. Rather than CDS you could try using ADO. Use a TADODataset. Suggest set CursorLocation to cdUseServer and experiment with CursorType and LockType.

Good luck
Simon