General speed of functions

[ctags009]

Hi all -

I'm relatively new to Access, so this may be a very simple question.
I am trying to create an application that relies heavily on reporting. Unfortunately, all of the data that I need in these reports are functions that can't be recreated in a query. The reports are taking minimum 10 minutes to load. Which will obviously not work.

For instance:

I am looking to calculate a total employer cost per employee which would include roughly 12-15 different functions that break down different pieces of salary/taxes/pension etc etc...

I've tried to use the functions in a query, put them directly on the report and most recently (and the most effective so far) had vba create a table and populate the report from the table, which prevented recalculating every time i scrolled through the report.

My question is, for reports that rely completely on user defined functions, how can I reduce the load time? Is this something I should not be using Access for?

 

[spikepl]

Functions are fast enough as such - the question is how you use them. Domain functions (with D-prefix) are slow in queries - they stupidly repeat the same work over and over again.

Queries are normally the fastest for processing a bunch of related data, because of optimisation - provided that the fields in all WHERE's are indexed. That also applies to domain functions (ie the fields used in criteria, in general, should be indexed).

Depending on what exactly goes on, it may be much faster to loop through some recordsets, maybe keeping some intermediate data ...create a table... but hard to tell without seeing any specifics.

In any case 10 minutes for a report sounds extremely unoptimised, unless the report concerns the payroll of the UN or something similar in size.

 

[ctags009]

I know i was pretty vague - All of the functions i'm using are relatively simple, but depend on other functions as well.

Ex.


To find someone's base salary I created a function that first finds how long theyve been in the position, and based on this number and what their contract says (another table) what "STEP" they would be within a salary range.
Another function finds all of their current positions and how long they have been in each position from one table. The salary range for said position from another table then calls the previous function to find the STEP and assigns them a base salary value.
Next, the base salary function would be used to find any stipends or longevity pay that they would have (usually a percentage of base salary)

I continuously have to call prior functions to find these numbers

On their own they work very quickly, but as i mentioned before, in reports they take up to 10 minutes to load (for approximately 200 employees) and using them in a query for the report takes longer and causes the report to recalculate whenever i scroll through it.

I'm not sure how to be more specific, I think im really looking for some kind of best practice suggestion when using multiple (around 15) functions (that call on eachother) in a query/report

 

[ctags009]

This would be an example of one of the functions to give you an idea of perhaps why it takes 10 minutes to calculate 15 of these times 200 employees (the first one that i mentioned in the prior post - which the Base Salary function would rely on):

Function empStep(empid As Integer, posNum As Byte, asofdate As Date)

Dim db As dao.Database, rst As dao.Recordset, strSQL As String, rst1 As dao.Recordset, strSQL1 As String
Set db = CurrentDb()

'OPEN EMPLOYEE POSITIONS
strSQL1 = "SELECT * FROM tblEmployeePositions " & _
"WHERE [lngEmpID] = " & empid & " AND [lngPstnNmbr] = " & posNum & " AND [dtPstnDtStrt] <=#" & asofdate & "# AND nz([dtPstnDtEnd],#" & asofdate & "#) >=#" & asofdate & "#"
Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)

With rst1
Dim YrsInPos As Single, StpOvrd As Byte, posStrt As Date, posEnd As Date
'Find Years in Position
YrsInPos = (Nz(.Fields("dtPstnDtEnd"), asofdate) - .Fields("dtPstnDtStrt")) / 365.25
'Find Position Start Date
posStrt = .Fields("dtPstnDtStrt")
If .Fields("blnOvrd") = -1 Then
'Override Applies
StpOvrd = .Fields("lngStpOvrd")
Else
'No Override
StpOvrd = 0
End If
End With

'Find Parameter Rules
If empUnion(empid, asofdate) = 0 And rst1.Fields("blnOvrd") = 0 Then
empStep = "N/A"
Else

