Slow Custom Functions calling to Passthrough Query

M

Marc

I am using Access to manipulate data that is stored in Visual FoxPro
(don't ask and I can't change this) free tables. Basically, it's a
recipe table that can have multiple recursive levels of ingredients
(i.e. Salad Mix contains Chopped Lettuce Mix, which contains Lettuce).

I have successfully programmed VBA functions to recursively seek out
the lowest level ingredient (one for which there is no recipe).
However, it is SLOW SLOW SLOW, and I was hoping for some advice with
regards to speeding it up. The passthrough database has over 5000
records in it.

qryARRECP01 is the passthrough recipe file. There are three custom
functions that call to Passthrough Queries -- getrawcode, getrawqty,
getrawwieght -- which obtain the ultimate raw ingredient, the amount of
this ingredient required and the resulting ingredient remaining after
processing (to calculate yield).

Following is the SQL code for the query:

SELECT qryARRECP01.recipe, qryARRECP01.item,
getrawcode([qryARRECP01]![item]) AS rawingred,
getrawqty([qryarrecp01]![item],[qryarrecp01]![recipe]) AS qty,
getrawweight([qryarrecp01]![recipe],[qryarrecp01]![item],[rawingred])
AS weight, [weight]/[qty] AS yield
FROM qryARRECP01 INNER JOIN qryARINVT01 ON qryARRECP01.recipe =
qryARINVT01.item
WHERE (((qryARRECP01.item) Not In ("LABOR","PACKPO","PACK")))
ORDER BY qryARRECP01.recipe, qryARRECP01.item;

