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

Expanding on @Ravaut123's answer I would suggest the following code:

Make sure your Query is not connected to any visual other component, and does not have any events set that fire on rowchanges because this will cause it to to updates on every change in the active record, slowing things way down.
You can disable the visual controls using disablecontrols, but not the events and non-visual controls.

...
SQLatable:= 'SELECT SingleField FROM atable ORDER BY indexedfield ASC';
AQuery:= TAdoQuery.Create(Form1);
AQuery.Connection:= ....
AQuery.SQL.Text:= SQLatable;  

Using a Query makes sure you only select 1 field, in the order that you want, this reduces network traffic. A table fetches all fields, causing much more overhead.

function TForm1.LoadingAllIntoStringList(AQuery: TAdoQuery): TStringList;  
var 
  Field1: TField; 
begin 
  Result:= nil;
  try
    if not(AQuery.Active) then begin
      AQuery.Open;
    end else begin
      AQuery.First;
    end;
    AQuery.DisableControls;
    AQuery.Filtered:= false;                    //Filter in the SQL `where` clause
    AQuery.FetchAll;                            //Preload all data into memory
    Result:= TStringlist.Create;
  except
    {ignore error, will return nil}
  end;
  try
    Result.Sorted:= false;                      //Make sure you don't enable sorting
    Result.Capacity:= AQuery.RecordCount;       //Preallocate the needed space     
    Field1:= AQuery.FieldByName('SingleField'); //Never use `fieldbyname` in a loop!
    while not AQuery.EOF do begin
      Result.Add(Field1.AsString);
      AQuery.Next;
    end; {while} 
    AQuery.EnableControls;
  except
    FreeAndNil(Result);
  end;   

If you want to load the data into the stringlist to do some processing, consider doing that in the SQL statement instead. The DB can use indexes and other optimizations that the stringlist cannot use.
If you want to save that data into a CSV file, consider using a build-in DB function for that.
e.g. MySQL has:

SELECT X FROM table1 INTO OUTFILE 'c:/filename_of_csv_file.txt'

Which will create a CSV file for you.
Many DB's have simular functions.

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

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 4

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

Answer 5

Seriously? Millions of records in a stringlist?

Ok, let's assume you really do need to take this approach...

There are some good suggestions already posted.

If you want to experiment with a different approach you could consider concatenating the individual records server side (via a stored procedure) and then returning the concatenated data as a blob (or possibly nvarchar(max)), which is basically the list of concatenated strings delimited by say a carriage return (assuming this is a reasonable delimiter for your needs).

You can then simply assign the returned value to the Text property of the TStringList.

Even if you cannot do all of the strings in a single hit, you could do them in groups of say 1000 at a time.

This should save you a ton of time looping around each record client side.

Answer 6

Is it sorted?

  // Turn off the sort for now
  StringList.Sorted := False;
  // Preallocate the space
  StringList.Capacity := recordCount;
  // Now add the data with Append()
  ...
  // Now turn the sort back on
  StringList.Sorted := True;