Help with performance problems

[Nick via AccessMonster.com]

Hello,

My VBA database application that I have written is starting to have some
problems with performance; as the number of records have grown, the
processing time has greatly increased. I am trying to find if there is any
way to cut down processing time short of a major overhaul of my code (which
it badly needs - the development was more of a series of random leaps than an
organized on-paper process).

The app manages the creation of parts and controls the sequence of inputs;
there are 3 tables involved where the slow performance occurs.
-table A contains all the part information (1 unique ID per record)
-table B contains information about steps in the part creation process (what
steps must be complete for data entry into the next step, what steps will be
"frozen" if data is entered in a later step),
-table C contains one record per (ID in table A) and (step in table B), and
tracks the completion/frozen states of each kit on each step

The pseudocode for the slow process is as follows:

Set rsA = db.openrecordset(SELECT Unicode FROM table A) 'Just loads up the
unique ID
Set rsB = db.openrecordset(table B)

rsA.Movefirst
Do Until rsA.EOF
Set rsC = db.openrecordset(IDs from table C that are equal to the current
ID on table A)
rsB.Movefirst
Do Until rsB.EOF
'Finds the first step that must be complete from rsB
'Uses FindFirst to find that step in rsC
'If Step = Complete, jump to current step in rsC, (various commands)
'Moves to the next record of rsB
Loop
'Moves to the next record of rsA
Loop

The nested Do loops take awhile to complete, but I really can't think of
another way to go about this. I am fairly sure that the problem just comes
from jumping through recordsets so much. All of the recordsets I am using
are SQL based to select only the vital information, but they still take
awhile to complete. Predefined queries are out of the question, because the
user must be able to change table B at any time and not have to change any
other tables/configurations (the application conforms to the changes).

Any help would be great appreciated. Thanks for reading this, and thanks in
advance for any advice.

-Nick

 

[Klatuu]

Unless there is something I am missing, I think you could improve performance
by constructing a query the will return only those records you need to take
action on.

 

[nickf123 via AccessMonster.com]

Sorry for the slow response, the performance issue had been bumped to the
back burner for a week. I have been looking into trying to improve it with a
query, but that is where I stumble...

Essentially, what the program is doing: looking at a part in a table for a
certain step, looking up that step in another table to see what previous step
(s) must be completed, looking back at the first table to see if those steps
have been completed, then returning to the current record to say that yes
(ready for next step) or no (previous steps not complete).

I'm having trouble wrapping my head around the looping-type structure that is
present, and coincidentally, that part is the real hangup in performance. Is
it possible to write a query that would work with this type of structure?
The only catch is that the query needs to be built in VBA; it can't be a
query saved on the FE database application, because the entire program's
purpose is to dynamically adjust to the Step table (so the admin should only
ever have to touch one table to make changes). Currently, my app works that
way, but very slowly.

I have only a very basic understanding of Access; I was assigned this job
because I was somewhat familiar with VBA. I still have trouble writing
append queries, for example; I'd rather handle everything through recordsets,
which is why I have very few queries present. Any guidance you could give
would be appreciated.

-Nick

 

[Klatuu]

Can you send me the name of the tables you are using and the names of the
fields invoved in making the determination, and what is done when a required
step is completed and what is done what a step is not completed.

If fact, if you can post the code it will help.

It may actually be possible to do it with a query or it may be that a query
joining the two tables can be use as a recordset that would improve the
performance.

Don't worry about not being able to use a stored query. There are multiple
ways to handle variable requirements for a query that would not require the
user any additional work.

 

[Nick via AccessMonster.com]

The table names are "Tbl - New Kits Progress" (stored in strNewKitTable) and
"Tbl - New Kits Progress" (strNewKitsReadyForSteps) [terrible variable names,
I know... ] Also, there is a 3rd table called "Tbl - Step Definitions",
stored as the recordset "StepRS", which is used globally.

In the below code, the basic idea is to step through "Tbl - New Kits
Progress" (contains one unique ID per part, also stores the part data); for
each ID, it will get the relevent records from "Tbl - New Kits Progress"
through a query recordset. The program jumps to the step number that the
user is on, finds that step's prerequisite steps in the New Kits Progress
table, sees if those prerequisites are complete, then will set the current
step to Ready = (True or False). It follows the same procedure inside the
loops for a freeze-step (if a certain step is complete, a previous step may
be disabled).

