Slow processing on Split database - BE and FE

[stigmatized]

Hi guys,



I'm doing a payroll calculation in my database and I split it into BE and FE. The BE is located in a network server while the FE is save in local computer of each user. My BE contains all the details of all the employees, e.g. ID number, Name, Department, Job Title, etc.

To do the calculation, I am doing an .OpenRecordset("MyTable1",dbOpenSnapshot) command to pull out all the necessary employee details which will be used to calculate the cost

When multiple users are using their FE at the same time, it slows down the calculation process.


here is my code:

Sub PayrollCalc()

Dim db as DAO.Database, rstFrom As DAO.Recordset, rstTo As DAO.Recordset
Dim fld As Field, Counter as Long, i as Long

Set db = CurrentDb

' table is located in BE
Set rstFrom = db.OpenRecordset("MyTable1", dbOpenSnapshot)

' table is located in FE
Set rstTo = db.OpenRecordset("MyTable2", dbOpenDynaset)

rstFrom.MoveLast
Counter = rstFrom.RecordCount

rstFrom.MoveFirst
For i = 1 to Counter
rstTo.AddNew
For Each fld In rstFrom.Fields
If fld.Name = "Salary" then
rstTo(fld.Name) = ' i have a separate function to calculate the amount
Else
rstTo(fld.Name) = rstFrom(fld.Name)
End If
Next
rstTo.Update
rstFrom.MoveNext
Next

Please help me to understand how to make this process a bit faster.

Thank you so much in advance!

[orange]

How slow is slow? How many records are involved? How many users are involved? Is it necessary to build a table in the FE?
Have you considered building an array and summing the values??(just a thought while typing)

[stigmatized]

Everytime i process 10 employees/records with 4 components of salary ( that is the process will run the code 4 times e.g. Basic, overtime, food allow, car allowance ) and its taking 3 mins. The problem is i have 20 users with 50 employees each. Every time they run the script they are consuming 15 mins or more.

I need to create a table in FE because i'm using it in forms/report.

Sorry i'm not that familiar with arrays.

[orange]

Can you show some of the queries/code involved in the processing of the script?

[stigmatized]

Hi Orange,

Here is my complete code for my payroll calculation:

Sub PayrollCalc()


Dim db As DAO.Database
Dim rstFrom As DAO.Recordset
Dim strSQLFrom As String
Dim rstTo As DAO.Recordset
Dim strSQLTo As String
Dim fld As Field, Counter As Long, i As Integer
ReDim Period(999) As String


Period(101) = "F_Jan"
Period(102) = "F_Feb"
Period(103) = "F_Mar"
Period(104) = "F_Apr"
Period(105) = "F_May"
Period(106) = "F_Jun"
Period(107) = "F_Jul"
Period(108) = "F_Aug"
Period(109) = "F_Sep"
Period(110) = "F_Oct"
Period(111) = "F_Nov"
Period(112) = "F_Dec"


Period(201) = "B_Jan"
Period(202) = "B_Feb"
Period(203) = "B_Mar"
Period(204) = "B_Apr"
Period(205) = "B_May"
Period(206) = "B_Jun"
Period(207) = "B_Jul"
Period(208) = "B_Aug"
Period(209) = "B_Sep"
Period(210) = "B_Oct"
Period(211) = "B_Nov"
Period(212) = "B_Dec"


Set db = CurrentDb


'table is located in BE
strSQLFrom = "SELECT * FROM tblEmployeeList;"
Set rstFrom = db.OpenRecordset(strSQLFrom, dbOpenSnapshot)
'table is located in FE
strSQLTo = "SELECT * FROM tblPayrollCost;"
Set rstTo = db.OpenRecordset(strSQLTo, dbOpenDynaset)


' Move to last record of the table to get the total number of records.
rstFrom.MoveLast
Counter = rstFrom.RecordCount


' Move back to first record.
rstFrom.MoveFirst
 
For i = 1 To Counter
       
    'Copy the values from tblEmployeeList to tblPayrollCost and calculate for the cost
    
    ' calculate for Basic Pay
    rstTo.AddNew
    For Each fld In rstFrom.Fields
        If Left(fld.Name, 2) = "F_" Or Left(fld.Name, 2) = "B_" Then
            For Counter = 101 To 212
                If fld.Name = Period(Counter) Then
                    rstTo(fld.Name) = CalculateAmount(1000, rstFrom!JobTitle)
                    Exit For
                End If
            Next
            
        Else
            rstTo(fld.Name) = rstFrom(fld.Name)
        End If
    Next
    rstTo.Update
    
    ' calculate for Overtime
    rstTo.AddNew
    For Each fld In rstFrom.Fields
        If Left(fld.Name, 2) = "F_" Or Left(fld.Name, 2) = "B_" Then
            For Counter = 101 To 212
                If fld.Name = Period(Counter) Then
                    rstTo(fld.Name) = CalculateAmount(2000, rstFrom!JobTitle)
                    Exit For
                End If
            Next
            
        Else
            rstTo(fld.Name) = rstFrom(fld.Name)
        End If
    Next
    rstTo.Update
    
    ' calculate for Food Allowance
    rstTo.AddNew
    For Each fld In rstFrom.Fields
        If Left(fld.Name, 2) = "F_" Or Left(fld.Name, 2) = "B_" Then
            For Counter = 101 To 212
                If fld.Name = Period(Counter) Then
                    rstTo(fld.Name) = CalculateAmount(3000, rstFrom!JobTitle)
                    Exit For
                End If
            Next
            
        Else
            rstTo(fld.Name) = rstFrom(fld.Name)
        End If
    Next
    rstTo.Update
    
    ' calculate for Car Allowance
    rstTo.AddNew
    For Each fld In rstFrom.Fields
        If Left(fld.Name, 2) = "F_" Or Left(fld.Name, 2) = "B_" Then
            For Counter = 101 To 212
                If fld.Name = Period(Counter) Then
                    rstTo(fld.Name) = CalculateAmount(4000, rstFrom!JobTitle)
                    Exit For
                End If
            Next
            
        Else
            rstTo(fld.Name) = rstFrom(fld.Name)
        End If
    
    Next
    rstTo.Update


    rstFrom.MoveNext


