Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
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.
Posted
Updated 29-Dec-18 1:30am
v2

If I understand the situation correctly, you're fetching portions of data from the database inside the recursion. If this is the case, then that's one thing that is slowing down.

If you need to populate the whole tree before usage, then try fetching all the data first and then using that pre-fetched data populate the tree. Most likely you need a hierarchical query for that so have a look at SQLite Query Language: WITH clause[^]

A little off-topic, but instead of concatenating the value to the SQL statement, use SqliteParameter Class (Microsoft.Data.Sqlite) | Microsoft Docs[^] . While it protects you from SQL injections and data type conversion problems, using parameters often also helps with performance.
 
Share this answer
 
Comments
MadMyche 29-Dec-18 11:13am    
Pre-Fetching would even be better if that data was stored into a different table, and then calling the data at startup. The only time then that the "prefetch" routine would need to be called is when the data is changed
You should place the beginupdate and endupdate outside the loop, see example here: Fast TreeView[^]

A better approach might be the Virtual Treeview: Virtual Treeview Implementation[^]
 
Share this answer
 
v2
I managed to get the FastTreeview class working in VB, had to make a few changes but 10 seconds to load up several hundred thousand items works a treat. I'll post an article about it along with search capabilities and checkbox items, I just need to get it working in my other grids in my project that cater for those facilities. Thanks for your help though - it spawned ideas and helped me solve the issue myself which I will share at some point soon ! :)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900