Speed up code

**jgelpi16** · 02-10-2011, 07:33 AM

Ok.....I give. I was trying to avoid this as I have received some excellent help/tips from ajetrumpet on speeding up my code. I added SQL statements in place of entire tables where I can. However, this thing is still taking 8 hours to run! I have tried to pare down the code as much as possible. Can someone please take a look at this and give me some times on speeding up the execution? the table "TblOracleEmp" is a linked table that exists on a server, but normal access to the table is as quick as if it were local. All other tables are local tables. Thank you in advance...

First loop:

Code:

    Do While Not rs3.EOF
    Set rs3 = db.OpenRecordset("TblOracleEmp", dbOpenDynaset)
    rs3.MoveLast
    rs3.MoveFirst

        vEmplID = rs3("A$EMPLOYEE_NUMBER")
        vDate = rs3("DATE_IN_JOB")
        vLocation = rs3("LOCATION")
        vPayType = rs3("PAY_TYPE")
        Call funAuditTrail(vEmplID, vLocation, vDate, vPayType)
        rs3.MoveNext
    Loop

Function funAuditTrail:

Code:

mySQLrs = "SELECT * FROM tblAudit WHERE [A$EMPLOYEE_NUMBER] = " & emplID & ";"
Set rs = db.OpenRecordset(mySQLrs, dbOpenDynaset)

mySQLrs = "SELECT * FROM TblOracleEmp WHERE [A$EMPLOYEE_NUMBER] = " & emplID & ";"
Set rs1 = db.OpenRecordset(mySQLrs, dbOpenDynaset)
rs1.MoveLast
rs1.MoveFirst

rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
rs1.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID


If rs.RecordCount = 0 Then
    If vCurrPayType = "Monthly Salary" Then
          'Execute SQL Statement referenced in next block of code
          rs.Close
          rs1.Close
          Exit Function
    End If
Else
    rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
    vLocation = rs("LOCATION")
    vPayType = rs("PAY_TYPE")
    vAppendDate = rs("RecordUpdateDate")
    While Not rs.NoMatch
        If vAppendDate < rs("RecordUpdateDate") Then
            vAppendDate = rs("RecordUpdateDate")
        End If
        rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
    Wend
    While Not rs.NoMatch
        vPosition = rs("JOB_NAME")
        vPositionDate = rs("DATE_IN_JOB")
        vCurrAppendDate = rs("RecordUpdateDate")
    If vCurrAppendDate >= vAppendDate Then
        If vPositionDate < vCurrPositionDate Then
                'Execute SQL Statement referenced in next block of code
        End If
       If vLocation <> vCurrLoc Then
                'Execute SQL Statement referenced in next block of code
        End If
       If vPayType <> vCurrPayType Then
                'Execute SQL Statement referenced in next block of code
        End If
    End If
    rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
    Wend
End If
rs.Close
rs1.Close

SQL Code:

