Access 2013 Slow Performance

I am working with a client app developed in Access 2003.  Eventually we will be converting the app to run in Access 2013.  

I have several machines in the office with different version of Access and different operating systems.  There is a very long running 'Aging' process in the application being converted.  It runs thru all of the entries in the DB and ages the accounts at the threshold of a new month, adding any new interest, penalties and fees due as of the new month.

Out of curiosity I ran the process on three different machines in the office.  At this point the application (both the front end and data_ are in MDB format.  The backend/data MDB is about 1GB in size.

In all cases the app was run 'locally' meaning the front end and data were in the same directory on C: drive of the machine.

All machines have the full version of Access installed.

Machine 1:  Win 7 Pro, Access 2010, i7 processor 16GB memory.
The process ran 1hr and 17 minutes.  I ran it multiple times, all times were within a minute or two of 1:17.

Machine 2:  Win 8 Pro, Access 2010, i7 processor 32GB memory.
In multiple test the processing time were very close to the time on machine 1, 1:17 - 1:20.

Machine 7:  Win 7 Pro, Access 2013, i7 processor 24GB memory.
In multiple tests the processing time was about 10 hours, or 7.5 times longer than either of the other machines.

I have run these tests multiple times on each machine with pretty close, within minutes, to the same result.  At one point I even upgraded the app from MDB's to Accdb's using the 2013 'save as'.  I de-compiled the app and recompiled it in 2013 and ran it on both Machine 3 (2013) and Machine 2 (2010).  Machine 3 was still in the 1:20 range, Machine 1 was still in the 10 hour range.

One thought I had was that maybe Access 2013 was 64 bit version but it shows 32 bit on the 'About' screen.  The version of Office Professional 2013 was installed from a download and activated with a keycard.  Both 2010 versions were installed from an install CD for Office 2010 professional

Why is the 2013 execution drastically slower?  This is exactly the same code, run in exactly the same way, the only difference being machine 3 has Access 2013 installed.  Machine 3 has more memory and a faster processor than Machine 1.  I find this very disturbing and have no explanation.

Does anyone have any ideas or experienced similar results and hopefully have a solution?

I won't be able to upload the app or the data due to sensitive content.
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Would it be possible for you to run a Win 8 Pro, Access 2013 test?

/gustav
mlcktmguyAuthor Commented:
I would have to purchase another license to do that.  I was hoping someone would have insight or experience wit this problem.
Gustav BrockCIOCommented:
No, you can run an 90 day evaluation. Still, I realize it will take some work to set it up.

I run normally only Win 8.1 and Access 2013 but have other machines running Win 7 and Access 2010. I don't feel 2013 being slow but haven't done direct measurements.

Couldn't you time your steps in your aging process and see if it is certain steps that are the bottleneck?

/gustav
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm running 2013 almost exclusively, and have not found any performance issues. I'd be more willing to bet on troubles with the machine/environment rather than Access. Do you have another machine with 2013 installed? I'd be curious to see if the trouble follows 2013, or if it's specific to that machine.

As gustav indicates, one way to determine where the bottleneck occurs is to include logging code. I generally insert a record into a table with some sort of descriptive text, and a time stamp. After the process runs, I can then examine the "flow" and see where the trouble is.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As the others have said, to really pin this down, you're going to need to get A2013 on another machine, and preferably one of the ones that runs A2010 OK.

I'd also agree it's probably something with the machine.  ACE (the database engine) has changed little from A2007 and up.

But there are a couple of things I'd check right off on machine #7:

1. Using task manager, check the amount of free memory.  Even though it has 24GB, if other processes are chewing up memory, DB operations might not be as fast if by some odd chance the OS has to start paging.  This is not probable, but I've learned never to say never and it's a quick check.

2. Ensure that the DB is not being virus scanned or monitored in any way (say a backup utility that checks for file changes).

3. With task manager, monitor the amount of memory used by MSACCESS.EXE in comparison to the other stations.  If it's drastically different, then look at MaxBuffers and MaxLocksPerFile (and you may just want to check those anyway right off).   MaxBuffers controls the size of the DB cache, MaxLocksPerFile the number of outstanding locks before an error a transaction must be done.

4. Check TMP and TEMP environment variables.   Make sure they point to a valid drive and directory.

5. Flush those directories.  If loaded with files entries, it can take quite a while to create a temp file, which JET/ACE will do for some DB operations.

Last, with all that said, you may find that it is A2013.  Microsoft does not document by far what changes it does from release to release and there are things in JET/ACE that can cause a problem like this.

Once I saw a situation where the query processor would choose one execution plan if at the beginning of a query one table was empty, and a totally different method if it was full.   The difference between the two was on the order of what your seeing.

 Since query plans are saved, it would continue on like that whether the table was full or not, and thus always yield good or slow performance.

 So that brings us to SHOWPLAN.  If after checking out all the easy stuff nothing comes up, you will want to try this.  SHOWPLAN shows you the costing plan of a query.  It doesn't work for sub or nested queries however so it's use is limited.

On the main query however, you'd be able to see if it was doing the same costing plan between each of the machines.  If different, that could be along the lines of what I mentioned above,  or something did get changed in A2013.

Jim.
mlcktmguyAuthor Commented:
Thanks, all excellent suggestions.  I suspected it was something unique about the machine also.

Not sure if this could be pertinent or not:  The machine now running 2013, originally was loaded with 2010.  I uninstalled 2010 then downloaded and installed 2013 using a card key code to activate.

I don't have any other 2013 machines currently.  I want to try one installing 2013 on the Win 8 Pro machine that was running 2010.  In have no issue purchasing another license for 2013 , if were going to be using it for development but that's up in the air until the performance issue is resolved.

Gustav mentioned that I could download a 90 day trial version.  I have been to the MS website and don't see any 2013 trial versions available for download that include Access 2013.  Do you have a link to the download you are referring to?

Jim Dettman: I appreciate your detailed comments and suggestions but I'm not sure how to check the items you refer to:

" look at MaxBuffers and MaxLocksPerFile (and you may just want to check those anyway right off).   MaxBuffers controls the size of the DB cache, MaxLocksPerFile the number of outstanding locks before an error a transaction must be done."

Not sure how to do that.

'4. Check TMP and TEMP environment variables.   Make sure they point to a valid drive and directory.

5. Flush those directories.  If loaded with files entries, it can take quite a while to create a temp file, which JET/ACE will do for some DB operations."

Not sure how to do this either?

" So that brings us to SHOWPLAN. "

Not sure how to check this
Gustav BrockCIOCommented:
Oh, I had Windows 8.1 trial in mind.

But Office 2013 Pro is here for a 60 days evaluation (and I believe a further 30 days grace period for the asking):

http://www.microsoft.com/en-us/evalcenter/evaluate-office-professional-plus-2013

/gustav
mlcktmguyAuthor Commented:
Thanks for the link, I was able to install 2013 on the Win 8 Pro machine.

The result is that the Win 8 Pro (Machine 2) which previously ran the same process in 1 hours and 20 minutes under Access 2010, is now processing just as slowly as the other machine that was running Access 2013.

The Win 8 machine has not had time to complete but it on a pace to take at least 10 hours.

This result would seem to point to 2013 being the issue, or more specifically something about the code in this process not interacting very well with 2013, but 2010 works just fine.

I also noted some of the processing specs while running the process on the Win 7 Pro machine with Access 2013.

Memory usage: 2.4GB (Out of 24)
Cpu : 10%
Physical Memory: 10%

There were not other processes running.

Any ideas or suggestions?
Gustav BrockCIOCommented:
No other than time your steps in your aging process and see if it is certain steps that are the bottleneck. If so, that could give a hint.

It could be some specific VBA code or a single query behaving strangely in A2013.

/gustav
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<" look at MaxBuffers and MaxLocksPerFile (and you may just want to check those anyway right off).   MaxBuffers controls the size of the DB cache, MaxLocksPerFile the number of outstanding locks before an error a transaction must be done."

Not sure how to do that.>>

  They are both registry settings, although the easy way to get to them for testing is using the dbEngine.Setoptions command:

dbEngine.SetOption dbMaxBufferSize, 65536

On the Maxlocksperfile, if your doing "action" queries (INSERTS, UPDATES, DELETES), you can avoid that all together by setting a queries UseTransaction property to no.

If you don't want to do that, or are doing SELECTs, then set that to something like 200,000 at least (default is 10,000) and see if it makes a difference.

<<'4. Check TMP and TEMP environment variables.   Make sure they point to a valid drive and directory.

5. Flush those directories.  If loaded with files entries, it can take quite a while to create a temp file, which JET/ACE will do for some DB operations.">>

 Drop to a command prompt (accessories, command prompt) and type SET followed by a return.

TMP and/or TEMP will be set to a path.   Navigate to that path and delete anything with *.tmp


<<" So that brings us to SHOWPLAN. ">>

 Show plan is a registry setting you change and once set, any query executed generates a text file.  The key should be:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

for 64 bit and:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

 For 32 bit Office running on a 64 bit OS.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and on the SHOWPLAN, make sure you run Regedit with a right click and "run as admin".  Also, that's a text key.  If the path/key does not exist, you'll need to create it.

What you'll get is a text file showplan.out with the costing plan.

Jim.
mlcktmguyAuthor Commented:
I'm putting together some timing test, should have results soon.
mlcktmguyAuthor Commented:
Some specific code running slower in 2013 Than 2010

Just as a reminder, this code was run on the same machine, first in Access 2010.  Access 2010 was uninstalled and Access 2013 was installed. The code was then run in Access 2013.  The timing loop I have in the module has this code consistently running 2 to 3 times slower in 2013.  I can't get more granular than 1/18 of a second using the 'Timer' function.

Public Sub writeBalancingDetail(passedUserName As String, _
                                passedDateTimeRevised As Date, _
                                passedDateOfNumbers As Date)
'
' This routine writes the detail recs created when the payoff was calulated
'
' Yearly tax records
'Dim wkTaxRecID As Long
Dim wkBRT As Long
Dim wkTaxYear As Long
Dim wkPrincipalBalDue As Double
Dim wkPenaltyBalDue As Double
Dim wkInterestBalDue As Double
Dim wkLienBalDue As Double
Dim wkAttyFeesBalDue As Double
Dim wkDateOfNumbers As Date
Dim wkPayStatusID As Long
'
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               If IsDeveloper Then
                               Else
                                 On Error GoTo writeBalancingDetail_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

wkDateOfNumbers = passedDateOfNumbers
'
selectString = "select * from qrytblzTmpWk_TaxRecMain_BalDue_Local_UpdateTaxRecBalance "
'
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
If rsIn2.EOF Then
    '
Else
    If rsIn2.RecordCount > 0 Then
        '
        While Not rsIn2.EOF
            '
            'wkTaxRecID = rsIn2!TaxRecID
            wkBRT = rsIn2!BRT
            wkTaxYear = rsIn2!TaxYear
            wkPrincipalBalDue = rsIn2!PrincipalBalDue
            wkPenaltyBalDue = rsIn2!PenaltyBalDue
            wkInterestBalDue = rsIn2!InterestBalDue
            wkLienBalDue = rsIn2!LienBalDue
            wkAttyFeesBalDue = rsIn2!AttyFeesBalDue
            '
            wkPayStatusID = rsIn2!PayStatusID
            '
            DoCmd.SetWarnings False
            updateString = "Update tblTaxRecs " & _
                    " SET PrincipalAmt = " & wkPrincipalBalDue & _
                    " ,   PenaltyAmt = " & wkPenaltyBalDue & _
                    " ,   InterestAmt= " & wkInterestBalDue & _
                    " ,   LienCost = " & wkLienBalDue & _
                    " ,   AttyFeesAmt= " & wkAttyFeesBalDue & _
                    " ,   DateOfNumbers = " & Chr(35) & wkDateOfNumbers & Chr(35) & _
                    " ,   PayStausID = " & wkPayStatusID & _
                    " ,   UserRevised = " & Chr(34) & passedUserName & Chr(34) & _
                    " ,   DateRevised = " & Chr(35) & passedDateTimeRevised & Chr(35) & _
                    " Where [BRT] = " & wkBRT & " And [TaxYear] = " & wkTaxYear
            'Debug.Print updateString
            DoCmd.RunSQL updateString
    '
            '
            rsIn2.MoveNext
        Wend
           
    End If
End If
'
rsIn2.Close
Set rsIn2 = Nothing
'

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
writeBalancingDetail_Error:
                               sysErrorHandler Err.Number, Err.Description, "writeBalancingDetail", "modSynchProcess", "Module"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------


End Sub


This is the query referenced in the code:

SELECT tblzTmpWk_TaxRecMain_BalDue_Local.TaxRecID, tblzTmpWk_TaxRecMain_BalDue_Local.TaxHeaderID, tblzTmpWk_TaxRecMain_BalDue_Local.PropertyID, tblzTmpWk_TaxRecMain_BalDue_Local.BRT, tblzTmpWk_TaxRecMain_BalDue_Local.TaxYear, tblzTmpWk_TaxRecMain_BalDue_Local.PrincipalBalDue, tblzTmpWk_TaxRecMain_BalDue_Local.PenaltyBalDue, tblzTmpWk_TaxRecMain_BalDue_Local.InterestBalDue, tblzTmpWk_TaxRecMain_BalDue_Local.LienBalDue, tblzTmpWk_TaxRecMain_BalDue_Local.AttyFeesBalDue, Date() AS DateOfNumbers, Round([PrincipalBalDue]+[PenaltyBalDue]+[InterestBalDue]+[LienBalDue]+[AttyFeesBalDue],2) AS TotalBalDue, IIf([TotalBalDue]>0,2,1) AS PayStatusID
FROM tblzTmpWk_TaxRecMain_BalDue_Local
ORDER BY tblzTmpWk_TaxRecMain_BalDue_Local.TaxYear;



Here is another piece of code that takes at least 3 time longer in Access 2013 as Access 2010.  It is showing a status message on the form that initiated this process:

            If dispCnt > dispMax Then
                '
                writeTimingStuff "Before Showing Status "
                '
                wkcurrTime = Now
                wkEndTimer = Timer
                wkElapsedTime = stepTime(wkstartTime, wkcurrTime)
                dispMsg = "Updating Balance Due Amounts, Processing Rec " & Trim(Str(recsRead)) & " Of " & Trim(Str(totRecs)) & " " & wkElapsedTime
                wkStatusRtn = SysCmd(acSysCmdSetStatus, dispMsg)
                '
                If passedWhereFrom = "Bogus" Then
                    Forms!zfrmBogus!lblCaption.Caption = dispMsg
                ElseIf passedWhereFrom = "AllBal" Then
                    Forms!frmUpdateAllSystem_Balances!lblStatus.Caption = dispMsg
                ElseIf passedWhereFrom = "Synch" Then
                    Forms!zfrmSynchProcess!lblStatus.Caption = dispMsg
                End If
                '
                DoEvents
                dispCnt = 0
                '
                writeTimingStuff "After Showing Status "
                '
            End If


Here is the 'StepTime' routine referenced in the above code:

Public Function stepTime(passedStartTime As Date, passedCurrTime As Date) As String
'
Dim lngHours As Long
Dim lngSeconds As Long
Dim lngMinutes As Long
Dim txtSeconds As Long
Dim txtMinutes As Long
'Dim secondsLgt As Long
'
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

                               If IsDeveloper Then
                               Else
                                 On Error GoTo stepTime_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

lngSeconds = dateDiff("s", passedStartTime, passedCurrTime)
'
lngHours = Int(lngSeconds / 3600)
lngSeconds = lngSeconds - (lngHours * 3600)
lngMinutes = Int(lngSeconds / 60)
lngSeconds = lngSeconds - (lngMinutes * 60)
'
txtMinutes = Trim(Str(lngMinutes))
txtSeconds = Trim(Str(lngSeconds))
'
'
stepTime = "Elapsed Time = " & lngHours & " Hour(s), " & txtMinutes & " Minute(s) and " & txtSeconds & " Second(s)"


'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Function
stepTime_Error:
                               sysErrorHandler Err.Number, Err.Description, "stepTime", "Common", "Module"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------


End Function

And the 'WriteTimingStuff' routine

Public Sub writeTimingStuff(passedDescription As String)
'
Static firstTimeThru As Long

Static startCdbl As Double
'
Static startDate As Date
'
Static startTimer As Double

Static prevCdbl As Double
'
Static prevDate As Date
'
Static prevTimer As Double
'
currCdbl = CDbl(Now)
currTimer = Timer
currDate = Now
'
If firstTimeThru = 0 Then
    firstTimeThru = 1
    prevCdbl = currCdbl
    prevTimer = currTimer
    prevDate = currDate
    '
    startCdbl = currCdbl
    startTimer = currTimer
    startDate = currDate
    '
End If
'
CdblDiff = currCdbl - prevCdbl
CdblElapsed = currCdbl - startCdbl

timerDiff = currTimer - prevTimer
timerElapsed = currTimer - startTimer
'
timeDiff = stepTime(prevDate, currDate)
timeElapsed = stepTime(startDate, currDate)
'
' Insert timing record
'
CurrentDb.Execute " insert into tblTimingLog_Local " & _
              "( [Description], [CdblValue] , [CdblDiff], [CdblElapsed], [TimerValue] , [TimerDiff], [TimerElapsed], [DateValue], [DateDiff], [DateElapsed]  )  " & _
  "   values(" & Chr(34) & passedDescription & Chr(34) & _
          ", " & currCdbl & _
          ", " & CdblDiff & _
          ", " & CdblElapsed & _
          ", " & currTimer & _
          ", " & timerDiff & _
          ", " & timerElapsed & _
          ", " & Chr(35) & currDate & Chr(35) & _
          ", " & Chr(34) & timeDiff & Chr(34) & _
          ", " & Chr(34) & timeElapsed & Chr(34) & _
          ")", dbFailOnError

prevCdbl = currCdbl
prevTimer = currTimer
prevDate = currDate
'
End Sub



Any insight as to why these would run significantly slower in Access 2010 than 2013 would be appreciated would be appreciated.
Public Function stepTime(passedStartTime As Date, passedCurrTime As Date) As String
'
Dim lngHours As Long
Dim lngSeconds As Long
Dim lngMinutes As Long
Dim txtSeconds As Long
Dim txtMinutes As Long
'Dim secondsLgt As Long
'
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

                               If IsDeveloper Then
                               Else
                                 On Error GoTo stepTime_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

lngSeconds = dateDiff("s", passedStartTime, passedCurrTime)
'
lngHours = Int(lngSeconds / 3600)
lngSeconds = lngSeconds - (lngHours * 3600)
lngMinutes = Int(lngSeconds / 60)
lngSeconds = lngSeconds - (lngMinutes * 60)
'
txtMinutes = Trim(Str(lngMinutes))
txtSeconds = Trim(Str(lngSeconds))
'
'
stepTime = "Elapsed Time = " & lngHours & " Hour(s), " & txtMinutes & " Minute(s) and " & txtSeconds & " Second(s)"


'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Function
stepTime_Error:
                               sysErrorHandler Err.Number, Err.Description, "stepTime", "Common", "Module"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------


End Function

Open in new window

            If dispCnt > dispMax Then
                '
                writeTimingStuff "Before Showing Status "
                '
                wkcurrTime = Now
                wkEndTimer = Timer
                wkElapsedTime = stepTime(wkstartTime, wkcurrTime)
                dispMsg = "Updating Balance Due Amounts, Processing Rec " & Trim(Str(recsRead)) & " Of " & Trim(Str(totRecs)) & " " & wkElapsedTime
                wkStatusRtn = SysCmd(acSysCmdSetStatus, dispMsg)
                '
                If passedWhereFrom = "Bogus" Then
                    Forms!zfrmBogus!lblCaption.Caption = dispMsg
                ElseIf passedWhereFrom = "AllBal" Then
                    Forms!frmUpdateAllSystem_Balances!lblStatus.Caption = dispMsg
                ElseIf passedWhereFrom = "Synch" Then
                    Forms!zfrmSynchProcess!lblStatus.Caption = dispMsg
                End If
                '
                DoEvents
                dispCnt = 0
                '
                writeTimingStuff "After Showing Status "
                '
            End If

Open in new window

Gustav BrockCIOCommented:
It is extremely slow to call this inside a loop:

  DoCmd.RunSQL updateString

It is way faster to open the recordset before the loop, then replace the SQL insert with rs.AddNew and rs.Update (for DAO, can't recall the exact methods for ADO). After the loop, close the recordset.

/gustav
mlcktmguyAuthor Commented:
Not sure I agree.  This isn't an insert, it's an update of an existing, specific record.  Using ADO you first have to locate or select the record to be updated, then update the record.

In either case I'm looking for a reason this would run slower in 2013 than in 2010.
Gustav BrockCIOCommented:
Well, I once cleaned up an ADO application doing some heavy updates/appends replacing all the DoCmd or Execute calls with rs methods. The running time dropped from about 50 minutes to a couple of minutes. The client didn't believe it at the first demo.

But what happened to all your timing? Where were the bottlenecks?

/gustav
mlcktmguyAuthor Commented:
Two big bottlenecks are the code I put in the post.
aikimarkCommented:
I recommend using dbEngine(0)(0).Execute or dbEngine(0)(0).Querydefs("nameofquery").Execute rather than DoCmd.RunSQL
Gustav BrockCIOCommented:
But what is the timing? How long does the part run? How many records are read and how many updated?

You could time the Query qrytblzTmpWk_TaxRecMain_BalDue_Local_UpdateTaxRecBalance itself for the time to open.

A simple test would be to out comment the update and time.
And now that you have the bottleneck, you could time part of it. At least the loop. I see no other method as nothing indicates why this should run slower in A2013.

/gustav
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Have you tried setting MaxBuffers to 65535 or MaxLocksPerFile?

Jim.
mlcktmguyAuthor Commented:
Thanks for all of the excellent feedback and suggestion.  Unfortunately the Acess 2013 upgrade is not my primary project and I don't have a lot of time to spend on it now.

Gustav- - The comparison between the Access 2013 execution and the Access 2010 execution was an exact parallel for 50 of the 50,00 records in the full production run..  Meaning that the records processed in each case were exactly the same.  The timings given were for one part of the process for one record.

Just a reminder that this is a batch process that in production would process close to 50,00 records.  The overall time difference between 2010 and 2013 processing all of the records was over 6 hours.  The seemingly small execution difference processing one record really add up.

For the blocks of code shown the times were 2 - 3 times greater in 2013 than 2010, for every record processed.  The actual times were in increments of the 'Timer'  function that you suggested in another EE question I posted.  I cannot get more granular than that.

Jim Dettman, Yes I have set the max buffers to 65535 and max locks up to 200,000.

Thanks again for all of your help.
Gustav BrockCIOCommented:
Did you try to replace the  DoCmd.RunSQL updateString with updating a recordset you open before the loop and close when finished?

/gustav
mlcktmguyAuthor Commented:
Not yet
Gustav BrockCIOCommented:
OK.
Beer time here.

/gustav
mlcktmguyAuthor Commented:
Haven't gotten a chance yet.  Will keep you posted.
mlcktmguyAuthor Commented:
My apologies to the experts.  I didn't even realize this question was still open.  Our priorities sometimes change rapidly in the office and this project has been on the back burner for quite a while.
The issue was never investigated any more than my above posts.  I thanks all experts for contributing and will most certainly be re-visiting this in the future.  In the meantime points have been distributed to show appreciating for your interest in my situation.

Thanks to all.
Monk RobinsonCommented:
I am a long-time Microsoft Access programmer starting with Access 2.0 and all versions higher. I have seen many times where the same query will run quickly with one version of MS Access and slowly with another version. I would step through the code on the Access 2013 system and observe how long queries are taking to execute. You will probably find 1 or 2 queries that are running super slowly now. All you have to do is change their SQL statements to something different that execute well in both Access 2010 and Access 2013. That should solve the problem. I doubt the issue has anything to do with hardware. The different versions of MS Access have differences in how they process queries, and you need to redesign the offending queries so they run well in both contexts. I'm guessing there's only 1 or 2 slow queries. You just need to find them.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.