recursive algorithm improvement

[Robert Samuel White via .NET 247]

I've managed to piece together a recursive algorithm for myparent-child table from different snippets of code on thiswebsite. It's actually pretty cool and it works, but it'sreally slow. At present, there are about 200 records, 8 ofwhich are top-level, and all have at least 3 levels beneaththem. In our real world environment, there are actually morethan 2000 records, and since it is already taking a full minuteto recursively build this smaller version of the list, I needsomething better. Could someone please recommend a speedier wayto accomplish the same task? I think my biggest problem is thatI don't know how to create a true relational relationship in asingle table, when I tried to do so I received a debug errorstating the relationship could not be established. Here is thecode that works, but I imagine it would run faster if I knewahead of time if there were any records beneath a node so as notto have to call the Add2Node function.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal eAs System.EventArgs) Handles MyBase.Load

Dim oleConnection As OleDb.OleDbConnection
Dim oleRoot As OleDb.OleDbDataAdapter
Dim oleDataSet As New DataSet()

oleConnection = NewOleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" & GetSetting("eNetwizard", "TechOpSuite\SuiteData","Management") & ";Mode=Share Deny None")
oleRoot = New OleDb.OleDbDataAdapter("SELECTUserId, Name, Key, Parent FROM tdataUsers WHERE Parent = 0 ORDERBY Name;", oleConnection)
oleRoot.Fill(oleDataSet, "theRoot")

TreeView1.Nodes.Clear()

Dim ParentRow As DataRow
Dim ParentTable As DataTable
ParentTable = oleDataSet.Tables("theRoot")

For Each ParentRow In ParentTable.Rows

Dim ParentNode As TreeNode

ParentNode = New TreeNode(ParentRow.Item("Name") & "(" & ParentRow.Item("Key") & ")")
TreeView1.Nodes.Add(ParentNode)
ParentNode.Tag = ParentRow.Item(0)
RecurseNode(ParentRow.Item(0), ParentNode)

Next ParentRow

End Sub



Function RecurseNode(ByVal UserId As Integer, ByVal theNodeAs TreeNode) As Integer

Try

Dim oleConnection As OleDb.OleDbConnection
Dim oleChild As OleDb.OleDbCommand
Dim oleDataSet As New DataSet()

oleConnection = NewOleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" & GetSetting("eNetwizard", "TechOpSuite\SuiteData","Management") & ";Mode=Share Deny None")
oleConnection.Open()
oleChild = New OleDb.OleDbCommand("SELECTUserId, Name, Key, Parent FROM tdataUsers WHERE Parent = " &UserId & " ORDER BY Name;", oleConnection)

Dim oleReader As OleDb.OleDbDataReader =oleChild.ExecuteReader

While oleReader.Read

Dim NewNode As TreeNode
NewNode = New TreeNode(oleReader.Item("Name") & "(" & oleReader.Item("Key") & ")")
Add2Node(TreeView1.Nodes, theNode, NewNode)
RecurseNode(oleReader.Item("UserId"), NewNode)

End While

oleReader.Close()
oleConnection.Close()

Catch e As Exception
MsgBox(e.Message)

End Try

End Function



Private Function Add2Node(ByVal TestNode As TreeNodeCollection,ByVal findnode As TreeNode, ByVal newnode As TreeNode) AsBoolean

Dim n As TreeNode

For Each n In TestNode

If n.Text = findnode.Text Then

n.Nodes.Add(newnode)

Add2Node = True

Exit Function

End If

Add2Node(n.Nodes, findnode, newnode)

Next

End Function

 

[Marina]

I would recommend, filling in the child nodes of a node, only when the user
expands that particular node. So first off, you get just the top level
nodes. Every time someone clicks one of them, you get all its *immediate*
child nodes. If someone clicks on one of those child nodes, you get its
immediate child nodes, and so forth.

Basically, you delay retrieving data until it is actually needed, which
should take very little time each time. By breaking it up this way, the user
experience should be greatly improved.

message I've managed to piece together a recursive algorithm for my parent-child
table from different snippets of code on this website. It's actually pretty
cool and it works, but it's really slow. At present, there are about 200
records, 8 of which are top-level, and all have at least 3 levels beneath
them. In our real world environment, there are actually more than 2000
records, and since it is already taking a full minute to recursively build
this smaller version of the list, I need something better. Could someone
please recommend a speedier way to accomplish the same task? I think my
biggest problem is that I don't know how to create a true relational
relationship in a single table, when I tried to do so I received a debug
error stating the relationship could not be established. Here is the code
that works, but I imagine it would run faster if I knew ahead of time if
there were any records beneath a node so as not to have to call the Add2Node
function.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim oleConnection As OleDb.OleDbConnection
Dim oleRoot As OleDb.OleDbDataAdapter
Dim oleDataSet As New DataSet()

oleConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
GetSetting("eNetwizard", "TechOpSuite\SuiteData", "Management") &
";Mode=Share Deny None")
oleRoot = New OleDb.OleDbDataAdapter("SELECT UserId, Name,
Key, Parent FROM tdataUsers WHERE Parent = 0 ORDER BY Name;", oleConnection)
oleRoot.Fill(oleDataSet, "theRoot")

TreeView1.Nodes.Clear()

Dim ParentRow As DataRow
Dim ParentTable As DataTable
ParentTable = oleDataSet.Tables("theRoot")

For Each ParentRow In ParentTable.Rows

Dim ParentNode As TreeNode

ParentNode = New TreeNode(ParentRow.Item("Name") & " (" &
ParentRow.Item("Key") & ")")
TreeView1.Nodes.Add(ParentNode)
ParentNode.Tag = ParentRow.Item(0)
RecurseNode(ParentRow.Item(0), ParentNode)

Next ParentRow

End Sub



Function RecurseNode(ByVal UserId As Integer, ByVal theNode As TreeNode)
As Integer

Try

Dim oleConnection As OleDb.OleDbConnection
Dim oleChild As OleDb.OleDbCommand
Dim oleDataSet As New DataSet()

oleConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
GetSetting("eNetwizard", "TechOpSuite\SuiteData", "Management") &
";Mode=Share Deny None")
oleConnection.Open()
oleChild = New OleDb.OleDbCommand("SELECT UserId, Name,
Key, Parent FROM tdataUsers WHERE Parent = " & UserId & " ORDER BY Name;",
oleConnection)

Dim oleReader As OleDb.OleDbDataReader = oleChild.ExecuteReader

While oleReader.Read

Dim NewNode As TreeNode
NewNode = New TreeNode(oleReader.Item("Name") & " (" &
oleReader.Item("Key") & ")")
Add2Node(TreeView1.Nodes, theNode, NewNode)
RecurseNode(oleReader.Item("UserId"), NewNode)

End While

oleReader.Close()
oleConnection.Close()

Catch e As Exception
MsgBox(e.Message)

End Try

End Function



Private Function Add2Node(ByVal TestNode As TreeNodeCollection, ByVal
findnode As TreeNode, ByVal newnode As TreeNode) As Boolean

Dim n As TreeNode

For Each n In TestNode

If n.Text = findnode.Text Then

n.Nodes.Add(newnode)

Add2Node = True

Exit Function

End If

Add2Node(n.Nodes, findnode, newnode)

Next

End Function