Code:

            vDOWNLOAD_DT = rs1("DOWNLOAD_DT")
            vACTUAL_TERM_DATE = rs1("ACTUAL_TERM_DATE")
            If IsNull(vACTUAL_TERM_DATE) Then
                vACTUAL_TERM_DATE = #1/1/9999#
            End If
            vPAY_CHANGE_DATE = rs1("PAY_CHANGE_DATE")
            vDATE_IN_JOB = rs1("DATE_IN_JOB")
            vTHIS_PERIOD_START_DATE = rs1("THIS_PERIOD_START_DATE")
            vDATE_OF_HIRE = rs1("DATE_OF_HIRE")
            vEMPLOYEE_NUMBER = rs1("A$EMPLOYEE_NUMBER")
            vFULL_NAME = rs1("A$FULL_NAME")
            vJOB_NAME = rs1("JOB_NAME")
            vPAY_CHANGE_REASON = rs1("PAY_CHANGE_REASON")
            vBusiness_Title = rs1("Business_Title")
            vSUPERVISOR_FULL_NAME = Nz(rs1("SUPERVISOR_FULL_NAME"))
            If vSUPERVISOR_FULL_NAME = "" Then
                vSUPERVISOR_FULL_NAME = "N/A"
            End If
            vASSIGNMENT_STATUS = rs1("ASSIGNMENT_STATUS")
            vLocation = rs1("LOCATION")
            vPERSON_TYPE_USAGE = rs1("PERSON_TYPE_USAGE")
            vPAY_TYPE = rs1("PAY_TYPE")

            mySQL = "INSERT INTO tblAudit (DOWNLOAD_DT, [A$EMPLOYEE_NUMBER], [A$FULL_NAME], ASSIGNMENT_STATUS, DATE_OF_HIRE," & _
                        "JOB_NAME, LOCATION, PERSON_TYPE_USAGE, SUPERVISOR_FULL_NAME, THIS_PERIOD_START_DATE, ACTUAL_TERM_DATE," & _
                        "PAY_TYPE, PAY_CHANGE_DATE, PAY_CHANGE_REASON, DATE_IN_JOB, Business_Title) VALUES (#" & vDOWNLOAD_DT & "#, " & vEMPLOYEE_NUMBER & "," & _
                        "'" & vFULL_NAME & "', '" & vASSIGNMENT_STATUS & "', #" & vDATE_OF_HIRE & "#, '" & vJOB_NAME & "', '" & vLocation & "'," & _
                        "'" & vPERSON_TYPE_USAGE & "', '" & vSUPERVISOR_FULL_NAME & "', #" & vTHIS_PERIOD_START_DATE & "#, #" & vACTUAL_TERM_DATE & "#," & _
                        "'" & vPAY_TYPE & "', #" & vPAY_CHANGE_DATE & "#, '" & vPAY_CHANGE_REASON & "', #" & vDATE_IN_JOB & "#, '" & vBusiness_Title & "')"
                        CurrentDb.Execute mySQL, dbFailOnError

I know this is a lot and I thank you for any tips/insight on speed of execution you can provide.

**weekend00** · 02-10-2011, 08:54 AM

at first look, you codes are very confusion.
I think we can use much less code to do what you want to, however, since I don't know actually what you want, I could not go father.

First loop:

Code:

   Do While Not rs3.EOF
   Set rs3 = db.OpenRecordset("TblOracleEmp", dbOpenDynaset)
   rs3.MoveLast
   rs3.MoveFirst
It looks like a endless loop here, should you open the recordset before the loop like this?
   Set rs3 = db.OpenRecordset("TblOracleEmp", dbOpenDynaset)
   rs3.MoveLast
   rs3.MoveFirst
   Do While Not rs3.EOF
        vEmplID = rs3("A$EMPLOYEE_NUMBER")
        vDate = rs3("DATE_IN_JOB")
        vLocation = rs3("LOCATION")
        vPayType = rs3("PAY_TYPE")
        Call funAuditTrail(vEmplID, vLocation, vDate, vPayType)
why do you use a function here? you open the table tblOracleEmp here, the open another recordset on this table in you function, it looks not necessary. opening a recordset again and again cost lots of time.
 
        rs3.MoveNext
 
    Loop

Function funAuditTrail:

Code:

 
mySQLrs = "SELECT * FROM tblAudit WHERE [A$EMPLOYEE_NUMBER] = " & emplID & ";"
Set rs = db.OpenRecordset(mySQLrs, dbOpenDynaset)
 
mySQLrs = "SELECT * FROM TblOracleEmp WHERE [A$EMPLOYEE_NUMBER] = " & emplID & ";"
Set rs1 = db.OpenRecordset(mySQLrs, dbOpenDynaset)
rs1.MoveLast
rs1.MoveFirst
 
rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
rs1.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
 
 
If rs.RecordCount = 0 Then
    If vCurrPayType = "Monthly Salary" Then
          'Execute SQL Statement referenced in next block of code
          rs.Close
          rs1.Close
          Exit Function
    End If
Else
    rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
    vLocation = rs("LOCATION")
    vPayType = rs("PAY_TYPE")
    vAppendDate = rs("RecordUpdateDate")
    While Not rs.NoMatch
        If vAppendDate < rs("RecordUpdateDate") Then
            vAppendDate = rs("RecordUpdateDate")
        End If
        rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
    Wend
   While Not rs.NoMatch
