asked on

fill treeview fast from two tables with 10000 records

hw can a put the data from a two tables in a treeview fast.
the first tables have a 600 records , secon 9000 recors
first one have fields group_id , parent_id,Name
second - group_id ,group_kod,Name
I loock some code and its work but veri slow,about two min.
h>w can this code work fast

unit Unit1;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls, DB, ZAbstractRODataset, ZAbstractDataset,
  ZDataset, ZConnection,  ExtCtrls;
 
 
type
  TForm1 = class(TForm)
    TreeView1: TTreeView;
    Button1: TButton;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZQuery2: TZQuery;
    procedure Button1Click(Sender: TObject);
  private
  Procedure  Load_Tree ;
      { Private declarations }
  public
    { Public declarations }
  end;
 
var
  Form1: TForm1;
 
implementation
 
{$R *.dfm}
Type
  PDataRec = ^TData;
  TData = Record
                       ID  : Integer;
                       PID : Integer;
                       Text: String[150];
                      end;
 
function FindParent(ATreeView : TTreeView; ID : Integer) : TTreeNode;
var i : integer;
begin
  result := nil;
  i := 0;
  while (i < ATreeView.Items.Count) and
        (assigned(ATreeView.Items[i].Data)) and
        (PDataRec(ATreeView.Items[i].Data)^.ID <> ID) do
    inc(i);
  if i < ATreeView.Items.Count then
    result := ATreeView.Items[i];
end;
 
 
Procedure TForm1.Load_Tree;
var
  ANode : TTreeNode;
  RecordCache : PDataRec;
  RecordCacheList : TList;
  I, PreviousCount : Integer;
begin
  TreeView1.Items.Clear ;
  ZQuery1.Close;
  ZQuery1.SQL.Text := 'Select * from NGroups order by group_id';
  RecordCacheList := TList.Create;
  try
    ZQuery1.Open;
      while not ZQuery1.Eof do
    begin
      RecordCache := New(PDataRec);
      RecordCache^.ID := ZQuery1.FieldByName('GROUP_ID').AsInteger;
      RecordCache^.PID := ZQuery1.FieldByName('Parent_ID').AsInteger;
      RecordCache^.Text := ZQuery1.FieldByName('Name').AsString;
      RecordCacheList.Add(RecordCache);
      ZQuery1.Next;
    end;
    ZQuery1.Close;
    PreviousCount := RecordCacheList.Count;
    i := 0;
    While i < RecordCacheList.Count do
    begin
      If PDataRec(RecordCacheList.Items[i])^.PID = 0 then
      begin
        ANode := TreeView1.Items.AddChild(NIL,PDataRec(RecordCacheList.Items[i])^.Text);
        ANode.Data := RecordCacheList.Items[i];
        RecordCacheList.Delete(i);
      end
      else inc(i);
    end;
    While (RecordCacheList.Count > 0) and (PreviousCount <> RecordCacheList.Count) do
    begin
      i := 0;
      PreviousCount := RecordCacheList.Count;
      while i < RecordCacheList.Count do
      begin
        ANode := FindParent(TreeView1,PDataRec(RecordCacheList.Items[i])^.PID);
        if Anode <> NIL then
        begin
          ANode := TreeView1.Items.AddChild(ANode,PDataRec(RecordCacheList.Items[i])^.Text);
          ANode.Data := RecordCacheList.Items[i];
          RecordCacheList.Delete(i);
        end
        else inc(i);
      end;
    end;
    if RecordCacheList.Count > 0 then
      raise exception.Create('XXXX!');
  finally
    RecordCacheList.Free;
  end;
  Form1.Caption := Inttostr(TreeView1.Items.Count) ;
