Ack need help - form takes 45sec to load !! 3

[MattBegg]

I am trying to form a treeview of part of my database.

Parent is UK
1st child is Country
2nd child is County
3rd child is Town
4th Child is Solicitor

The treeview works but with the procedure I have but it take 45secs to run.

I have pasted the code below (apologies it is not commented), I was wondering if using recursion would be faster, but I do not have a clue where to start.

I would be grateful if anyone could help

matt


Public gettree()
On Error Resume Next
Set mDBQuotes = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\Quotes.mdb&quot
TreeView1.Sorted = True
Set mNode = TreeView1.Nodes.Add()
mNode.Text = "UK"
mNode.Tag = mDBQuotes.Name
mNode.Image = "uk"

Dim rsCountry As Recordset
Dim rsCounty As Recordset, countyall As Recordset
Dim rsTown As Recordset, townall As Recordset
Dim rsSolicitors As Recordset

Set rsCountry = mDBQuotes.OpenRecordset("tblCountry", dbOpenDynaset)
Set countyall = mDBQuotes.OpenRecordset("tblCounty", dbOpenDynaset)
Set townall = mDBQuotes.OpenRecordset("tblTowns", dbOpenDynaset)

Dim countrycount As Integer, countycount As Integer, towncount As Integer, solicitorcount As Integer
Dim a As Integer, b As Integer, c As Integer, d As Integer

rsCountry.MoveLast
countrycount = rsCountry.RecordCount
rsCountry.MoveFirst

Dim intIndex As Integer, intIndex2 As Integer, intIndex3 As Integer
DoEvents

For a = 0 To countrycount - 1
Set mNode = TreeView1.Nodes.Add(1, tvwChild)
mNode.Text = rsCountry!Country
mNode.Tag = "Country"
mNode.key = rsCountry!countriesid & rsCountry!Country
mNode.Image = "fileC"
intIndex = mNode.Index

Set rsCounty = mDBQuotes.OpenRecordset("SELECT * From tblcounty WHERE countriesid = " & rsCountry!countriesid, dbOpenDynaset)

rsCounty.MoveLast
countycount = rsCounty.RecordCount
rsCounty.MoveFirst
frmSplash.ProgressBar1.Max = countycount

For b = 0 To countycount - 1
If rsCountry!countriesid = rsCounty!countriesid Then
Set mNode = TreeView1.Nodes.Add(intIndex, tvwChild)
mNode.Text = rsCounty!county
mNode.key = rsCounty!countiesid & rsCounty!county
mNode.Tag = "County"
mNode.Image = "folderC"
intIndex2 = mNode.Index
End If

Set rsTown = mDBQuotes.OpenRecordset("SELECT * From tbltowns WHERE countiesID = " & rsCounty!countiesid, dbOpenDynaset)

rsTown.MoveLast
towncount = rsTown.RecordCount
rsTown.MoveFirst

For c = 0 To towncount - 1
If rsCounty!countiesid = rsTown!countiesid Then
Set mNode = TreeView1.Nodes.Add(intIndex2, tvwChild)
mNode.Text = rsTown!town
mNode.key = rsTown!townsid & "town"
mNode.Tag = "Town"
mNode.Image = "folderC"
intIndex3 = mNode.Index
End If

Set rsSolicitors = mDBQuotes.OpenRecordset("SELECT * From tblSolicitors WHERE townsid = " & rsTown!townsid, dbOpenDynaset)

rsSolicitors.MoveLast
solicitorcount = rsSolicitors.RecordCount
rsSolicitors.MoveFirst

For d = 0 To solicitorcount - 1
If rsTown!townsid = rsSolicitors!townsid Then
Set mNode = TreeView1.Nodes.Add(intIndex3, tvwChild)
mNode.Text = rsSolicitors!CompanyName
mNode.key = rsSolicitors!CompanyName & " " & rsSolicitors!Postcode
mNode.Tag = "Solicitors"
mNode.Image = "solicitor"
End If
rsSolicitors.MoveNext
Next d
rsTown.MoveNext
Next c
rsCounty.MoveNext
Next b
rsCountry.MoveNext
Next a
End Sub

Regards

Matt

 

[RickSpr]

You're doing a number of things here that will slow everything down. First of all, each loop is starting with a MoveLast so you can get the record count. That's very slow, especially when you consider that the innermost loop may be executed hundreds or thousands of times. You don't need to get the record count. Just change your For loop into a Do While Not <recordset>.EOF loop. You won't need the record count variables or the For loop control variables any more. This should give you a pretty big improvement.

Next, you're testing within each loop to see if the record matches your criteria. But the OpenRecordset call has already limited the recordset to those records which match your criteria, so the If test is always True. Get rid of it. This will only give you a tiny improvement.

If that gives you enough speed, you can stop there, or you can continue.

Next, you're repeatedly opening recordsets within your loops. That's a moderately slow process, but improving it will take a little more effort. You're already opening all 4 recordsets with no criteria at the top of the procedure, which was a waste of time in your logic, but you need to for this. This gives you access to all the records in all the tables. To get just the records you want within the loops, replace each of the OpenRecordset calls with a FindFirst method call. In the FindFirst argument, specify the same WHERE clause you used in the OpenRecordset. Also, replace the MoveNext call with a FindNext call, and replace the Do While Not <recordset>.EOF with Do While Not <recordset>.NoMatch. This should give you a moderate speed improvement.

If this doesn't make it fast enough, there's one more thing you can do. It requires some pretty complicated logic, but you could stick a dummy, empty child node under each node that will be collapsed when you initially display the tree, instead of populating the branch with real child nodes. This will give the user a &quot;+&quot; to click to expand the node. When they do, you detect the empty node and load the children just when the expansion occurs. If it turns out there are no child nodes after all, you just delete the dummy child node, causing the &quot;+&quot; to go away. It can be a little confusing to the user when this happens, but it might be better than having to wait for the form while the tree is loaded. As I said, the logic is complicated, and I don't intend to work it out--you're on your own if you need to do this.

Some final mentions, not related to speed. You should always have a <recordset>.Close statement to balance each OpenRecordset statement, and you should always release object variables (such as mdbQuotes and all your recordsets) by Setting them = Nothing before exiting your procedure. This is good programming practice, but in this case it's more than that. There is a DAO bug in Access that sometimes causes resources not to be released if you rely on Access to clean this up for you. Not only does this waste memory, but it can cause Access to remain active in the TaskBar when you exit the application, and you have to use the Task List to kill it. The memory never gets released until you reboot.

Also, you have &quot;countyall&quot; and &quot;townall&quot; recordsets that you never use. Unless you left out some code for brevity, you ought to remove them. They don't slow you down or anything, but they're confusing to the next programmer who reads your code. Rick Sprague

 

[RickSpr]

There's one more speedup measure I forgot to mention. Make sure all the table fields you reference in your criteria are indexed, unless the table is very small. This could help quite a bit. Rick Sprague

 

[MattBegg]

Thanks a lot RickSpr

You got my vote!!

You gave me some very useful programming tips

B-) Regards

Matt

matt@begg-uk.co.uk

 

[Quehay]

Thanks Rick! I think you just gave me insight into some memory leak that was going on. I was doing quickie rs adjustments in code and not setting rs's to nothing. I found that Access was eating up the system resources until reboot.