Not rs.NoMatch is always false here, because you just finish a loop with same criteria. so this loop will never be run.
        vPosition = rs("JOB_NAME")
        vPositionDate = rs("DATE_IN_JOB")
        vCurrAppendDate = rs("RecordUpdateDate")
    If vCurrAppendDate >= vAppendDate Then
        If vPositionDate < vCurrPositionDate Then
                'Execute SQL Statement referenced in next block of code
        End If
       If vLocation <> vCurrLoc Then
                'Execute SQL Statement referenced in next block of code
        End If
       If vPayType <> vCurrPayType Then
                'Execute SQL Statement referenced in next block of code
        End If
    End If
    rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
    Wend
End If
rs.Close
rs1.Close

**jgelpi16** · 02-10-2011, 09:06 AM

Thanks for the tips. I actually open the rs at the very beginning of my code. That was a copy/paste error.

I guess I have the function because of the amount of code that is being executed. The recordsets in the functions are based on queries though and should have one record in each of them. Does that make a difference?

regarding the rs.NoMatch....I need to verify I copied the code correctly because I do have proof in my tables that the code did run. Maybe I neglected to copy/past in part of the code.

**ajetrumpet** · 02-10-2011, 09:13 AM

hey bud, I didn't read through it all, but here are two things I noticed right off:

1) weekend is right. do you realize that you're code is working 500 times harder than it should have to? you've got opening and closing happening everywhere, and on every loop! change this:

Code:

    Do While Not rs3.EOF
    Set rs3 = db.OpenRecordset("TblOracleEmp", dbOpenDynaset)
    rs3.MoveLast
    rs3.MoveFirst

        vEmplID = rs3("A$EMPLOYEE_NUMBER")
        vDate = rs3("DATE_IN_JOB")
        vLocation = rs3("LOCATION")
        vPayType = rs3("PAY_TYPE")
        Call funAuditTrail(vEmplID, vLocation, vDate, vPayType)
        rs3.MoveNext
    Loop

to this:

Code:

    Set rs3 = db.OpenRecordset("TblOracleEmp", dbOpenDynaset)
    rs3.MoveLast
    rs3.MoveFirst
    Do While Not rs3.EOF
        vEmplID = rs3("A$EMPLOYEE_NUMBER")
        vDate = rs3("DATE_IN_JOB")
        vLocation = rs3("LOCATION")
        vPayType = rs3("PAY_TYPE")
        Call funAuditTrail(vEmplID, vLocation, vDate, vPayType)
        rs3.MoveNext
    Loop

that's one of your biggest issues. that'll shave plenty of time off the whole thing. and secondly:

2) do you realize that you have statements that source from one recordset open TWICE the entire time the first loop is running? as in, you have two instances of it open at once. I don't what the consequences are for that, but I'm sure one of them is a slow processing time. Do it another way...

Logically, this makes no sense, because you've got the first loop open to loop through the recs and run a function that pertains to THAT record only. Couldn't you write that code located in the function, and even in a consolidated nature, in the same loop? That way, you don't have to open the recordset again. Again, that whole thing doesn't even make logical sense. That's like giving a drowning man a glass of water. Duplication that does double evil, while still being unproductive to the purpose.

**weekend00** · 02-10-2011, 10:00 AM

after hours's digging in you code, I am still not clear in some part, but it looks like we can approch the goal by a couple queries instead of pages of codes. and I am sure queries will run faster than your code.

so far, based on my understanding, you are going to add an audit record if:
1 no audit for this employeeID
or
2 one of vlocation,vpaytype,vpositiondate is changed comparing with the lastest audit record. (vCurrAppendDate >= max(RecordUpdateDate) and (vPositionDate < vCurrPositionDate or vLocation <> vCurrLoc or vPayType <> vCurrPayType) )

but I don't know where are vCurrLoc, vcurrPayType, vCurrpositionDate from.

and did you mean employeeID is unique in tblOrcalEmp?

I found a very interesting thing in you code, if I am right,

