none
Microsoft Access and Windows 7 Issue

    Question

  • Issue

                    Programs (using MS Access 2003 databases as the backend) that have been running fine for the past 8+ years using XP, now having sporadic slowness when using Win7.  The slowness comes when executing certain SQL statements.  One statement that never took longer than six seconds to execute now takes up to an hour to execute.  Another that might have taken half a second is now taking one minute.  The other strange thing is that the same statements do not always take longer.  The statement that might take an hour usually does but the one that might take 1 minute is executes fast half the time.

    Failed Resolutions

                    Some other found that changing a group policy has helped them but this did nothing for me.  I also think this fixing a network issue that does not pertain to me as all my programs and data files reside locally on the computers.

                    I have found others with similar issues and even found a message on Microsoft’s site stating that this is a known issue for all Access databases and that a patch for Access 2010 will be released.  The site said that this would fix ACE but not JET.  I have purchased Access 2010 and ported two of my databases over.  When testing with this engine I did notice an improvement but this does not appear to be a fix.  The statement that took one hour went down to four minutes but still much longer that the six seconds on a slower XP box.  Also Microsoft does not recommend using this as a replacement for Jet.

    This comes from their website:

    The Access Database Engine 2010 Redistributable is not intended:

    1. As a general replacement for Jet      (If you need a general replacement for Jet you should use SQL      Server Express Edition).

    I have tried SQL Server Express and it works fine but I have over 1000 computers that are scattered around the Mid United States and my bosses do not accept this as a solution.

    Here is one of many statements that the execution speed has greatly been reduced:

    DELETE FROM CompetitivePricingSigns WHERE StoreID=999 AND IsPrinted=FALSE AND

    ProductID IN (SELECT ProductID FROM Pricing WHERE StoreID=999 AND RetailPrice IS NULL);

    On a single core Celeron with 2 Gig of RAM and running Win XP: takes less than ½ second.

    On a dual core i3 with 4 Gig of RAM and running Win 7: can take one hour.

    There are less than 800 records in the CompetitivePricingSigns table and I do have my tables indexed.

    Tuesday, June 05, 2012 3:35 PM

Answers

  • Hi Shane,

    This a quick note to let you know that I am trying to involve someone familiar with this topic to look at this issue.

    Sincerely,

    Max Meng
    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please contact tnmff@microsoft.com.

    Wednesday, June 06, 2012 4:34 AM
  • Shane,

    I am sorry to hear about the performance issue you are running into.  Here are the things I would recommend checking.

    1) Verify you have installed service pack 1 for Access 2010 and installed the latest updates..
    Description of Office 2010 SP1
    http://support.microsoft.com/kb/2460049
    Office 2010 cumulative update for February 2012
    http://support.microsoft.com/kb/2658222

    2) Verify there are no 3rd party programs on the machine causing a conflict with Access.  The easiest way to do this is to take the following steps:
    A) Click on the Windows Start button, in the Search box type msconfig and then hit enter.
    B) This should open up the System Configuration Utility. Make the following adjustments within each tab.
    Boot Tab:
                - Check the boot option "Base Video"
    Services Tab:
               - Check the option 'Hide All Microsoft Services'
                - Click on button 'Disable All'
    Startup Tab:
                - Click the button 'Disable All'
    C) Click Ok and restart the machine.
    D) When you are done testing you can set this back to a normal startup.  Click on the Windows Start button, in the Search box type msconfig and then hit enter.
    E) This should open up the System Configuration Utility. Make the following adjustments within each tab.
    General Tab:
                - Choose Normal Startup
    F) Click OK and restart the machine.

    3) Lastly you can check and change the value in the following registry keys.  If you are using Windows 7 32 bit then you can take out the WOW6432Node in the keys below.

    For Access 2003 and lower, it will be a Jet key
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0
    For Access 2010, it will be an ACE key
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

    Default Values:
    MaxBufferSize 0
    MaxLocksPerFile 9500
    New values:
    MaxBufferSize 20000
    MaxLocksPerFile 500000

    Let us know if any of those suggestions help.

    • Marked as answer by Shane White Thursday, June 07, 2012 9:30 PM
    Wednesday, June 06, 2012 7:45 PM
  • Thanks :)
    Wednesday, June 06, 2012 3:21 PM
  • What used to be a 4 minute minimum wait time now appears to be about 20 seconds based on about 6 different batches that have been sent in this morning after we tweaked some Windows registry settings. Are these setting the max we can do or is there more speed to be had here?

    Default Values:
    MaxBufferSize 0
    MaxLocksPerFile 9500
    New values:
    MaxBufferSize 20000
    MaxLocksPerFile 500000

    • Marked as answer by Shane White Thursday, June 07, 2012 9:32 PM
    Thursday, June 07, 2012 9:32 PM
  • Hi Shane,

    I am glad to hear the the recommendations seem to have improved the performance.  We usually use the new values I gave you as a good starting point.  You can tweak them up or down to try to optimize them for your machine.  Unfortunately I don't have a recommendation other that tweak the settings and test to see if it increases the speed.  The article below has more information on the key.

    How To Determine Jet Memory Usage with DAO MaxBufferSize
    http://support.microsoft.com/kb/187872/EN-US

    Let us know if you have any further questions.

    • Marked as answer by Shane White Tuesday, June 26, 2012 5:34 PM
    Wednesday, June 13, 2012 6:52 PM

