Poor update performance...ideas

[Tsagoth]

I have a table with 90,000 rows that gets updated all at once every hour.

The way I do it is like this:

dtable := CurrentGameTask.SetDBByID(dbtSystem);
dtable.Refresh;
for i := 0 to (Systems.Count-1) do
begin
zstar := Systems[i];
rc := CurrentGameTask.LocateDBRecord(dtable,zstar.Idxno);
if (rc = True) then
begin
try
ms := zstar.SaveObserversToStream;
with dtable do
begin
Edit;
FieldByName('name').AsString := zstar.Name;
..other fields left out for size....
derb := FieldByName('observers') as TBlobField;
derb.LoadFromStream(ms);
ms.Free;
Post;
end;

This takes about 35 minutes to run. The first 10K records are handled about 150/second and then it starts dropping. By 30K it has flatlined at about 25-30 records/sec. What I find odd is that the thread doing this is running pretty much 100% cpu, while the mySQL machine is idle. When the DB was created, the thread was busy and the SQL machine was about 10% busy. So the update is telling me the thread is busy doing something, but it's not talking to the SQL box much.

I have another db component that I tried just to see how it would handle the same situation and it runs in 35 seconds with almost the identical loop.

Any ideas on why the MyDAC component is so slow ? The connection to the SQL server is TCP/IP but they are both vms on the same Server 2008 box, so network latency isn't an issue here.

[eduardosic]

Check the tables indexes, and use TMyQuery with SQL to update the table.

Locate in larger tables is slow, try to use Select/Update SQL statments.

[Dimon]

To solve the problem you can try using transactions. In many cases it increases performance when many records of table are changed.

[Tsagoth]

Well I didn't try transactions, but I did try changing it to use UPDATE SET WHERE and the elapsed time went from 35 minutes to 45 seconds.

That's a great improvement, but it seems very bizarre that the two components (TMyTable & TMyQuery) have such wildly different performance.

[Dimon]

The point is that TMyTable uses the same functionality as TMyQuery, only TMyTable always generates an update query itself and in TMyQuery you can set update queries yourself.
It seems that there are no key fields in your tables. To solve the problem try to set the TMyTable.KeyFields property. KeyFields may hold a list of semicolon-delimited field names.