Loading millions of records into a stringlist can be very slow

Question

how can i load millions of records from tadotable into a stringlist very fast?

procedure TForm1.SlowLoadingIntoStringList(StringList: TStringList);
begin
  StringList.Clear;
  with SourceTable do
  begin
    Open;
    DisableControls;
    try
      while not EOF do
    begin
      StringList.Add(FieldByName('OriginalData').AsString);
      Next;
    end;
   finally
   EnableControls;
   Close;
  end;
end;

Answer 1

in your loop you get the field. Search the field out of the loop

procedure TForm1.SlowLoadingIntoStringList(StringList: TStringList); 
var
  oField: TField;
begin
  StringList.Clear;   
  with SourceTable do   
  begin     
    Open;     
    DisableControls;     
    try       
      oField:= FieldByName('OriginalData');
      if oField<>Nil then
      begin
        while not EOF do
        begin       
          StringList.Add(oField.AsString);       
          Next;     
        end;   
      end; 
    finally    
      EnableControls;    
      Close;   
    end; 
  end;  
end;

Answer 2

Unfortunately, you can't do this quickly. It is an inherently slow operation that involves large amounts of CPU time and memory bandwidth to achieve. You could throw more hardware at it, but I suspect you should be re-thinking your task instead.

Answer 3

With 'millions of records' you may consider : 1/ Change your Query from

SELECT * FROM MYTABLE;

in

SELECT OriginalData FROM MYTABLE;

You'll use less memory and be more efficient.

2/ Look another component than TStringList depending on your needs.

3/ Look all good previous advices, mainly :

• don't use FieldByName
• direct link to the OleDB provider