This code is more lengthy than a good procedure should be, but this project
has grown like a fungus; it started really small and just exploded with no
time (literally) for housekeeping.


Public Sub Check_Prerequisites_and_Frozen()

Dim db As DAO.Database
Dim NewRS, CompletionRS, ModRS As DAO.Recordset
Dim intCounter, intPreReq, intPRCounter As Integer
Dim All_Prerequisites_Met As Boolean
Dim intFreezeCounter, intFreeze As Integer

Set db = CurrentDb

'Switch, determines which table to look at
If boolIsNew Then
Set NewRS = db.OpenRecordset("SELECT Unicode FROM [" & strNewKitTable & "]
;")
Set CompletionRS = db.OpenRecordset(strNewKitsReadyForSteps)
ElseIf Not boolIsNew Then
Set NewRS = db.OpenRecordset("SELECT Unicode FROM [" & strModKitTable & "]
;")
Set CompletionRS = db.OpenRecordset(strModKitsReadyForSteps)
End If

If NewRS.EOF And NewRS.BOF Then Exit Sub
NewRS.MoveFirst
StepRS.MoveFirst

Do Until NewRS.EOF
For intCounter = 1 To NumberofSteps
intPRCounter = 1
intFreezeCounter = 1
StepRS.FindFirst ("Number = " & intCounter)
*'Assumes all prerequisites will be met
All_Prerequisites_Met = True
Do Until Nz(StepRS.Fields("Prerequisite " & intPRCounter), "") = ""
intPreReq = StepRS.Fields("Prerequisite " & intPRCounter)
CompletionRS.FindFirst ("[ID Step] = " & intPreReq & " AND
Unicode = '" & NewRS("Unicode") & "'")
'*Handles the case where a prerequisite is not on the list to be
modified
'*Goes ahead and counts it as finished, skips the cycling
If Not boolIsNew Then
If Not CompletionRS("To Be Modified") Then Exit Do
End If
If Not CompletionRS("Complete") And Not CompletionRS.NoMatch Then
All_Prerequisites_Met = False
Exit Do
End If
intPRCounter = intPRCounter + 1
Loop
Do Until Nz(StepRS.Fields("Freeze " & intFreezeCounter), "") = ""
intFreeze = StepRS.Fields("Freeze " & intFreezeCounter)
CompletionRS.FindFirst ("Unicode = '" & NewRS("Unicode") & "' AND
[ID Step] = " & intFreeze)
If CompletionRS("Complete") = True And Not CompletionRS.NoMatch
Then
CompletionRS.MoveFirst
CompletionRS.FindFirst ("Unicode = '" & NewRS("Unicode") &
"'" & _
" AND [ID Step] = " & StepRS("ID Step"))
CompletionRS.Edit
CompletionRS("Frozen") = True
CompletionRS.Update
ElseIf CompletionRS("Complete") = False And Not CompletionRS.
NoMatch Then
CompletionRS.MoveFirst
CompletionRS.FindFirst ("Unicode = '" & NewRS("Unicode") & _
"' AND [ID Step] = " & StepRS("ID Step"))
CompletionRS.Edit
CompletionRS("Frozen") = False
CompletionRS.Update
End If
intFreezeCounter = intFreezeCounter + 1
Loop
If Not All_Prerequisites_Met Then
CompletionRS.FindFirst ("[ID Step] = " & StepRS("ID Step") & _
" AND Unicode = '" & NewRS("Unicode") & "'")
If CompletionRS("Ready") And Not CompletionRS.NoMatch And
boolIsNew Then
CompletionRS.Edit
CompletionRS("Ready") = False
CompletionRS("When Ready") = Empty
CompletionRS.Update
End If
ElseIf All_Prerequisites_Met Then
CompletionRS.FindFirst ("[ID Step] = " & StepRS("ID Step") & _
" AND Unicode = '" & NewRS("Unicode") & "'")
If Not CompletionRS("Ready") And Not CompletionRS.NoMatch Then
CompletionRS.Edit
CompletionRS("Ready") = True
CompletionRS("When Ready") = Date
CompletionRS.Update
End If
End If
Next intCounter
NewRS.MoveNext
Loop

CompletionRS.Close
NewRS.Close
Set CompletionRS = Nothing
Set NewRS = Nothing

End Sub


I tried to exaggerate the indents a bit for clarity, hopefully it is readable.
Thanks for taking the time to look at this. If anything isn't clear, let me
know and I will clarify.

Again, thank you.

-Nick

 

[Klatuu]

Sorry to be so long getting back to you, Nick. I have spent a good deal of
time looking at this to see if there was any way I could see to make this
work any faster. Given the complexity of what you are doing, I don't think I
could construct queries that might make it any faster.

The table names are "Tbl - New Kits Progress" (stored in strNewKitTable) and
"Tbl - New Kits Progress" (strNewKitsReadyForSteps) [terrible variable names,
I know... ] Also, there is a 3rd table called "Tbl - Step Definitions",
stored as the recordset "StepRS", which is used globally.

In the below code, the basic idea is to step through "Tbl - New Kits
Progress" (contains one unique ID per part, also stores the part data); for
each ID, it will get the relevent records from "Tbl - New Kits Progress"
through a query recordset. The program jumps to the step number that the
user is on, finds that step's prerequisite steps in the New Kits Progress
table, sees if those prerequisites are complete, then will set the current
step to Ready = (True or False). It follows the same procedure inside the
loops for a freeze-step (if a certain step is complete, a previous step may
be disabled).

This code is more lengthy than a good procedure should be, but this project
has grown like a fungus; it started really small and just exploded with no
time (literally) for housekeeping.


Public Sub Check_Prerequisites_and_Frozen()

Dim db As DAO.Database
Dim NewRS, CompletionRS, ModRS As DAO.Recordset
Dim intCounter, intPreReq, intPRCounter As Integer
Dim All_Prerequisites_Met As Boolean
Dim intFreezeCounter, intFreeze As Integer

Set db = CurrentDb

'Switch, determines which table to look at
If boolIsNew Then
Set NewRS = db.OpenRecordset("SELECT Unicode FROM [" & strNewKitTable & "]
;")
Set CompletionRS = db.OpenRecordset(strNewKitsReadyForSteps)
ElseIf Not boolIsNew Then
Set NewRS = db.OpenRecordset("SELECT Unicode FROM [" & strModKitTable & "]
;")
Set CompletionRS = db.OpenRecordset(strModKitsReadyForSteps)
End If

If NewRS.EOF And NewRS.BOF Then Exit Sub
NewRS.MoveFirst
StepRS.MoveFirst

Do Until NewRS.EOF
For intCounter = 1 To NumberofSteps
intPRCounter = 1
intFreezeCounter = 1
StepRS.FindFirst ("Number = " & intCounter)
*'Assumes all prerequisites will be met
All_Prerequisites_Met = True
Do Until Nz(StepRS.Fields("Prerequisite " & intPRCounter), "") = ""
intPreReq = StepRS.Fields("Prerequisite " & intPRCounter)
CompletionRS.FindFirst ("[ID Step] = " & intPreReq & " AND
Unicode = '" & NewRS("Unicode") & "'")
'*Handles the case where a prerequisite is not on the list to be
modified
'*Goes ahead and counts it as finished, skips the cycling
If Not boolIsNew Then
If Not CompletionRS("To Be Modified") Then Exit Do
End If
If Not CompletionRS("Complete") And Not CompletionRS.NoMatch Then
All_Prerequisites_Met = False
Exit Do
End If
intPRCounter = intPRCounter + 1
Loop
Do Until Nz(StepRS.Fields("Freeze " & intFreezeCounter), "") = ""
intFreeze = StepRS.Fields("Freeze " & intFreezeCounter)
CompletionRS.FindFirst ("Unicode = '" & NewRS("Unicode") & "' AND
[ID Step] = " & intFreeze)
If CompletionRS("Complete") = True And Not CompletionRS.NoMatch
Then
CompletionRS.MoveFirst
CompletionRS.FindFirst ("Unicode = '" & NewRS("Unicode") &
"'" & _
" AND [ID Step] = " & StepRS("ID Step"))
CompletionRS.Edit
CompletionRS("Frozen") = True
CompletionRS.Update
ElseIf CompletionRS("Complete") = False And Not CompletionRS.
NoMatch Then
CompletionRS.MoveFirst
CompletionRS.FindFirst ("Unicode = '" & NewRS("Unicode") & _
"' AND [ID Step] = " & StepRS("ID Step"))
CompletionRS.Edit
CompletionRS("Frozen") = False
CompletionRS.Update
End If
intFreezeCounter = intFreezeCounter + 1
Loop
If Not All_Prerequisites_Met Then
CompletionRS.FindFirst ("[ID Step] = " & StepRS("ID Step") & _
" AND Unicode = '" & NewRS("Unicode") & "'")
If CompletionRS("Ready") And Not CompletionRS.NoMatch And
boolIsNew Then
CompletionRS.Edit
CompletionRS("Ready") = False
CompletionRS("When Ready") = Empty
CompletionRS.Update
End If
ElseIf All_Prerequisites_Met Then
CompletionRS.FindFirst ("[ID Step] = " & StepRS("ID Step") & _
" AND Unicode = '" & NewRS("Unicode") & "'")
If Not CompletionRS("Ready") And Not CompletionRS.NoMatch Then
CompletionRS.Edit
CompletionRS("Ready") = True
CompletionRS("When Ready") = Date
CompletionRS.Update
End If
End If
Next intCounter
NewRS.MoveNext
Loop

CompletionRS.Close
NewRS.Close
Set CompletionRS = Nothing
Set NewRS = Nothing

End Sub


I tried to exaggerate the indents a bit for clarity, hopefully it is readable.
Thanks for taking the time to look at this. If anything isn't clear, let me
know and I will clarify.

Again, thank you.

-Nick

 

[Nick via AccessMonster.com]

Sorry to be so long getting back to you, Nick. I have spent a good deal of
time looking at this to see if there was any way I could see to make this
work any faster. Given the complexity of what you are doing, I don't think I
could construct queries that might make it any faster.

Not a problem, I appreciate you taking a look at it. The complexity of what
it has to do is a stumbling point when I try to simplify it, but all the
operations are necessary so I can't cut anything out. I had managed to
eliminate multiple similar looping structures by combining a couple of Subs
but can't chop it further.

At least I will have a shield against criticism when I release a new version
of the program... "Hey, the performance issue stumped the Access masters."

Anyways, thanks again for taking the time to look over it.

-Nick

 

[Klatuu]

Rather than "stumped the masters," how about
"The experts authenticated my methodology"

Makes us both look better

 

[nickf123 via AccessMonster.com]

"The experts authenticated my methodology"

Makes us both look better

Well said... Has a nice ring to it.

I was still thinking about building queries in the code when I stumbled on an
idea, but I'm having trouble with the implementation. I'm not even sure if
the idea would really work, or if this is too much work, but it may be worth
a try.

I step through the Step definitions table as my outer loop. The first thing
I do is get the prerequisite steps; when I get the prerequisite (say, step #4)
, the program creates a querydef array object that effectively says "Get the
Unicodes where step 4 is complete". For each prerequisite, a new querydef is
created (i.e. qdf(1) for the 1st prerequisite, qdf(2) for 2nd, and so on.)

I then create a "master" querydef, "qdf(0)" that INNER JOINs the other query
defs to get the Unicodes in common, from which I base my recordsets I need to
update. This way, I would have only the records I need, but I'm not sure how
Access would handle all the querydefs creation. It was actually working
decently (low functionality) until I had 3 querydefs, and the INNER JOIN
concatentation string I wrote didn't work.

Does something like this sound feasible, or would it be a programming
nightmare?

-Nick

 

[Klatuu]

I could see where that might work. A way to get the syntax correct would be
to create the querys, then do the joins in the query builder. Then switch to
SQL view and copy the join statement and paste it into your code and use it
conditionally to build the joins you need. I think that after a few
experiments, you would figure it out. I'd be interested to know how that
works out.

 

[nickf123 via AccessMonster.com]

Using this method seems to be working quite nicely - I still have small bugs
to work out, but it seems like the time required for processing in the Back-
end across the network has decreased by a factor of 4, which is a good thing.
And thinking about it, this routine really isn't even causing any significant
delays anymore; my other slow routines are causing the performance drop.
More experimenting for me to do, I guess

Well, thanks again for steering me to the queries. If you want to see the
code I ended up writing, I will post, but as of right now it looks messy and
isn't 100% complete.

-Nick

 

[Klatuu]

Glad to hear it is working out for you. That is why I originally thought
that if you could use queries it would help the performance. They are
usually much faster than recordset operations, but not always as flexible.