Poor TStringGrid Performance

Question

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

It seems a little slower to me.

I get data from a database query. If I scroll through the query but don’t write the results to StringGrid, the process takes about 100 ms, so it cannot slow down the database.

After adding rows, StringGrid's performance is great.

Here is the code I'm 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 actual code is actually a bit more complicated (the columns of the table do not match the columns of the query), but the main idea

Answer 1

The solution was to immediately add all the values ​​to the string using the "Rows" property.

Now my code 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 reduced the time from 2 seconds to 50 ms.

Answer 2

Another thing that seemed to me to be very important when navigating through many records is the use of the correct TField variables for each field. FieldByName iterates through the Fields collection every time, so this is not the most efficient option. Before the loop defines 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; 

This along with BeginUpdate / Endupdate and a call to Query.DisableControls, if necessary.

Answer 3

The FieldByName used in the loop is very slow since it is evaluated every time. You have to do this from the loop, and then just use the results inside the loop.

Answer 4

The first optimization is to replace the very slow Query.FieldByName ('Value1') queries with 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. load all the content into a TStringList or any structure in memory, then use the OnGetText or OnDrawCell methods.

Answer 5

TStringGrid works fine for a small number of records, but do not try to use more than 10,000 records.

We had serious performance problems with the TAdvStringGrid from TMS (which is based on Delphi TStringGrid) when loading / sorting / grouping large sets of grids, but also when inserting one row at the top of the grid (extension of the node grid group). Also the memory usage was high. And yes, I already used startupdate / endupdate. Also other tricks. But after diving into the TStringGrid structure, I came to the conclusion that it cannot be fast for many records.

As a general tip (for large grids): use the OnGetText (and OnSetText) event. This event is used to fill the grid on demand (only displayed cells). Store data in your own data objects. This made our grid very fast (1,000,000 records are no longer a problem, loading in a few seconds!)

Answer 6

I believe it is slow because it needs to be redrawn every time you add a line. Since you are accepting values ​​from the query, I think you are better off using TDBGrid.

Sincerely.

Answer 7

If you know how many rows you are going to add, save the current row in a temporary variable, set the rowcount grid to accommodate the current row of the row and the rows you are going to add, then assign a new value to the rows (using the same row you saved), and do not add them. This will reduce the amount of background processing.

Answer 8

Try testing with AQTime or a similar tool (profilers).
Without any code, this is complicated, but I think the poor performance is related to FieldByName , not StringGrid .

FieldByName searches for:

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

If your dataset has many columns (fields), the performance will be lower.

Sincerely.

Answer 9

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

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

See ZhaawZ's answer for where he uses a pair of WM_SETREDRAW messages to turn redrawing on / off. If this works, use the “exclude the use of FieldbyName” trick in conjunction with the trick, and no need to waste time.

Answer 10

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

This avoids multiple calls to the OnPaint event.