Code:

   rs.FindFirst "[A$EMPLOYEE_NUMBER] = " & emplID
    vLocation = rs("LOCATION")
    vPayType = rs("PAY_TYPE")
    vAppendDate = rs("RecordUpdateDate")
   While Not rs.NoMatch
       If vAppendDate < rs("RecordUpdateDate") Then
           vAppendDate = rs("RecordUpdateDate")
       End If
       rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
   Wend
You scan the whole recordset to get the lastest RecordUpdateDate, it's all right, although DMAX() is much simpler
 
 
   While Not rs.NoMatch
here, you scan the whole recordset, but actually, you are looking for only one record with the latest RecordUpdateDate,
 which you can find by rs.findfirst().  At first look at you code, I thought you are to add a new audit record for each 
record in rs. but since VAppendDate is the max date in rs,  only the record with max date can meet criteria 
"vCurrAppendDate >= vAppendDate", I think there is only one record that match.  It looks like you put a "trap" here to 
make it more difficult for other people to understand.
        vPosition = rs("JOB_NAME")
        vPositionDate = rs("DATE_IN_JOB")
        vCurrAppendDate = rs("RecordUpdateDate")
    If vCurrAppendDate >= vAppendDate Then
        If vPositionDate < vCurrPositionDate Then
                'Execute SQL Statement referenced in next block of code
        End If
       If vLocation <> vCurrLoc Then
                'Execute SQL Statement referenced in next block of code
        End If
       If vPayType <> vCurrPayType Then
                'Execute SQL Statement referenced in next block of code
        End If
    End If
    rs.FindNext "[A$EMPLOYEE_NUMBER] = " & emplID
    Wend

Maybe I am not right.

**jgelpi16** · 02-10-2011, 10:23 AM

Wow....Thank you both for your input. I realize I do most definitely need to make some changes to by code...One of the "issues" was a copy/paste error. Setting the recordset and rs.movelast/rs.movefirst is done at the beginning of my code where I dim my variables, not in the loop.

weekend - I'll have to look into DMAX() but it sounds like it will do exactly what I need to do. I am looking for the most recent time that employeeID was added to the table and then comparing the attributes of it to what currently exists in the TblOracleEmp.

The TblOracleEmp table has one instance of each employee.

Pertaining to weekend00's second point. The reason I am scanning the entire recordset is to make sure I get the most recent append date. So...I want the most recent append date for employeeID. My date is truly a date, not date/time so there could be several records with the same append date. I need to look at both the append date and the employeeID together.

Fortunately my code actually ran much quicker today. I think it is because I had to leave my computer alone for a meeting. I will make these suggested changes and let you know the outcome.

Thanks again to the both of you for your help. It is greatly appreciated!

**weekend00** · 02-10-2011, 10:31 AM

I looked, I thought, I dug into you code, then I got conclusion:
you compare each employee in tables tblOracalEmp with lastest record of this employee in table tblAudit, if any of vrecordupdatedate,vlocation,vpaytype,vpositiondate is changed, add a record to tblaudit.
based on above, I have these queries:

Query1: select latest record from tblAudit for each employee
select a.[A$EMPLOYEE_NUMBER],max(a.recordupdatedate) as recordupdatedate from tblAudit as a group by [A$EMPLOYEE_NUMBER]

Query2: lastest record in tblAudit
select a.[A$EMPLOYEE_NUMBER],a.recordupdatedate,a.location,a.pay_type,a.date_i n_job from tblAudit as a inner join Query1 as b on a.[A$EMPLOYEE_NUMBER]=b.[A$EMPLOYEE_NUMBER] and a.recordupdatedate=b.recordupdatedate

Query3: add audit trial
INSERT INTO tblAudit select a.* from TblOracleEmp as a left join query2 as b on a.[A$EMPLOYEE_NUMBER] =b.[A$EMPLOYEE_NUMBER] WHERE b.[A$EMPLOYEE_NUMBER] is null or (a.recordupdatedate>=b.recordupdatedate and (a.DATE_IN_JOB>b.DATE_IN_JOB or a.location<>b.location or a.pay_type<>b.pay_type)) is null

