Recordset vs Array speed? (1 Viewer)

[PISI Guy]

Hello all. First of all, thanks to all the knowledgeable folks out there willing to contribute. I've learned quite a bit as a lurker.

I have a function that is passed some information about a tree, including the species. This function performs a simple calculation on the information passed, with parameters (2) that differ by tree species. These parameters are stored in a separate table. There are a lot of trees (~220k) and only a few species (~7). My question is, will it be faster to:

1) Copy the parameters table into an array, then loop through the array each time the function is called to find the right ones for the species?

2) Maintain an open recordset and use .Find to get the correct parameters for the species?

3) Open the recordset each time the function is called, using SQL to select the correct parameters?

Thanks!

 

[jdraw]

Does this function have to process all trees when it is used, or does it identify a small set of records?

Suppose you had a form with a list of values for parameter1, and a list of values for parameter2.

Once you select the parameters, and say click a button

you could invoke some SQL with a WHERE clause using the 2 parameters.

If the result set is only a few records, you would keep your tables,
use queries to populate the parameter lists,
then use an SQL statement to return the required records.

The tables should be structured properly with indexes to facilitate searching.

 

[PISI Guy]

jdraw,
Thanks for the reply.

As the project is currently organized my function (let's call it Carbon) is called on a tree by tree basis, but eventually all trees get calculated. Basically I have a procedure that iteratively selects a subset of trees, cleans up bad/missing data, calls the Carbon function for each tree, and then further processes the result.

Currently every time the Carbon function is called I create a recordset from a SQL query of the Parameters table to get the numbers for the species. Opening the parameters table 220k times now seems a little ridiculous. I was thinking I could open it once as private in the calling procedure and either leave the recordset open (makes me nervous) and use .Find, or copy it into a private array and loop through that each time Carbon is called to get the number I need.

Disclaimer: clearly, I'm not a programmer.

 

[jdraw]

If you are going to process 200K+ records you wouldn't set it up to open the file for each record, I think you should show us the function or parts of it.

If you are processing a subset of records say 200-500 at a time, you might want to use Transaction logic -- everything works or everything is backed out , but that's another story at the moment.

If you are opening tables in the function then perhaps the function should be adjusted.
Again better to see the function and what it is set up to do before making any changes to it or to your set up.

 

[PISI Guy]

Alright, here goes. I need to be able to call this on a record-by-record basis, but there are only a handful of values in the equation parameter table. I wonder how to avoid opening a recordset based on that table each time the function is called, so I was thinking either a module level array with the values, or a module level recordset only opened once and left open (sketchy?)

Function tree_live_C(strSpp As String, dblDia As Double, dblHeight As Double) As Double
 
' This function takes diameter, height, and species and returns the total C content of the
' tree in kg. Parameters are taken from tStandishEqs depending on tree species
 
' Option Explicit, so all variables and recordsets declared here
 
'****get Standish equation parameters for total biomass
strSQL = "SELECT * from tStandishEqs WHERE Species = """ & strSpp & """"
rsStandish.Open strSQL, CurrentProject.Connection
 
'*********calculate C content for tree***********
' calculation involving dblDia, dblHeight rsStandish!CoeffA, rsStandish!coeffB went here
 
rsStandish.Close
 
End Function

Thanks!

 

[jdraw]

I looked at your materials and set up a test.
In the test I have a table MyTrees and
a mockup of your tStandishEqs see PISIGuy0.jpg

I adjusted your function to accept CoeffA and CoeffB since they are in tStandishEqs.
Also, made a query to use your data and the adjusted function and to output the Carbon_Value calculation as a field.
Here is the query

SELECT MyTrees.id
, MyTrees.Species
, MyTrees.Height
, MyTrees.Diameter
, tStandishEqs.coeffA
, tStandishEqs.coeffB
, tree_live_C([diameter],[height],[coeffA],[coeffB]) AS Carbon_Value
FROM MyTrees INNER JOIN tStandishEqs
ON MyTrees.Species = tStandishEqs.species;

You could run the query from a form where you could select by Species or set it up as you want. I think this will resolve the opening and closing of a recordset numerous times.

The output of the query and the sample data I used is in PISIGuy.1.jpg

The test function was

'---------------------------------------------------------------------------------------
' Procedure : tree_live_C
' Author : Jack (PISI Guy)AWF DEC 7 2011
' Date : 09-12-2011
' Purpose : From http://www.access-programmers.co.uk/forums/showthread.php?t=219246
'to find options for a function that involves opening a recordset numerous times
' Why not adjust function to use a query??
'You could run this from a form where you select a species
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Function tree_live_C(dblDia As Double, dblHeight As Double, CoA As Double, coB As Double) As Double
'Test in line
'dblDia = 20.2
'dblHeight = 600.5
'CoA = 0.54
'coB = 0.22
'Original Function tree_live_C(strSpp As String, dblDia As Double, dblHeight As Double) As Double

' This function takes diameter, height, and species and returns the total C content of the
' tree in kg. Parameters are taken from tStandishEqs depending on tree species

' Option Explicit, so all variables and recordsets declared here

'****get Standish equation parameters for total biomass
On Error GoTo tree_live_C_Error

'strSQL = "SELECT * from tStandishEqs WHERE Species = """ & strSpp & """"
'rsStandish.Open strSQL, CurrentProject.Connection

'*********calculate C content for tree***********
' calculation involving dblDia, dblHeight rsStandish!CoeffA, rsStandish!coeffB went here

'rsStandish.Close

'**************************************************TESTING *********************
' suppose a test calculation
' (Diam * coeffA) + (Height * coeffB )
tree_live_C = (dblDia * CoA) + (dblHeight * coB)
Debug.Print tree_live_C
On Error GoTo 0
Exit Function
tree_live_C_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure tree_live_C of Module AWF_Related"

End Function

 

[PISI Guy]

jdraw,
Thanks for taking the time to do that. That will work well for the living trees. The trick will be a preceeding step to calculate height from diameter where height was not measured, and a subsequent exponential decay function for dead trees, but I suspect I can work it out. My lessons from this project have been (1) optimization matters and (2) do everything you can in SQL. I'll see if I can't rearrange things a bit.
Thanks again,

 

[jdraw]

Glad to help.