I have a Product-Hierarchy which pre-populates a treeview (I cannot use population on the fly due to some other features of the application - so I have to go the pre-populate route).
Once I start getting several thousand entries to populate, it starts taking a long time to populate, even with a beginupdate and endupdate on the treeview.
The SQL Queries and database format are irrelevant in the overall performance (there is a small bottleneck but its livable) - its the population of the Treeview itself.
I have looked at the FastTreeView code elsewhere on CodeProject which utilises Dictionaries/Arrays to be populated then populate the treeview - the example the chap provides is lightning fast but I cannot get it to work with what I am trying to achieve. I know the answer will probably be just that - Dictionaries or an List Of but I cannot get it to function correctly. So here is my function (its the recursive part).
The sub that calls this function, first calls a Setup sub that populates the root nodes, sets beginupdate, calls the FindNode function below then when it eventually returns, calls endupdate on the treeview.
I have approx 130,000 items and again, yes, I need to pre-populate rather than on-the-fly, the reason being I use this function to populate a checkbox type treeview which needs to be able to save what has been selected - and I want to keep things nice and tidy by utilising the same base-populating code.
So if anyone has any ideas of being able to reduce the 50 minutes or so to populate this down to a few seconds I would be in your debt :)
<pre> Private Function FindNode(ByVal _nodeCollection As TreeNodeCollection) As TreeNode
Dim tmpNode As TreeNode
Dim SQLStr As String
Dim TotX As Long = 0
Dim Z As Long = 0
Try
For Each _child As TreeNode In _nodeCollection
TotX = 0
SQLStr = "SELECT "
SQLStr = SQLStr & "Product_Category_HierarchyID"
SQLStr = SQLStr & ","HierarchyName"
SQLStr = SQLStr & " FROM Product_Category_Hierarchy"
SQLStr = SQLStr & " WHERE Product_Category_Hierarchy_ParentID=" & _child.Tag
SQLStr = SQLStr & " ORDER BY HierarchyName"
Using da As New SQLite.SQLiteDataAdapter(SQLStr, SQLDB)
da.SelectCommand.CommandTimeout = 0
Using ds As New DataSet
da.Fill(ds, "CreateTree")
If ds.Tables(0).Rows.Count > 0 Then
TotX = ds.Tables(0).Rows.Count - 1
For Z = 0 To TotX
_child.Nodes.Add(ds.Tables(0).Rows(Z).Item("HierarchyName").ToString).Tag = ds.Tables(0).Rows(Z).Item("Product_Category_HierarchyID").ToString
Next
End If
End Using
End Using
tmpNode = FindNode(_child.Nodes)
If Not tmpNode Is Nothing Then
If _CloseTreeNodes = True Then
_child.Collapse()
End If
Return tmpNode
End If
If _CloseTreeNodes = True Then
_child.Collapse()
End If
Next
Catch ex As Exception
Messagebox.show(ex.Message)
End Try
Return Nothing
End Function
What I have tried:
Tried putting the beginupdate and endupdate within the loop above, it makes no difference.
I think Im less than a half dozen lines of code to achieve a few seconds or several seconds to populate but have spent about 4 hours, maybe longer looking at this without success.