I := 0 ;
TreeView1.DoubleBuffered := true;
 
  ZQuery2.SQL.Clear ;
  ZQuery2.SQL.Text := 'Select group_kod,group_id,name from NGRP where group_id = :n';
    TreeView1.Items.BeginUpdate ;
  while i < TreeView1.Items.Count do
  begin
    begin
      ZQuery2.ParamByName('n').AsInteger := PDataRec(TreeView1.Items[i].Data)^.ID;
      ZQuery2.Open;
      While Not ZQuery2.Eof do
       begin
        New(RecordCache);
        RecordCache^.ID := ZQuery2.FieldByName('group_kod').AsInteger;
        RecordCache^.PID := 0;
        RecordCache^.Text := ZQuery2.FieldByName('group_kod').AsString +' - '+ZQuery2.FieldByName('Name').AsString;
        ANode := TreeView1.Items.AddChild(TreeView1.Items[i],RecordCache^.Text);
        ANode.Data := RecordCache;
        ANode.ImageIndex := 2;
        ANode.SelectedIndex := 3;
        ZQuery2.Next;
       end;
      ZQuery2.Close;
    end;
    inc(i);
    TreeView1.Items.EndUpdate ;
  end;
 
end;

Open in new window

SteveBay

There is no quick way to add nodes to a standard TreeView. If speed is what you need then you should try Virtual Treeview
http://www.soft-gems.net/index.php?option=com_content&task=view&id=12&Itemid=33

ASKER CERTIFIED SOLUTION

Russell Libby

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peev711

ASKER

thanks for the information(code). its work very fast .thanks again.

bokist

I agree with rllibby, your code is very confused.
assuming that I have understand your problem, I would suggest :
- improve your SQL code
- fill Treeview simplier

var
   show_first_time : boolean = True;
 
type
     TData = Record
           ID : integer;
           PID : integer;
           Text : string[150];
end;
 
procedure TForm1.FormShow(Sender: TObject);
begin
   if show_first_time  then
      begin
      show_first_time := False;
      Prepare_data;
      Load_Tree;
   end; 
end;
 
procedure TForm1.Prepare_data;
begin
   with zQuery1 do
      begin
      Close;
      SQL.Clear;
      SQL.Add('alter table NGRP add  NGroups_parent_id     integer null,');
      SQL.Add('                                         NGroups_name    varchar(150) null,');
      SQL.Add('                                         items_count    integer null');
      Execsql;
      SQL.Clear;
      SQL.Add('update NGRP set NGroups_parent_id = g.parent_id, NGroups_name = g.name, items_count = 0');
      SQL.Add('    from NGROUPS g');
      SQL.Add(' where NGRP.group_id = g.group_id');
      Execsql;
      SQL.Clear;
      SQL.Add('update NGRP set  items_count = isnull((select count(*) from NGRP g');
      SQL.Add('                                                                        where NGRP.group_id = g.group_id');
      SQL.Add('                                                                         group by group_id),0)');
      Execsql;
      Close;
   end;
end;
 
procedure TForm1.Load_Tree;
var
  NodeRec : ^Rec;
  group_id, group_kod : integer;
  MainNode, FirstNode : TTreeNode;
begin
  group_id := 0;
  group_kod := 0;
  NodeRec := nil;
  with TreeView1 do
     begin
     Items.Clear;
     Items.BeginUpdate;
     with zQuery1 do
        begin
        SQL.Clear;
        SQL.Add('Select group_id, group_kod, name, NGroups_parent_id, NGroups_name, items_count');
        SQL.Add('  from NGRP');
        SQL.Add(' order by group_id desc, group_kod');
        Open;
        while (not EOF) do
           begin
           New(NodeRec);
           NodeRec^.ID := fieldByName('group_id').asinteger;
           NodeRec^.PID := fieldByName('group_kod').asinteger;
           NodeRec^.Text := fieldByName('name').asstring;
           if group_id <> fieldbyname('group_id').asinteger  then
              begin
	      MainNode := TreeView1.Items.AddFirst(nil, inttostr(FieldByName('group_id).asinteger));
              group_id := fieldbyname('group_id').asinteger;
              MainNode.Data := NodeRec;
              group_kod := 0;
           end;
           if fieldByName('items_count').asinteger > 1 then
              begin
              if group_kod <> FieldByName('group_kod').asinteger then
                 begin
                 FirstNode := TreeView1.Items.AddChild(MainNode, inttostr(FieldByName('group_kod').asinteger));
                 FirstNode.Data := NodeRec;
              end;
              group_kod := FieldByName('group_kod').asinteger;
           end;
           Next;
        end;
     end;
     Close;
     Items.EndUpdate;
  end;
end;

Open in new window