Optimizing looping through dataset

Question

I have a code some thing like this

dxMemOrdered : TdxMemData;
while not qrySandbox2.EOF do
    begin
      dxMemOrdered.append;
      dxMemOrderedTotal.asCurrency := qrySandbox2.FieldByName('TOTAL').asCurrency;
      dxMemOrdered.post;
      qrySandbox2.Next;
    end;

this code executes in a thread. When there are huge records say "400000" it is taking around 25 minutes to parse through it. Is there any way that i can reduce the size by optimizing the loop? Any help would be appreciated.

Update

Based on the suggestions i made the following changes

dxMemOrdered : TdxMemData;
qrySandbox2.DisableControls;
while not qrySandbox2.Recordset.EOF do
    begin
      dxMemOrdered.append;
      dxMemOrderedTotal.asCurrency := Recordset.Fields['TOTAL'].Value;  
      dxMemOrdered.post;
      qrySandbox2.Next;
    end;
qrySandbox2.EnableControls;

and my output time have improved from 15 mins to 2 mins. Thank you guys

Answer 1

Without seeing more code, the only suggestion I can make is make sure that any visual control that is using the memory table is disabled. Suppose you have a cxgrid called Grid that is linked to your dxMemOrdered memory table:

var    
  dxMemOrdered: TdxMemData;
...

Grid.BeginUpdate;
try
  while not qrySandbox2.EOF do
  begin
    dxMemOrdered.append;
    dxMemOrderedTotal.asCurrency := qrySandbox2.FieldByName('TOTAL').asCurrency;
    dxMemOrdered.Post;
    qrySandbox2.Next;
  end;
finally
  Grid.EndUpdate;
end;

Answer 2

Some ideas in order of performance gain vs work to do by you:

1) Check if the SQL dialect that you are using lets you use queries that directly SELECT from/INSERT to. This depends on the database you're using.

2) Make sure that if your datasets are not coupled to visual controls, that you call DisableControls/EnableControls around this loop

3) Does this code have to run in the main program thread? Maybe you can send if off to a separate thread while the user/program continues doing something else

4) When you have to deal with really large data, bulk insertion is the way to go. Many databases have options to bulk insert data from text files. Writing to a text file first and then bulk inserting is way faster than individual inserts. Again, this depends on your database type.

[Edit: I just see you inserting the info that it's TdxMemData, so some of these no longer apply. And you're already threading, missed that ;-). I leave this suggestions in for other readers with similar problems]