strSQL = "SELECT * FROM tblStepParameters " & _
"WHERE [lngUnnID] = " & empUnion(empid, asofdate)
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
Dim strCriteria As String
'Union not in Step Paramaters Table
If (rst.EOF = True) And (rst.BOF = True) Then
If rst1.Fields("blnOvrd") = -1 Then
empStep = StpOvrd
Else
empStep = "N/A"
End If
'Union IS in step Paramaters Table
Else
Dim rst2 As dao.Recordset, strSQL2 As String
Select Case Nz(rst.Fields("dtstpAnnivDt"), "N/A")
'No Anniversary Date
Case "N/A"
strCriteria = " AND [lngYrsMin] <=(" & YrsInPos & " + " & StpOvrd & ") AND nz([lngYrsMax]," & YrsInPos & " + " & StpOvrd & ")>= " & YrsInPos & " + " & StpOvrd
strSQL2 = "SELECT * FROM tblStepParameters " & _
"WHERE [lngUnnID] = " & empUnion(empid, asofdate) & strCriteria
Set rst2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
'Anniversary Date
Case Else
Dim AnnivDate As Date, AnnivStep As Single
AnnivDate = DateSerial(Year(posStrt), Left(rst.Fields("dtStpAnnivDt"), 2), Right(rst.Fields("dtStpAnnivDt"), 2))
AnnivStep = ((asofdate - AnnivDate) / 365.25) + StpOvrd
strCriteria = " AND [lngYrsMin] <=" & AnnivStep & " AND nz([lngYrsMax]," & AnnivStep & ")>= " & AnnivStep & " ORDER BY tblStepParameters.lngStpNmbr;"
strSQL2 = "SELECT * FROM tblStepParameters " & _
"WHERE [lngUnnID] = " & empUnion(empid, asofdate) & strCriteria
Set rst2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
rst2.MoveLast
End Select

empStep = rst2.Fields("lngStpNmbr")

rst2.Close
Set rst2 = Nothing
End If
End If

rst1.Close
Set rst1 = Nothing
If Not empUnion(empid, asofdate) = "N/A" Then
rst.Close
Set rst = Nothing
End If

End Function

I know i was pretty vague - All of the functions i'm using are relatively simple, but depend on other functions as well.

Ex.


To find someone's base salary I created a function that first finds how long theyve been in the position, and based on this number and what their contract says (another table) what "STEP" they would be within a salary range.
Another function finds all of their current positions and how long they have been in each position from one table. The salary range for said position from another table then calls the previous function to find the STEP and assigns them a base salary value.
Next, the base salary function would be used to find any stipends or longevity pay that they would have (usually a percentage of base salary)

I continuously have to call prior functions to find these numbers

On their own they work very quickly, but as i mentioned before, in reports they take up to 10 minutes to load (for approximately 200 employees) and using them in a query for the report takes longer and causes the report to recalculate whenever i scroll through it.

I'm not sure how to be more specific, I think im really looking for some kind of best practice suggestion when using multiple (around 15) functions (that call on eachother) in a query/report

 

[spikepl]

It's the question of the algorithm. The brute-fore approach is to call a function to get the data you need whenever you need it. That gives you what you have. Since it doesn't work very well, then you have to rethink your approach. Anything that is used more than once and has to be found or calculated can be saved in a temp table, or an array, or a dictionary object (that makes it easy to retrieve a value again based on a key).

Again, without seeing a specific example hard to tell. As to "best practice" the standard recommendation is not to use brute force but to think and rethink.

 

[spikepl]

Your code is bl... hard to read - can you put it in code tags? Open your post in "advanced", select the code and click on #

 

[ctags009]

Sorry, wasn't aware I could do this

