VirtualStringTree Correct/recommended use

Question

I have been using virtualstringtree for a while now. I use it for two different things, first a s a normal tree for selecting ,displaying data and secondly as a grid to show outputs from SQL statements.

All my data loaded in to the trees is from a database. For the tree example, i have a parentId field to distinguish the heirarchy, and for the grid examples i simply use an SQL statement with a customized record for each tree (that is unique).

My questions relates to the preferred/best way to populate the tree. I have read from the VST docs that you should use the onInitNode event along with rootnodecount. However i have found that using the AddChild() method to be very similar, even though it is not encouraged.

Let me show some (simplified) examples:

1. Heirarchy

type PData = ^rData;
    rData = packed record
      ID : Integer;
      ParentID : Integer;
      Text : WideString;
    end;

procedure Loadtree;
 var Node : PVirtualNode;
Data : PData;
 begin
    Q1 := TQuery.Create(Self);
            try
                Q1.SQL.Add('SELECT * FROM Table');
            Q1.Open;
            Q1.Filter := 'ParentID = -1'; //to get the root nodes
            Q1.Filtered := True;
            while not Q1.Eof do
            begin
                    Node := VST.AddChild(nil);
                    Data := VST.GetNodeData(Node);
                    Data.ID := Q1.Fields[fldID].AsInteger;
                    Data.ParentID := Q1.Fields[fldParentID].AsInteger;
                    Data.Text := Q1.Fields[fldText].AsString;
                    //now filter the query again to get the children of this node
                    PopulateChildren(Data.ParentID,Node); //add children to this node and do it recursively
                    Q1.Next;
            end;
       finally
          Q1.free;
      end;

end;

2. Grid

procedure LoadGrid;
var Node : PVirtualNode;
Data : PData;
begin
     Q1 := TQuery.Create(self);
        try
            Q1.SQL.Add('SELECT * FROM Table');
            Q1.Open;
            while not Q1.eof do
            begin
                    Node := VST.AddChild(nil);
                    Data.ID := Q1.Fields[fldID].AsInteger;
                    Data.Text := Q1.Fields[fldText].AsString;
                    Q1.Next;
            end;
    finally
            Q1.Free;
    end;
end;

So essentially i am bypassing the RootNodeCount and OnInitNode methods/property and using the old fashioned way to add nodes to the tree. It seems to work fine. Note in the example i create and destroy my queries at runtime.

The reason i started using the tree in this manner is that i could load all the data in the tree once and then free the TQuery after i had finished using it. I was thinking that regardless of keeping the TQuery alive/created i would still need to use my rData record to store the data, hence using up more memory if i did not destroy the TQuery. Currently my application uses about 250+MB when fully loaded, and can increase when i run SQL reports and display them in the VST. I have seen it use about 1GB of ram when i have run a SQL report with 20000+ nodes and 50+ columns. What i would like to know if the way i am using the VST incorrect with regards to minimizing memory usage?

Would i be better off creating a query for the lifetime of the tree and using the onInitNode event? So that when data is requested by the tree it fetches it from the TQuery using the onInitNode/OnInitChildren event (i.e. the pure virtual paradigm of the tree)? Thus i would need to keep the TQuery alive for the duration of the form. Would there be any memory benefit/performance benefit in using it this way?

In the above situations the i can see the difference for the grid example would be far less (if any) than the heirarchy - due to the fact that all nodes need to be initialized when populated.

Answer 1

The reason why AddChild() is not encouraged is that it breaks the virtual paradigm of the component - you create all the nodes, even though you may never need them. Say the query returns 100 records but the tree shows 10 nodes at the time. Now if the user nevers scrolls down you have wasted resources for 90 nodes as they are never needed (don't became visible). So if you're worried about memory usage, AddChild() is bad idea. Another thing is that if the resultset is big, populating the tree takes time and your app isn't responsive at that time. When using virtual way (RootNodeCount and OnInitNode) the tree is "ready" immediately and user doesn't experience any delay.

Then again, in case of (relatively) small resultsets using AddChild() might be the best option - it would allow you to load data in one short transaction. Ie in case of tree structure it would make sense to load whole "level" at once when user expands the parent node.

Using DB with VT is a bit tricky as the DB query is special resource too, with it's own constraints (you want to keep transactions short, it is relatively slow, the DB might only support one way cursor etc).
So I'd say it is something you have to decide on each individual case, depending on the use case and amount of data, ie

• small resultset is OK to load all at once with AddChild() or into internal data structure and then using it throught VT's events;
• loading one level at time is probably good compromise for trees;
• in case of very large resultsets loading in batches might give good perfomance and memory usage compromise but adds complexity to the code managing VT.

Answer 2

TQuery provide array-like access to records.

If you check with the documentation you can jump to each record using the RecNo property and you can jump to each field using Fields[i] (by it's index).

There is no holding back using TVirtualStringTree as virtual mode connecting with database.

Once you execute the query the data is available in memory as TDataSet anyway so why don't use it as a data container?

PS: Accessing fields using their index is faster than FieldByName.