Query3 is the query to run.

Please note: recordupdatedate,location,pay_type,date_in_job should not have null value.

**ajetrumpet** · 02-10-2011, 10:35 AM

Originally Posted by jgelpi16

I think it is because I had to leave my computer alone for a meeting.

hell, if that's all it takes, I'm just gonna buy another laptop and switch my computer usage time to both of them...back and forth, back and forth. At that right, I'll never have another computer issue again!

thanks, bud!

**jgelpi16** · 02-10-2011, 10:41 AM

hell, if that's all it takes, I'm just gonna buy another laptop and switch my computer usage time to both of them...back and forth, back and forth. At that right, I'll never have another computer issue again!

Yea....that's what I get for making a stupid remark :-p....Lemme know how that second computer works for your ajetrumpet. Maybe I'll start a new ad campaign for Microsoft....."All you need is two computers and you'll be able to get your work done in half the time!"

**weekend00** · 02-10-2011, 10:43 AM

Originally Posted by jgelpi16

Wow....Thank you both for your input. I realize I do most definitely need to make some changes to by code...One of the "issues" was a copy/paste error. Setting the recordset and rs.movelast/rs.movefirst is done at the beginning of my code where I dim my variables, not in the loop.

weekend - I'll have to look into DMAX() but it sounds like it will do exactly what I need to do. I am looking for the most recent time that employeeID was added to the table and then comparing the attributes of it to what currently exists in the TblOracleEmp.

The TblOracleEmp table has one instance of each employee.

Pertaining to weekend00's second point. The reason I am scanning the entire recordset is to make sure I get the most recent append date. So...I want the most recent append date for employeeID. My date is truly a date, not date/time so there could be several records with the same append date. I need to look at both the append date and the employeeID together.

Fortunately my code actually ran much quicker today. I think it is because I had to leave my computer alone for a meeting. I will make these suggested changes and let you know the outcome.

Thanks again to the both of you for your help. It is greatly appreciated!

in this case (above marked in red words), you may get trouble if you have two audit records in same day, you will add new record to tblAudit at each time you run you code.
e.g. (I omit employeeid)

tblOracleEmpl:
recordupdatedate,location,pay_type,date_in_job
1/1/2011,LocationA,typeA,12/31/2010

tblAudit:
recordupdatedate,location,pay_type,date_in_job
1/1/2011,OldLocation,typeA,12/31/2010

after you run you code, tblOracleEmpl will not change, and tblAudit will be:
recordupdatedate,location,pay_type,date_in_job
1/1/2011,OldLocation,typeA,12/31/2010
1/1/2011,LocationA,typeA,12/31/2010

run you code again, you keep adding one new record in tblAudit:
recordupdatedate,location,pay_type,date_in_job
tblAudit:
1/1/2011,OldLocation,typeA,12/31/2010
1/1/2011,LocationA,typeA,12/31/2010
1/1/2011,LocationA,typeA,12/31/2010

Conclusion: you should have date/time instead of pure Date

**jgelpi16** · 02-10-2011, 10:52 AM

I want to add a new record for each employeeID. This process is designed to run weekly so if one of the attributes for an employeeID change from week to week then I want to append a new record into tblAudit. Each week the TblOracleEmp table is updated but there is no history there. So week1 in TblOracleEmp you'll have empID = 123, Pay_Type = Hourly. Week2 in TblOracleEmp you'll have empID = 123, Pay_Type = Monthly Salary. I want to capture that change from week one to week two if there is a change to RecordUpdateDate, Location, Pay_Type or Date_In_Job. So I do want to keep the Date and Time separate because I'm only doing the compare on a weekly basis. Does that make more sense?

**weekend00** · 02-10-2011, 11:02 AM

OK,

would you take a look at my post #7?

**jgelpi16** · 02-10-2011, 12:38 PM

Thanks for all your help guys. I've got my code running a lot faster now. I do owe ya one!

Speed up code

Thread Tools

Speed up code

Similar Threads

Improve query speed

Need for Speed

Increase search speed

Query speed issues

Speed issue

Tags for this Thread

Posting Permissions