Function empStep(empid As Integer, posNum As Byte, asofdate As Date)

        Dim db As dao.Database, rst As dao.Recordset, strSQL As String, rst1 As dao.Recordset, strSQL1 As String
        Set db = CurrentDb()

        'OPEN EMPLOYEE POSITIONS
        strSQL1 = "SELECT * FROM tblEmployeePositions " & _
                  "WHERE [lngEmpID] = " & empid & " AND [lngPstnNmbr] = " & posNum & " AND [dtPstnDtStrt] <=#" & asofdate & "# AND nz([dtPstnDtEnd],#" & asofdate & "#) >=#" & asofdate & "#"
        Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
        
            With rst1
                Dim YrsInPos As Single, StpOvrd As Byte, posStrt As Date, posEnd As Date
                'Find Years in Position
                YrsInPos = (Nz(.Fields("dtPstnDtEnd"), asofdate) - .Fields("dtPstnDtStrt")) / 365.25
                'Find Position Start Date
                posStrt = .Fields("dtPstnDtStrt")
                If .Fields("blnOvrd") = -1 Then
                    'Override Applies
                    StpOvrd = .Fields("lngStpOvrd")
                Else
                    'No Override
                    StpOvrd = 0
                End If
            End With
                 
        'Find Parameter Rules
        If empUnion(empid, asofdate) = 0 And rst1.Fields("blnOvrd") = 0 Then
            empStep = "N/A"
        Else
        
        strSQL = "SELECT * FROM tblStepParameters " & _
                 "WHERE [lngUnnID] = " & empUnion(empid, asofdate)
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
        Dim strCriteria As String
                'Union not in Step Paramaters Table
                If (rst.EOF = True) And (rst.BOF = True) Then
                    If rst1.Fields("blnOvrd") = -1 Then
                        empStep = StpOvrd
                    Else
                        empStep = "N/A"
                    End If
                'Union IS in step Paramaters Table
                Else
                    Dim rst2 As dao.Recordset, strSQL2 As String
                    Select Case Nz(rst.Fields("dtstpAnnivDt"), "N/A")
                        'No Anniversary Date
                        Case "N/A"
                            strCriteria = " AND [lngYrsMin] <=(" & YrsInPos & " + " & StpOvrd & ") AND nz([lngYrsMax]," & YrsInPos & " + " & StpOvrd & ")>= " & YrsInPos & " + " & StpOvrd
                            strSQL2 = "SELECT * FROM tblStepParameters " & _
                                        "WHERE [lngUnnID] = " & empUnion(empid, asofdate) & strCriteria
                            Set rst2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
                        'Anniversary Date
                        Case Else
                            Dim AnnivDate As Date, AnnivStep As Single
                            AnnivDate = DateSerial(Year(posStrt), Left(rst.Fields("dtStpAnnivDt"), 2), Right(rst.Fields("dtStpAnnivDt"), 2))
                            AnnivStep = ((asofdate - AnnivDate) / 365.25) + StpOvrd
                            strCriteria = " AND [lngYrsMin] <=" & AnnivStep & " AND nz([lngYrsMax]," & AnnivStep & ")>= " & AnnivStep & " ORDER BY tblStepParameters.lngStpNmbr;"
                            strSQL2 = "SELECT * FROM tblStepParameters " & _
                                        "WHERE [lngUnnID] = " & empUnion(empid, asofdate) & strCriteria
                            Set rst2 = db.OpenRecordset(strSQL2, dbOpenDynaset)
                            rst2.MoveLast
                        End Select
                        
                        empStep = rst2.Fields("lngStpNmbr")
                        
                    rst2.Close
                    Set rst2 = Nothing
                End If
        End If
       
        rst1.Close
        Set rst1 = Nothing
        If Not empUnion(empid, asofdate) = "N/A" Then
            rst.Close
            Set rst = Nothing
        End If
            
End Function

 

[spikepl]

Ok that was better (for readability).

I am a bit whacked at this time , so the only thing I can suggest for now is to check what data you need more than once (during your multiple function calls) and then consider storing it in a table, array or dictionary. Intermediate data -reused to find other data - may be useful to store too. At least I don't see any obvious nono's You did remember to index the relevant fields?

If someone more awake that me comes by do chime in

 

[ctags009]

