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

First optimization is to replace very slow Query.FieldByName('Value1') calls by a local TQuery.

var
  F1, F2, F3: TField;

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

If this is not enough, use the grid in virtual mode, i.e. retrieve all content in a TStringList or any in-memory structure, then use the OnGetText or OnDrawCell methods.

Answer 5

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!)

Answer 6

I believe it's slow because it has to repaint itself everytime you add a row. Since you are taking the values from a query i think it would be better for you to use a TDBGrid instead.

Best regards.

Answer 7

If you know how many rows you're about to add, store the current rowcount in a temporary variable, set the grid's rowcount to accommodate the current rowcount plus the rows you're about to add, then assign the new values to the rows (using the former rowcount you stored) rather than adding them. This will reduce a lot of background processing.

Answer 8

Try testing with AQTime or similar tool (profilers).
Without any code is difficult, but I thinks thar the poor performance is due to FieldByName, not StringGrid.

FieldByName make a liear search:

  for I := 0 to FList.Count - 1 do
  begin
    Result := FList.Items[I];
  ...

If your Dataset have many columns (fields) the performance will still be lower.

Regards.

Answer 9

I was going to say "why not just use beginupdate/endupdate?" but now I see that the regular string grid doesn't support it.
While googling that, I found a way to simulate beginupdate/endupdate:

http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_21832072.html

See the answer by ZhaawZ, where he uses a pair of WM_SETREDRAW messages to disable/enable the repainting. If this works, use in conjunction with the "eliminate use of FieldbyName" trick, and it should take no time to draw.

Answer 10

Set Grid.RowCount = 2 before the loop then when the loop is finished set the rowcount to the correct value.

That avoids lots of calls to the OnPaint event.

Answer 11

In my case it turned out that the Debug build was slow and the Release build was fast - a Heisenbug.

More specifically, FastMM4 FullDebugMode triggered the slowness.