Help in speeding up a procedure

[monrosal]

I need help in speeding up a process of looping through a recordset and grabbing a value while I'm looping.
There is about 40,000 records so it takes a long time to do.

First let me display an example table I'm using:
level levelAbove qty
------ ----------- -----
levelA none 1
levelB levelA 1
levelC levelB 2
levelD levelB 3

The "levelAbove" field is a field that stores the level above a specific level. So, levels levelC and levelD belong to levelB.

So let's use levelD. First I want to grab the qty of levelD. Next I look at levelD's levelAbove which is levelB and move to levelB's record then I grab that quantity which is 1. Then I search for the levelB's levelAbove which is levelA and move to that record and grab levelA' quantity which is 1. So it's like a continuous loop until I get to the top level.

Then I multiply the quantities 3 * 1 * 1.
Then I put this value in a table so I could do a sum for all the values.

My code looks something like this

sql = "select * from table"
set rst = currentdb.openrecordset(sql)
strlevel = rst("level&quot
product = 1
Do until rst.eof
Do until strlevel = "none"
rst.FindFirst "level='" & strlevel & "'"
product = rst!qty * product
strLevel = rst!levelAbove
Loop
With rst2
.AddNew
!level = rst("level&quot
!qty = product
.Update
End With
rst.MoveNext
Loop

Can I do this different so it speeds up the process?
Thanks for your help

Ramon

 

[scriverb]

You didn't talk about the structure of your table in your question. The first thing that I would suggest is to create an index in your table on the field "level". You are performing a rst.FindFirst on this field many many times in this looping process. If the field is not indexed then the FindFirst operations starts at record one of the recordset and searches through the entire recordset to find the record. With an indexed field it would be much faster.

Let's start with that first. Get back with me and let me know if that helps. Bob Scriver

 

[scriverb]

I have looked over your code and found some problems with it that would make it run a very long time. You didn't say if you were satisfied with the output table from your code. When I ran it it created a huge number of records all of which were for levelA with quanty of 1. So, I modified it to look like this. I added some declarations to make it work and I created my own table names. You will have to modify to fit yours. I think this will run much quicker even without the indexing but that is still needed.

Dim rst As Recordset
Dim rst2 As Recordset
Dim Product As Long
Dim strLevel As String
Dim strPointerLevel As String
Dim SQL As String
SQL = "select * from tableINPUT"
Set rst = CurrentDb.OpenRecordset(SQL)
Set rst2 = CurrentDb.OpenRecordset("tableOUTPUT", dbOpenDynaset)
rst.MoveFirst
strLevel = rst("level&quot
strPointerLevel = rst("LevelAbove&quot
Product = rst("qty&quot
Do Until rst.EOF
Do Until strPointerLevel = "none"
rst.FindFirst "level='" & strPointerLevel & "'"
If rst.NoMatch then
strPointerLevel = "none"
else
Product = rst!qty * Product
strPointerLevel = rst!levelAbove
end if
Loop
With rst2
.AddNew
!level = strLevel
!qty = Product
.Update
End With
rst.FindFirst "level='" & strLevel & "'"
rst.MoveNext
If Not rst.EOF Then
strLevel = rst("level&quot
strPointerLevel = rst("LevelAbove&quot
Product = rst("qty&quot
End If
Loop

With the data from your example it creates four records levelA 1, levelB 1, levelC 2, levelD 6. Take a look at the changes in the code and test if out with the table names(Red) changed to your tables.

Let me know how it works out.
Bob Scriver

 

[scriverb]

As a long shot, if the number of nexted pointers is only going to be four(4) like in your example you could use a query to perform this process. But, if it is an infinite number of pointer which is only know by looping through them then this query idea is no good.

SELECT Table.Level, NZ(

![qty],1)*NZ([Table_1]![qty],1)*NZ([Table_2]![qty],1)*NZ([Table_3]![qty],1) AS Qty
FROM ((

LEFT JOIN

AS Table_1 ON Table.LevelAbove = Table_1.Level) LEFT JOIN

AS Table_2 ON Table_1.LevelAbove = Table_2.Level) LEFT JOIN

AS Table_3 ON Table_2.LevelAbove = Table_3.Level
ORDER BY Table.Level;

You will have to go through the query and change the word Table to whatever your input table is called.
Bob Scriver

 

[cmmrfrds]

The model you have to determine relationships (side by side) is difficult to work with in SQL. There is no easy way in sql to retrieve the recursive relationship. I saw this problem addressed yesterday in thread701-320225 where a different model of storing the relationship was suggested. It is a tree structure and much easier to combine in sql statements (would obviate the need to use the loop in vba code). This model seems much better for Org Chart and BOM type of relationships. FYI.

 

[monrosal]

Bob,

BTW thanks for your help, but I do Index the level and levelAbove fields. It helped, but I need it to run even faster. Wouldn't adding more declarations slow it down? I also left a lot of code out to make it simpler to explain. I haven't tried your code yet, but once I do I'll let you know how it works.

Ramon