All replies

  • Hi Shane,

    This a quick note to let you know that I am trying to involve someone familiar with this topic to look at this issue.

    Sincerely,

    Max Meng
    Forum Support


    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please contact tnmff@microsoft.com.

    Wednesday, June 06, 2012 4:34 AM
  • Thanks :)
    Wednesday, June 06, 2012 3:21 PM
  • Shane,

    I am sorry to hear about the performance issue you are running into.  Here are the things I would recommend checking.

    1) Verify you have installed service pack 1 for Access 2010 and installed the latest updates..
    Description of Office 2010 SP1
    http://support.microsoft.com/kb/2460049
    Office 2010 cumulative update for February 2012
    http://support.microsoft.com/kb/2658222

    2) Verify there are no 3rd party programs on the machine causing a conflict with Access.  The easiest way to do this is to take the following steps:
    A) Click on the Windows Start button, in the Search box type msconfig and then hit enter.
    B) This should open up the System Configuration Utility. Make the following adjustments within each tab.
    Boot Tab:
                - Check the boot option "Base Video"
    Services Tab:
               - Check the option 'Hide All Microsoft Services'
                - Click on button 'Disable All'
    Startup Tab:
                - Click the button 'Disable All'
    C) Click Ok and restart the machine.
    D) When you are done testing you can set this back to a normal startup.  Click on the Windows Start button, in the Search box type msconfig and then hit enter.
    E) This should open up the System Configuration Utility. Make the following adjustments within each tab.
    General Tab:
                - Choose Normal Startup
    F) Click OK and restart the machine.

    3) Lastly you can check and change the value in the following registry keys.  If you are using Windows 7 32 bit then you can take out the WOW6432Node in the keys below.

    For Access 2003 and lower, it will be a Jet key
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0
    For Access 2010, it will be an ACE key
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

    Default Values:
    MaxBufferSize 0
    MaxLocksPerFile 9500
    New values:
    MaxBufferSize 20000
    MaxLocksPerFile 500000

    Let us know if any of those suggestions help.

    • Marked as answer by Shane White Thursday, June 07, 2012 9:30 PM
    Wednesday, June 06, 2012 7:45 PM
  • What used to be a 4 minute minimum wait time now appears to be about 20 seconds based on about 6 different batches that have been sent in this morning after we tweaked some Windows registry settings. Are these setting the max we can do or is there more speed to be had here?

    Default Values:
    MaxBufferSize 0
    MaxLocksPerFile 9500
    New values:
    MaxBufferSize 20000
    MaxLocksPerFile 500000

    • Marked as answer by Shane White Thursday, June 07, 2012 9:32 PM
    Thursday, June 07, 2012 9:32 PM
  • Hi Shane,

    I am glad to hear the the recommendations seem to have improved the performance.  We usually use the new values I gave you as a good starting point.  You can tweak them up or down to try to optimize them for your machine.  Unfortunately I don't have a recommendation other that tweak the settings and test to see if it increases the speed.  The article below has more information on the key.

    How To Determine Jet Memory Usage with DAO MaxBufferSize
    http://support.microsoft.com/kb/187872/EN-US

    Let us know if you have any further questions.

    • Marked as answer by Shane White Tuesday, June 26, 2012 5:34 PM
    Wednesday, June 13, 2012 6:52 PM
  • Hi!

    I have the problem reported in this thread, slow Access 2010 SP1 (64Bit) on Win 7 (64-bit). 

    I've also installed KB: 2553116, which improve startup performance.

    So I try to do the registry key modification and everything goes ok, for the first 4/5 queries. After that Access becomes even slower.. I've tried different MaxBufferSize values, from 20k to 300k, but sooner or later it becomes slow!

    Have you got any suggestion?

    Marco


    • Edited by mserioli Friday, May 17, 2013 2:48 PM
    Friday, May 17, 2013 2:35 PM