Ah, thank you, the results of the function can potentially change each day (it's all based on the date used) -- I'm not very familiar with storing data, do I create a temporary table for each function, having only a key (1) and the result of the function? Can you perhaps tell me what to search for to get me started?

I read through the "Access 2007 Bible" looking for help as well, one of the things I found was to make sure appropriate fields are indexed, so I made sure I checked for that.

Thanks again!

Ok that was better (for readability).

I am a bit whacked at this time , so the only thing I can suggest for now is to check what data you need more than once (during your multiple function calls) and then consider storing it in a table, array or dictionary. Intermediate data -reused to find other data - may be useful to store too. At least I don't see any obvious nono's You did remember to index the relevant fields?

If someone more awake that me comes by do chime in

 

[spikepl]

I don't mean to create a temporary table for each function. I mean that you should inspect all of your functions, see what is used more than once, and maybe store it somewhere.

In the function listed you call
empUnion(empid, asofdate) a few times - is this also a function that runs some sql? Thus repeatedly? It should only be run once.

Further, when you run queries get only the fields you need, not "*" - that saves a little bit too.

Finally, your function is geared toward dealing with one employee. This means that whatever overhead there is in setting up and running running queries is repeated for each employee. Maintenance-wise this is the simplest to do. Performance-wise there is a cost, as you have experienced. What can be shared/prepared for all employees? So instead calling a bunch of queries for each, what could be done by looping over all employees?

 

[Galaxiom]

It is best to get as much of the calculation coming directly from joined tables in the queries because they can be optimised by Access before running.

A function is a discrete block and the database engine has no way to share the resources between instance of the function.

For example the calculation of the base salary would be faster if the tables and arithmetic involved are in the query itself.

 

[vbaInet]

For instance:

I am looking to calculate a total employer cost per employee which would include roughly 12-15 different functions that break down different pieces of salary/taxes/pension etc etc...

I've tried to use the functions in a query, put them directly on the report and most recently (and the most effective so far) had vba create a table and populate the report from the table, which prevented recalculating every time i scrolled through the report.

There's quite a lot you can do in a query. My advice would be to create a new thread in the Queries section of the forum explaining what you would like to achieve and then include your function with some notes on what it does. There might be a way to do it in a query with less coding.

 

[ctags009]

Thanks for your responses:

The problem I see with this is that it seems I can only link a report to one query and it wouldnt be possible for me to have one query with all of the functions I need (obviously unless I called a function from the query, which I think would defeat the purpose?) - The function I posted above was just an example of what they look like. Like I said, there are 10-15 similar functions that find values I need for my reports.

What spikepl said earlier, that this was a "brute force" approach, makes a lot of sense to me, in that I need to rethink how I am coming up with these values. But I have yet to come up with a way to achieve the values the functions provide me with without calling them individually.

I also read somewhere that using SQL in my VBA would be better in the long run because it makes it easy to read whats going in my code straight from the module. I also thought that finding the values directly from a table instead of from a query would be quicker?

As for the report, it would need to be comprehensive, in that it would contain all of the values I'm coming up with in my functions (which change daily) and they must be specific to the employee.

The fact is that access is doing what I want it to... it just seems as though 10 minutes for a report is an awfully long time to wait. So I know there has to be a better way.

I will check out the query board and see if there is perhaps something I can do prior to getting to the report stage.

Thanks again for your responses!

There's quite a lot you can do in a query. My advice would be to create a new thread in the Queries section of the forum explaining what you would like to achieve and then include your function with some notes on what it does. There might be a way to do it in a query with less coding.

 

[ctags009]

So, instead of using SQL to get my data from tables, I should create a query, than open the recordset in VBA and retrieve the field I am looking for (based on a parameter)?

I tried using this code to create my parameters (such as employee ID or date effective):

Dim arrParameter(4)
 
Public Sub SetParam(ByVal InputVal, ByVal ParamID)
arrParameter(ParamID) = InputVal
End Sub
Public Function GetParam(ByVal ParamID)
GetParam = arrParameter(ParamID)
End Function

Then I would do something like this to retrieve the data I'm looking for:

Function OvertimeHours(empID As Integer, asofdate As Date) As Currency
'Year to Date Overtime Hours based on AsOfDate
Call SetParam(empID, 1)
Call SetParam(Year(asofdate), 2)
    Dim rst As Recordset
    Dim qdf As QueryDef
    Set qdf = CurrentDb().QueryDefs("qryOvertime")
    Set rst = qdf.OpenRecordset
        If (rst.BOF = True And rst.EOF = True) Then
            OvertimeHours = 0
        Else
            OvertimeHours = rst.Fields("TotalHours")
        End If
 
qdf.Close
rst.Close
Set rst = Nothing
Set qdf = Nothing
 
End Function

This was my original approach, do you think this is faster than something like this would be: where I use SQL to open the table I need the data from?

Function YearsOfService(empID As Integer, asofdate As Date) As Single
'Total Years of Service as of specified date.
On Error GoTo ErrorHandler
 Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String, i As Integer, arrCount() As Single
        Set db = CurrentDb()
        strSQL = "SELECT * FROM tblHireDates " & _
                 "WHERE [lngEmpID] = " & empID
        Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
            With rst
                If (.BOF = True And .EOF = True) Then
                    YearsOfService = 0
                Else
                    .MoveLast
                    .MoveFirst
                    RcdCnt = .RecordCount
                    ReDim arrCount(1 To RcdCnt)
                    For i = 1 To RcdCnt
                        arrCount(i) = (Nz(.Fields("dtTrmDt"), asofdate) - .Fields("dtHrDt")) / 365.25
                        .MoveNext
                    Next i
                    For i = 1 To RcdCnt
                        YearsOfService = YearsOfService + arrCount(i)
                    Next i
                End If
            End With
        rst.Close
        Set rst = Nothing
 
ErrorHandlerExit:
    Exit Function
ErrorHandler:
    If Err = 3021 Then    ' 3021: NO CURRENT RECORD
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume ErrorHandlerExit
    End If
 
End Function

I've been trying every which way i can think of to save time when running reports - but i'm running out of ideas!

Thanks!

It is best to get as much of the calculation coming directly from joined tables in the queries because they can be optimised by Access before running.

A function is a discrete block and the database engine has no way to share the resources between instance of the function.

For example the calculation of the base salary would be faster if the tables and arithmetic involved are in the query itself.

 

[gemma-the-husky]

there are a few choices with stuff like this

a) look carefully at the algorithm, to see if it coould be improved