I will include the code for just getrawcode, because the other three
queries are fairly similar (though getrawweight is even slower because
it searches two passthrough queries.


Function GetRawCode(ByVal ingrcheck As String) As String

Dim db As Database
Dim rs As DAO.Recordset

'Open Recipe Query
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryARRECP01", dbOpenDynaset, dbReadOnly)

If (Not rs.EOF) Then
'Call recursive function to locate raw ingredient
GetRawCode = FindRawIngred(ingrcheck, rs)
End If

rs.Close 'Close Table
Set db = Nothing 'Clear reference to database

End Function


Function FindRawIngred(ByVal ingrcheck As String, rs As DAO.Recordset)
As String

Dim sSQL As String

sSQL = "[recipe] = """ & ingrcheck & """"

rs.FindFirst sSQL 'Search recordset for matching item code

If rs.NoMatch Then 'When item code is not found (e.g.
is a raw ingredient)
FindRawIngred = ingrcheck 'Set the raw ingredient as the
item code
Else
If Trim(rs!item) = "LABOR" Then rs.FindNext sSQL
FindRawIngred = FindRawIngred(rs!item, rs) 'Run FindRawIngred on
item code
End If

End Function


Any thoughts, suggestions or advice would be very much appreciated.

Best regards,
marc
 
M

Marc

I determined that the problem relates directly to the continual opening
and closing of a recordset based on the passthrough query. By creating
module-level recordset variables that run the first time any of these
functions are called (using a static variable) and closing them upon
exiting the database, I sped up this query by magnitudes (from a couple
of hours to five minutes).
I am using Access to manipulate data that is stored in Visual FoxPro
(don't ask and I can't change this) free tables. Basically, it's a
recipe table that can have multiple recursive levels of ingredients
(i.e. Salad Mix contains Chopped Lettuce Mix, which contains Lettuce).

I have successfully programmed VBA functions to recursively seek out
the lowest level ingredient (one for which there is no recipe).
However, it is SLOW SLOW SLOW, and I was hoping for some advice with
regards to speeding it up. The passthrough database has over 5000
records in it.

qryARRECP01 is the passthrough recipe file. There are three custom
functions that call to Passthrough Queries -- getrawcode, getrawqty,
getrawwieght -- which obtain the ultimate raw ingredient, the amount of
this ingredient required and the resulting ingredient remaining after
processing (to calculate yield).

Following is the SQL code for the query:

SELECT qryARRECP01.recipe, qryARRECP01.item,
getrawcode([qryARRECP01]![item]) AS rawingred,
getrawqty([qryarrecp01]![item],[qryarrecp01]![recipe]) AS qty,
getrawweight([qryarrecp01]![recipe],[qryarrecp01]![item],[rawingred])
AS weight, [weight]/[qty] AS yield
FROM qryARRECP01 INNER JOIN qryARINVT01 ON qryARRECP01.recipe =
qryARINVT01.item
WHERE (((qryARRECP01.item) Not In ("LABOR","PACKPO","PACK")))
ORDER BY qryARRECP01.recipe, qryARRECP01.item;

I will include the code for just getrawcode, because the other three
queries are fairly similar (though getrawweight is even slower because
it searches two passthrough queries.


Function GetRawCode(ByVal ingrcheck As String) As String

Dim db As Database
Dim rs As DAO.Recordset

'Open Recipe Query
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryARRECP01", dbOpenDynaset, dbReadOnly)

If (Not rs.EOF) Then
'Call recursive function to locate raw ingredient
GetRawCode = FindRawIngred(ingrcheck, rs)
End If

rs.Close 'Close Table
Set db = Nothing 'Clear reference to database

End Function


Function FindRawIngred(ByVal ingrcheck As String, rs As DAO.Recordset)
As String

Dim sSQL As String

sSQL = "[recipe] = """ & ingrcheck & """"

rs.FindFirst sSQL 'Search recordset for matching item code

If rs.NoMatch Then 'When item code is not found (e.g.
is a raw ingredient)
FindRawIngred = ingrcheck 'Set the raw ingredient as the
item code
Else
If Trim(rs!item) = "LABOR" Then rs.FindNext sSQL
FindRawIngred = FindRawIngred(rs!item, rs) 'Run FindRawIngred on
item code
End If

End Function


Any thoughts, suggestions or advice would be very much appreciated.

Best regards,
marc
Click to expand...
 
D

david epsom dot com dot au

Also, I see that [rawingred] appears twice in the select query:
once as an output field, once as parameter to getrawweight.

Access won't optimise those two appearances: quite the reverse:
the function is called twice, once for each appearance of the
field in the query.

Any slow function indirectly referenced multiple times in a
query can be spead up just by saving the last calculated
value, and returning the saved value if the input parameters
have not changed.

Also, I see that you are using FindFirst. This always re-starts
at the top. In general, you can make this kind of search faster
by starting at the present position (FindNext), and then trying
backwards (FindPrevious). David Fenton:

rs.FindNext
If rs.NoMatch Then
rs.FindPrevious
If rs.NoMatch Then GoTo End
End If
http://groups.google.com.au/group/c...85927e7ea5ed7dc?lnk=st&hl=en#585927e7ea5ed7dc


(david)


Marc said:
I determined that the problem relates directly to the continual opening
and closing of a recordset based on the passthrough query. By creating
module-level recordset variables that run the first time any of these
functions are called (using a static variable) and closing them upon
exiting the database, I sped up this query by magnitudes (from a couple
of hours to five minutes).
I am using Access to manipulate data that is stored in Visual FoxPro
(don't ask and I can't change this) free tables. Basically, it's a
recipe table that can have multiple recursive levels of ingredients
(i.e. Salad Mix contains Chopped Lettuce Mix, which contains Lettuce).

I have successfully programmed VBA functions to recursively seek out
the lowest level ingredient (one for which there is no recipe).
However, it is SLOW SLOW SLOW, and I was hoping for some advice with
regards to speeding it up. The passthrough database has over 5000
records in it.

qryARRECP01 is the passthrough recipe file. There are three custom
functions that call to Passthrough Queries -- getrawcode, getrawqty,
getrawwieght -- which obtain the ultimate raw ingredient, the amount of
this ingredient required and the resulting ingredient remaining after
processing (to calculate yield).

Following is the SQL code for the query:

SELECT qryARRECP01.recipe, qryARRECP01.item,
getrawcode([qryARRECP01]![item]) AS rawingred,
getrawqty([qryarrecp01]![item],[qryarrecp01]![recipe]) AS qty,
getrawweight([qryarrecp01]![recipe],[qryarrecp01]![item],[rawingred])
AS weight, [weight]/[qty] AS yield
FROM qryARRECP01 INNER JOIN qryARINVT01 ON qryARRECP01.recipe =
qryARINVT01.item
WHERE (((qryARRECP01.item) Not In ("LABOR","PACKPO","PACK")))
ORDER BY qryARRECP01.recipe, qryARRECP01.item;

I will include the code for just getrawcode, because the other three
queries are fairly similar (though getrawweight is even slower because
it searches two passthrough queries.


Function GetRawCode(ByVal ingrcheck As String) As String

Dim db As Database
Dim rs As DAO.Recordset

'Open Recipe Query
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryARRECP01", dbOpenDynaset, dbReadOnly)

If (Not rs.EOF) Then
'Call recursive function to locate raw ingredient
GetRawCode = FindRawIngred(ingrcheck, rs)
End If

rs.Close 'Close Table
Set db = Nothing 'Clear reference to database

End Function


Function FindRawIngred(ByVal ingrcheck As String, rs As DAO.Recordset)
As String

Dim sSQL As String

sSQL = "[recipe] = """ & ingrcheck & """"

rs.FindFirst sSQL 'Search recordset for matching item code

If rs.NoMatch Then 'When item code is not found (e.g.
is a raw ingredient)
FindRawIngred = ingrcheck 'Set the raw ingredient as the
item code
Else
If Trim(rs!item) = "LABOR" Then rs.FindNext sSQL
FindRawIngred = FindRawIngred(rs!item, rs) 'Run FindRawIngred on
item code
End If

End Function


Any thoughts, suggestions or advice would be very much appreciated.

Best regards,
marc
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function to extract element of a complex delimited string (Pick D3 database) 2

Top