Fastest way to fill string grid from database?

VCL TMS VCL UI Pack
1

Hi All

(win 7 (32 bit), Xeon 3gHz processor, 4mb ram, TMS string grid version 7.4.1.1)

I have 17000 records to fill in the fastest possible time..

I fill the string grid with 7 columns and 17000 rows from a database in a simple loop

----------start of code-----------------------------
     RowValues:=TStringList.Create;
     LoadProgressBar.Position:=0;
     LoadProgressbar.Visible:=True;
     for ridx := 1 to tbname.RecordCount - 1 do SGSearch.Rows[ridx].Clear();
     Main_f.SGSearch.colwidths[0]:=220;
     Main_f.SGSearch.colwidths[1]:=210;
     Main_f.SGSearch.colwidths[2]:=100;
     Main_f.SGSearch.colwidths[3]:=60;
     Main_f.SGSearch.colwidths[4]:=200;
     Main_f.SGSearch.colwidths[5]:=20;
     Main_f.SGSearch.colwidths[6]:=10;
      ridx:=0;
     LoadProgressBar.Min:=0;
     LoadProgressBar.Max:=tbname.RecordCount;
     //LoadProgressBar.Format:='Loading Data ... Please Wait';
     tbname.first;
     tbname.DisableControls;
     Main_f.SGSearch.BeginUpdate;
     try
       While not (tbname.eof) do
       begin
         actualrecords:=actualrecords+1;
         Begin
          Main_f.SGSearch.RowCount := ridx;
          RowValues.Insert(0,tbname.FieldByName('NAME').AsString);
          RowValues.Insert(1,tbname.FieldByName('ADDRESS').AsString);
          RowValues.Insert(2,tbname.FieldByName('NATIONALITY').AsString);
          RowValues.Insert(3,tbname.FieldByName('SEX').AsString);
          RowValues.Insert(4,tbname.FieldByName('RELIGION').AsString);
          RowValues.Insert(5,'N');
          RowValues.Insert(6,tbname.FieldByName('REF').AsString);
          Main_f.SGSearch.Rows[Main_f.SGSearch.RowCount]:=RowValues;
          ridx:=ridx+1;
         end;
         tbname.Next;
         LoadProgressBar.Position:=actualrecords;
       end;
     finally
       Main_f.SGSearch.EndUpdate;
     end;
----------end of code-----------------------------

It takes  15 seconds. !!!

User is complaining that it takes too long !

How to fill the grid in fastest possible way.?

Maybe I should load from text file ? , or from an excel file, or change settings in the string grid ?

who  has solved this problem and how ? what are the techniques to use ?

kind regards

Kamran


2

A couple of optimizations off the top of my head.

1. Instead of adding a row each time through the loop, add all of the rows at once, then fill them.

2. FieldByName is very inefficient. You're doing a string look-up on every field on every run through the loop, for a total of nearly 120,000 string compares. Assign the fields to field variables once before you enter the loop, then use these to access the field data. See http://fgaillard.com/2010/11/fieldbyname-findfield-too-convenient-to-be-honest/

There may be some string grid optimizations available too, but somebody from TMS will be better to help with these.

3
  1. Set RowCount to nr. of rows you need BEFORE starting to fill
    2) Access cells with grid.Cells[col,row]: string;
    3) Call FieldByName() outside the loop and use a TField variable in the loop

    These 3 things should already significantly increase performance.
4

Perfect .... Thank you