Next i


rstFrom.Close
Set rstFrom = Nothing
rstTo.Close
Set rstTo = Nothing


db.Close
Set db = Nothing
    
End Sub


Function CalculateAmount(ByVal WageType As Long, ByVal JobTitle As String)


Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String


Set db = CurrentDb


strSQL = "SELECT * FROM tblSalaryCost WHERE " & _
    "((WageType=" & WageType & ") OR (JobTitle='" & JobTitle & "');"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)


With rst
    If .EOF Then Exit Function
    
    CalculateAmount = !Amount
    
End With


End Function

[orange]

Can you provide some basic documentation for your code? Just an overview.
You are defining an array Period with 1000 entries, but you're only using 24??
You select every field Select *? Why?

[stigmatized]

Hi orange,

I have attached my sample code in the test.accdb. I am using "SELECT *... in the query to include all the fields necessary from the source table to the new table.

I hope there is a way to improve the performance of the FE considering that it will be in the network and will be used by multiple users.

thanks!

[robrich22]

I was taking at look at the attached .accdb. Honestly, No offense but that code needs to be re-written. Can you reattach the database with tables (structure) only. No data, so I can get a better feel for what your doing? I'm thinking this is more of a project you need to contract out, and pay for a professional solution.

[June7]

Payroll is really difficult (taxes and labor laws and advances and benefit perks) and there are plenty of OTS software to accomplish. I used QuickBooks. Have to update it every year for changes in tax structure but that is a fairly inexpensive investment for the peace of mind it offers.

[stigmatized]

Payroll is really difficult (taxes and labor laws and advances and benefit perks) and there are plenty of OTS software to accomplish. I used QuickBooks. Have to update it every year for changes in tax structure but that is a fairly inexpensive investment for the peace of mind it offers.

hi june7,

to tell you honestly, i am not a programmer but an accountant by profession. I learned vb through the scripts/code from the internet and youtube tutorials.

I am just doing this to lessen my duties in the consolidation process. Actually i'm using this FE/BE database to calculate the payroll amount during our budgeting season that's why i have come up to this.

Thanks.

stigmatized

[rpeare]

I can tell you generally what's wrong with this code but I am going to echo the other folks. Payroll is one of those things that you really do not want to mess around with on your own unless it's strictly informational in nature and you can't be audited/cited/imprisoned for inconsistencies or bad data.

Some of what I'm going to put down others have already mentioned.

1. When you open a recordset with code, limit that data to the smallest possible set of data that you're going to need. You are opening three different data sets using SELECT *. Two of those datasets are probably on larger tables (EMPLOYEELIST, PAYROLLCOST) You should limit no only the fields you select but the amount of it you select, for instance if this is a process you run once a month, select only the records for the new month, in other words put in a WHERE criteria to select only the pertinent records.
2. You don't need to MOVELAST to get to the last record of a recordset you can just issue DO WHILE RST.EOF <> TRUE, if you need to check for a null dataset you can issue IF RST.RECORDCOUNT = 0 THEN. What you're doing with the MOVELAST is unnecessary and though minor, is probably slowing down your code.
3. I am guessing based on how you are defining your array (999 items) but you're only using a portion of them that you are using a NON-NORMALIZED structure. What this means, in essence is that you are storing records horizontally instead of vertically, for instance:

tblExpense

Exp_ID	Emp_ID	Act_Date	Travel	Mileage	Meals
1	1	1/1/2014	10$	15$	10$
2	1	1/2/2014	15$	25$	20$
3	2	1/3/2014	5$	10$

Instead of:
tblExpense

Exp_ID	Emp_ID	Act_Date
1	1	1/1/2014
2	1	1/2/2014

tblExpenseDetail

ExpDet_ID	Exp_ID	Exp_CatID	Amt
1	1	1	10$
2	1	2	15$
3	1	3	10$
4	2	1	15$
5	2	2	25$
6	2	3	20$
7	3	1	5$
8	3	2	10$

Where ExpCat_ID would link to a table defining what travel, mileage and meals are.

Typically more normalized structures are much faster to process, at least in my experience.

4. look at the individual number of records your code is processing.
Let's assume for the sake of argument that your table EMPLOYEELIST has 20 columns
Further let's assume that you have (as you stated in a post above) 1000 employees (20 users with 50 employees each)

As it stands you are processing EVERY EMPLOYEE every time you run this code so you are cycling through 1000 records every time.
Further, you are cycling through 20 columns for each of these employees 4 times
20*4 = 80
80 * 1000 = 80,000

So far, without even doing anything else you've multiplied your processing way out of proportion to what you want to accomplish I think.

Next you're cycling from 101 through 212 (111 cycles) four times as well which is a total multiplier of 444 for each field that starts with B_ or F_.
Let's assume for the sake of argument that you only have 5 fields that start B_ or F_ you are cycling through 444 items per field which would be

(80 + (5 * 444)) * 1000 = 2,300,000

This is the number of checks your code is doing. It excludes looking up values from your SALARYCOST table. It excludes the time for adding records to your table PAYROLLCOST.

[stigmatized]

hi rpeare,

now i realized that my codes are so complex in the sense that im using codes which is not useful or shall i say redundant.

i want to know in detail how to make your point 3. can you share some code to do it because i have no idea how to simplify my code.

thanks!!!!