Poor performance of TStringGrid

Question

I have a TStringGrid with 10 columns. Adding 500 rows to it takes around 2 seconds. Is this normal performance?

It seems a bit slow to me.

I am getting the data from a database query. If I loop through the query but don't write the results to the StringGrid, the process takes around 100ms, so it's not the database that's slowing things down.

Once the rows are added, the StringGrid performance is fine.

Here is the code I am using

Grid.RowCount := Query.RecordCount;
J := 0;

while not Query.EOF do
begin
    Grid.Cells[0,J]:=Query.FieldByName('Value1').AsString;
    Grid.Cells[1,J]:=Query.FieldByName('Value2').AsString;
    Grid.Cells[2,J]:=Query.FieldByName('Value3').AsString;
    // etc for other columns.
    Inc(J);
    Query.Next();
end;

The real code is actually a bit more complex (the table columns do not correspond exactly to the query columns) but that's the basic idea

Answer 1

One other thing I have found to be very important when going through a lot of records is to use proper TField variables for each field. FieldByName iterates through the Fields collection every time so is not the most performant option. Before the loop define each field as in:

var
  f1, f2: TStringField;
  f3: TIntegerField;

begin
  // MyStringGrid.BeginUpdate; // Can't do this
  // Could try something like this instead:
  // MyStringGrid.Perform(WM_SETREDRAW, 0, 0);
  try
    while ... do
    begin
      rowvalues[0] := f1.AsString;
      rowvalues[1] := f2.AsString;
      rowvalues[2] := Format('%4.2d', f3.AsInteger);
      // etc 
    end;
  finally
    // MyStringGrid.EndUpdate; // Can't - see above
    // MyStringGrid.Perform(WM_SETREDRAW, 1, 0);
    // MyStringGrid.Invalidate;
  end;
end;

That along with BeginUpdate/Endupdate and calling Query.DisableControls if appropriate.

Answer 2

The solution was to add all values in a row at once, using the "Rows" property.

My code now looks like this:

Grid.RowCount := Query.RecordCount;
rowValues:=TStringList.Create;
J := 0;

while not Query.EOF do
begin
    rowValues[0]:=Query.FieldByName('Value1').AsString;
    rowValues[1]:=Query.FieldByName('Value2').AsString;
    rowValues[2]:=Query.FieldByName('Value3').AsString;
    // etc for other columns.
    Grid.Rows[J]:=rowValues;
    Inc(J);
    Query.Next();
end;

rowValues.Free; // for the OCD among us

This brought the time down from 2 seconds to about 50ms.

Answer 3

FieldByName used in a loop is very slow since it is calculated each time. You should do it out of the loop and then just use results inside of a loop.

Answer 4

TStringGrid works OK for a small number of records, but don't try it for more than 10.000 records.

We had severe performance problems with TAdvStringGrid from TMS (which is based on Delphi TStringGrid) when loading/sorting/grouping large grid sets, but also when inserting one row at the top of the grid (expanding a grid group node). Also memory usage was high. And yes, I used the beginupdate/endupdate already. Also other tricks. But after diving into the structure of TStringGrid I concluded it could never be fast for many records.

As a general tip (for large grids): use the OnGetText (and OnSetText) event. This event is used for filling the grid on demand (only the cells that are displayed). Store the data in your own data objects. This made our grid very fast (1.000.000 record is no problem anymore, loads within seconds!)