Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Slow Custom Functions calling to Passthrough Query

Discussion in 'Information Technology' started by Marc, Jul 28, 2006.

  1. Marc

    Marc
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Opinion IDEAS: Revolution, Evolution Or Slow Death Sikh Sikhi Sikhism Jul 16, 2016
    Slow metabolism 'obesity excuse' true Health & Nutrition Oct 26, 2013
    India India in the Slow Lane Breaking News Feb 10, 2013
    USA Slow Recovery at US Sikh Temple Breaking News Jan 14, 2013
    UK Archbishop of Canterbury Visits Hounslow Sikh Temple Breaking News Oct 19, 2011

  3. Marc

    Marc
    Expand Collapse
    Guest

    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).

    Marc wrote:
    > 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
     
  4. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    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" <vermutmb@gmail.com> wrote in message
    news:1152576366.566991.172850@m73g2000cwd.googlegroups.com...
    >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).
    >
    > Marc wrote:
    >> 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

    >
     

Share This Page