Access 2013 Slow Performance

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

/gustav

I would have to purchase another license to do that.  I was hoping someone would have insight or experience wit this problem.

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.

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

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

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?

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

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.

I'm putting together some timing test, should have results soon.

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

Select allOpen 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

Select allOpen in new window

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

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.

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

Two big bottlenecks are the code I put in the post.

I recommend using dbEngine(0)(0).Execute or dbEngine(0)(0).Querydefs("nameofquery").Execute rather than DoCmd.RunSQL

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

Have you tried setting MaxBuffers to 65535 or MaxLocksPerFile?

Jim.

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.

Did you try to replace the  DoCmd.RunSQL updateString with updating a recordset you open before the loop and close when finished?

/gustav

Not yet

OK.
Beer time here.

/gustav

Haven't gotten a chance yet.  Will keep you posted.

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.

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.