b) look to see if normalisation rules could be broken, and store all or at least part of the calculation as a calculated value

c) accept that 10mins is not too bad for such a complex process. i presume it would take hours to do manually

I suspect the answer might be a combination of these.

 

[Galaxiom]

So, instead of using SQL to get my data from tables, I should create a query, than open the recordset in VBA and retrieve the field I am looking for (based on a parameter)?

No. The recordset slows it down. The query should be based on all the tables required to retrieve all the information for all the records in the report.

Derive calculated fields in the query to return the values to be displayed in the report.

 

[spikepl]

Your function OvertimeHours - why does it exist? It's pretty straight forward to do all that in one query for all employees. Maybe it could be married into a query doing all your stuff or not - hard to tell without knowing the rest of the story. But at least this result could be stored in a table, one record for each employee.

Your function YearsOfService -the same question.. Further, it seems a bit weird that there is more than one record per employee, that is unless you record each year on the job in a separate record but then is that necessary? In any case, all that could be done in one query for all employees - and stored as a column in the same table as your OvertimeHours. My bet is that the same applies to many of your other functions.

 

[spikepl]

In #13 you wrote:

The problem I see with this is that it seems (1) I can only link a report to one query and (2) it wouldnt be possible for me to have one query with all of the functions I need (obviously unless I called a function from the query, which I think would defeat the purpose?) - The function I posted above was just an example of what they look like. Like I said, there are 10-15 similar functions that find values I need for my reports.

1. One query yes - but it can be a complex animal !
2. How do you know? Or do you mean that YOU are not able to construct appropriate query off-hand? That is a different thing, and that's why we have this forum
3. Storing data in a temp table/array (in case one query is too complex) by processing all employees in one shot is far better for performance than calling your functions employee by employee.

 

[ctags009]

Your function OvertimeHours - why does it exist? It's pretty straight forward to do all that in one query for all employees. Maybe it could be married into a query doing all your stuff or not - hard to tell without knowing the rest of the story. But at least this result could be stored in a table, one record for each employee.

Your function YearsOfService -the same question.. Further, it seems a bit weird that there is more than one record per employee, that is unless you record each year on the job in a separate record but then is that necessary? In any case, all that could be done in one query for all employees - and stored as a column in the same table as your OvertimeHours. My bet is that the same applies to many of your other functions.

You're right, a lot of the functions can be done in queries, the reason I made functions was because I was misinformed that queries actually slow down the db more than what I'm doing now and was shown applications that had no queries created at all... I have clearly not been using queries to their full potential and am currently exploring more options.

As for the YearsofService, the reason there would be more than one record per employee is because it is a possibility that An employee is terminated and rehired years later or perhaps goes on leave for a period of time. I'm trying to be as thorough as possible and am designing it with all possible scenarios that come to mind.

I appreciate all of your help, once I design a query that can replace most, if not all, of the functions i have, I will post and let you know if it's running quicker. (Although, if I can replace all of the functions, I might question when/why I would create functions at all)

Thanks all!

 

[Galaxiom]

Functions have their place but the query is always faster if you can do it especially if the function is looking up tables. Functions are also a good way to deal with what would otherwise require monster expressions.

A vast proportion of Access users have had no formal training. Misunderstandings are rife and it isn't just those with no formal training that are resposible. Some become fixed in their ways and it is quite impossible to get through to them even with graphic evidence that counters their thinking.

Never stop questioning everything you think you know and you will keep learning.