Very slow Access 2002 query with Windows 7

Question

I need help troubleshooting consistently slow queries in an Access 2002 database with Windows 7.  I’m running a temporary query in a local copy of a back end database with only tables. The identical query will take just 2-3 seconds with Windows XP but 30-60 seconds with Windows 7. Multiple Windows 7 workstations are all slow and all Windows XP machines run the query quickly.  I even used JetShowPlan on both workstations to compare and the plans are identical. Two tables are involved with rushmore used on the first and a full scan on the second table of about 150,000 records. Even in XP Mode on the Windows 7 machine the query still takes only about 10 seconds. Anyone have any ideas why Access/Jet on Windows 7 can be so slow? Thanks for any ideas.

---

Rick Collard

Answer 1

Sorry I don't have a real answer, but I would suggest you try again using Access 2010. The runtime is a free download.

---

-Tom. Microsoft Access MVP

Answer 2

I opened the Access 2002 MDB with Access 2010 and repeatedly ran the same temporary query and I get similar results.  The query requires a table scan of 150,000 records and many times it will run in just a few seconds but other times it will take over 30 seconds.  Since the slowness appears with both Access 2002 and 2010 I'm beginning to suspect the problem may be with Jet on Windows 7.

---

Rick Collard

Answer 3

Hello Rick Collard,

Welcome to our forum.

 

Many people have met the similar issue as yours. Luckily someone has found the solution for this issue.

 

I think this thread will be helpful for you.

http://social.technet.microsoft.com/Forums/en-US/office2007deploymentcompatibility/thread/88542372-0fcb-4521-87ec-e2ab72e688dd/ 

 

If the thread can’t help solve your issue or your issue persists please feel free to let me know.

 

Best Regards,

---

Macy Dong [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Setting MaxBufferSize to 50000 offers some performance improvement but unfortunately it does not solve the problem.  I have opened a support request with Microsoft.  The support engineer has duplicated the problem with a subset of the database and is still researching a solution.

---

Rick Collard

Answer 5

I would LOVE to hear how that support case gets resolved. Please keep us posted.

---

-Tom. Microsoft Access MVP

Answer 6

I don't have a solution yet but here is the latest.  A few weeks ago the support case was assumed by the Microsoft Access Escalation team and assigned to a Senior Support Escalation Engineer.  Yesterday, I received the following update from the Escalation Engineer:

We have identified an issue in the Access source code that is contributing to the cause of the intermittent query performance problem you see in Microsoft Access when using Windows 7 machines.

Due to efficiencies built into the newer operating systems, as well as efficiencies in many newer machines,  resource management is vastly different from the time when the database engine code for Microsoft Access was developed. The Microsoft Jet Database Engine code, on which the Access 2007 and Access 2010 Database Engine was based, was developed to work around resource limitations in older operating systems. Due to the recent changes in resource management, we are finding that the older approach to resource management is resulting in the intermittent query performance issues.  Our developers are currently exploring an approach to resolving the issue in the source code for the Access Database Engine.

Since I have duplicated the problem with an mdb in both Access 2002 and 2010 and an accdb in Access 2010, this problem occurs with both Jet 4.0 and ACE 14.0 on Windows 7.  I answered a series of questions about our application to help the Access developers decide the best approach for a solution.  I don't know where this will lead but I thought others should be aware of these performance issues with Access on Windows 7.  I'll post when I have more news.

---

Rick Collard

Answer 7

Still no solution but Microsoft is working on a fix.  I just received the following case update from Microsoft's Senior Support Escalation Engineer:

I hope you did not think I forgot about you.  I just want to let you know that a fix for this issue is scheduled to be available late August/early September.  I will update you if anything changes with regard to the timeframe. Thanks for your continued patience!

I'll post when I learn more about the fix.

 

---

Rick Collard

Answer 8

Rick,

Thanks for the feedback. I hope we're not to blame due to poor design and coding.

Answer 9

I also hope, that this upcoming fix will also fix the x64 related problem. The same query on the same database takes 50-100 times(!) longer on Windos XP Prof. x64 and Windows 7 x64 on a multicore system with at least 4 CPU-cores. On a Core 2 Duo no problems, on Core i5 or Core i7 reproducible. Changing the cpu affinity mask of the process, to run on core-count - 1 cores also solves the problem. Windows 7 x86 is not affected, runs on any hardware fast. Is this issue also known?

Answer 10

Rick,

Has Microsoft provided you with a KB article number yet?  There are several users at my office that are experiencing this issue.

Answer 11

No, I have not been given a KB article number.  Maybe that will come after they provide a fix.

---

Rick Collard

Answer 12

Rick, are you also experiencing this problem only on x64 Windows 7 and Core-i-series CPUs? If yes, does it help changing the CPU-affinity of the process?

Answer 13

Nagy,

We are currently experiencing the issue only on our x64 Windows 7 SP1 Intel i7 machines.  We only use Intel so I don't know if the issue is present on the AMD chipset.  Changing the affinity did not seem to help much (in my case).

Answer 14

Nagy,

I have only tested and duplicated the problem on 32-bit Windows 7 on an Intel Core2 Duo P8700 CPU.

---

Rick Collard

Answer 15

Rick, I'm a bit confused now... Can you reproduce the problem only on x64 Windows 7 or also on x86?

Answer 16

Hi Rick. I know it isn't much help but I can +1 your problem. I discovered exactly the same problem with Access both 2003 and 2010 on Windows 7. When firing queries with large datasets the whole program would cripple to a halt. Reminiscent of the Vista (and Windows 7) file copying debacle from a few years earlier. I tried lots of different things changing loads of different network and registry settings. One of those nightmare sessions where you try everything for days but make no progress. In the end I gave up and moved to SQL Server. I was actually going through the upgrade to SQL Server anyway and I found that large queries in a client server situation didn't have the same issues - for obvious reasons.

 

Basically any large queries on Windows 7 would give me massive performance issues but would be fine on XP. As an aside I also note no network performance increases due to using Windows 7 over XP. So whatever extra network 'enhancements' MS has added to Windows 7 all it does is screw things up for Access and who knows what else.

 

I'm amazed (and pleased) that MS have flagged this as a problem and are now starting to fix it. I wonder what else it will speed up as I cannot think that Access is the only system affected. Also - I'm glad it's not just me that has had this problem.

---

--- Sam

Answer 17

Nagy,

I have only tested and duplicated the problem on 32-bit Windows 7.

---

Rick Collard

Answer 18

I can 100% confirm the same problem on 64 bit Windows 7 too.

---

--- Sam

Answer 19

A solution may be near.  I received a private release of a hotfix, which unfortunately would not install on my system so I'm waiting for a resolution.  If the hotfix resolves my issue, I've been told a public release of the hotfix will follow.  I'll post here when I have more news.

---

Rick Collard

Answer 20

Mr. Collard

    There are a lot of reason for this behavior.  How many tables are involved?  How often do you defrag?  How often do you compress you databases?

Microsoft creates a file for each index.  Do not show a field as an index field unless you are going to use it as an index.  Using the primary key is always best whenever possible.  If there are a lot of secondary keys with the same info then it will run slower.  The way Microsoft keeps track of these things can either be a huge asset or detriment.  

I would also recommend going to an Access 2003 upgrade, if you can get one.  If you do make sure you do your Microsoft Office updates.  Amazing as it seems some of the older accesses do work better than the new ones.  It is a matter of how many issues that Microsoft has to deal with.  In other words the squeaky wheel gets the grease. 

In many cases I have found both Access 2003 and 2007 running faster and slower depending on what you want to do.  Just try to keep it as simple as you can.

Good Luck

 

Tony 

Answer 21

The ONLY reason for these slow queries on Windows 7 is a problem Microsoft has acknowledged.  Timing the same query in the same Access 2002 database shows consistently fast performance on Windows XP and erratic and slow performance on Windows 7.  The slow performance on Windows 7 occurs with both Access 2002 and Access 2010 so the problem exists with both Jet 4.0 and ACE 14.0 on Windows 7.

---

Rick Collard

Answer 22

Microsoft had originally sent a 64-bit version of the private hotfix.  When they sent the 32-bit version I need, I installed the hotfix and it successfully solved the slow query problem for Access 2010 on Windows 7.  The fix was for ACE 14.0 so I've asked if they will also supply a fix for Jet 4.0.  If they do, that should solve the problem for Access 2002/2003 on Windows 7.  I'll post again when I learn more.

---

Rick Collard

Answer 23

Rick, will Microsoft allow you to share the private fix?  I'm curious to test it on a couple of workstations here while waiting for Microsoft to publish the hotfix.

Answer 24

Rick, will Microsoft allow you to share the private fix? I'm curious to test it on a couple of workstations here while waiting for Microsoft to publish the hotfix.

Answer 25

Rick, will Microsoft allow you to share the private fix? I'm curious to test it on a couple of workstations here while waiting for Microsoft to publish the hotfix.

I don't know Microsoft's policy on sharing preliminary private fixes.  Given the secured workspace they provide for sending and receiving files for the support case, I would guess they'd prefer the files not be shared.

---

Rick Collard

Answer 26

As I was afraid, anyone experiencing this slow performance problem with Access 2002/2003 (i.e. Jet 4.0) on Windows 7 is out of luck.  The ACE 14.0 private hotfix solved the problem for Access 2010 so I asked if a Jet 4.0 fix would also be available.  Here is the response I received today from Microsoft's Senior Support Escalation Engineer:

I am pleased to hear that the private release solves the slow query performance with Access 2010.  I will pass this feedback along to the developers so they can work on a final release of the fix, scheduled for late August, early September. 

Unfortunately, there will be no fix provided for Jet 4.0 or as it is out of main stream support.

We are more than a year away from releasing an Access 2010 version of our commercial product.  Until then, I will have to warn our users about possible performance issues on Windows 7.

---

Rick Collard

Answer 27

Well at least it is a fix - even if it does just support Access 2010.

---

--- Sam

Answer 28

Good feedback Rick. Thank you.

At least we know now Jet 4.0 will have or might have issues in newer OS now and in the future.

As a side note, I'm running Access2000 with Windows7 (32/32-bit). I have no issues with slow queries but I do have problem with Report Filter with Dates. It always is in US format instead of UK. The rest of my Dates queries and reports are working fine. Regional settings in Win7 is set to UK.

Answer 29

Hi Rick,

I too have this same Problem.  Office 2007 sp2 running on an HP Z800 W7-64b (2x2.4ghz/24gbmem/2gbgfx/2x300gb drv).

Have tried:

• several of the registry adjustments suggested --didn't help
• moved database local to the computer -didn't help
• ran in administror mode & account -didn't help
• database works Fine on a XP-Pro w/Office 2007-sp2 installed.

I look forward to hearing any new News that you care to Post.  Hope MS release this Hotfix Soon!!!

Thanks for taking the time to Write this up - It was a big help!!

Answer 30

danab252 wrote:

Hi Rick,

I too have this same Problem.  Office 2007 sp2 running on an HP Z800 W7-64b (2x2.4ghz/24gbmem/2gbgfx/2x300gb drv).

Have tried:

* several of the registry adjustments suggested --didn't help * moved database local to the computer -didn't help * ran in administror mode & account -didn't help * database works Fine on a XP-Pro w/Office 2007-sp2 installed.

Have you seen this KB article?
http://support.microsoft.com/kb/2397391

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

Answer 31

Hi Tony,

Thanks for the pointer - I had found that one earlier and tried it - did not help.  I think the HP ProtectTools Security Manager is Normally loaded on Laptops - my problem is on Workstation.  I did a test of 2007 Access in the Virtual XP Mode window - There is works 'normally'. 

 

To give you an idea how Slow a query in 2007 access/W7 is;

·     I started a search for part numbers with 'busbar' in their description...

·        on W7 - Database is LOCAL to WS and upgraded to Office 2007

·     I then opened an XP Mode window

·     Opened the 2007 Access DB to the 'normal' database located on fileserver.

·     enter the same Part search

·     counted to 10 - 19 Parts found

·     Closed DB

·     Closed XP Mode window

·     Waited about 2 minutes... same 19 parts found....  Wonderful!

I will wait till my MS Rep can get me in line for the Hot fix or MS releases it.  Till then I will teach the Users how to Use XP Mode.

-db 

Answer 32

db,

I can certainly believe your results on Windows 7.  In my timing tests, I found a query that consistently took just two seconds on a slow Windows XP machine, sometimes took over 60 seconds on a much faster Windows 7 system.  You can see the results of my timing tests in another thread at http://www.utteraccess.com/forum/Slow-Queries-Windows-7-t1968457.html.

 

---

Rick Collard

Answer 33

Microsoft has released the hotfix, which solves the slow query problem on Access 2010.  As I mentioned earlier, the hotfix is for ACE 14 only.  The same problem exists with Jet 4 on Windows 7 but Microsoft is not releasing a fix so Access 2002/2003 will continue to encounter slow queries on Windows 7.  Here's a link to the Access 2010 hotfix:

http://support.microsoft.com/kb/2553116

 

---

Rick Collard

Answer 34

RCollard wrote:

Microsoft has released the hotfix, which solves the slow query problem on Access 2010.  As I mentioned earlier, the hotfix is for ACE 14 only.  The same problem exists with Jet 4 on Windows 7 but Microsoft is not releasing a fix so Access 2002/2003 will continue to encounter slow queries on Windows 7.  Here's a link to the Access 2010 hotfix:

Thanks for posting the hotfix availability.

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

Answer 35

Will this hotfix end up in 2010 SP2?

---

--- Sam

Answer 36

-Sam- wrote:

Will this hotfix end up in 2010 SP2?

They almost always do.   I can't think of a case where they haven't but
then I haven't paid that much attention.

Tony

---

Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

Answer 37

Thanks for the updates from MS. I cannot believe they are not going to release a fix for the older jet applications. We have no plans (or budget) for upgrading dbs or rewriting a lot of legacy code that serves our needs perfectly now. So what are our options? New hardware is shipped with Win 7! Are we supposted to always roll this back to XP, run in Vm, what?

There has to be a better answer.

Ddresh

Answer 38

Ddresh,

I am equally disappointed that Microsoft is apparently ignoring the Jet 4.0 problem on Windows 7.  The official reply I received in August was this:

Unfortunately, there will be no fix provided for Jet 4.0 or as it is out of main stream support. 

Here are links to the product lifecycle for your reference:

http://support.microsoft.com/lifecycle/?p1=1266

http://support.microsoft.com/lifecycle/?p1=2488

The links above are the product lifecycles for Access 2002 and 2003.  But isn't Jet 4.0 part of Windows?  The Microsoft article How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine shows the Jet 4.0 versions by operating system, which stops short of the msjet40.dll version 4.00.9756.0 found on my Windows 7 system.  To me, that suggests Jet 4.0 is part of Windows and it  has been updated many times since Access 2002 and 2003 were released and last updated.  The slow query problem is a Jet 4.0 problem on Windows 7 not an Access 2002/2003 problem.

---

Rick Collard

Answer 39

Rick,

Thanks for the info - at least I am not alone on this problem, but I am really at a loss as to how to proceed. My clients just upgraded hardware with win 7.0 installed and I transferred legacy software and dbs and now this. I'm thinking about buying new HDs and doing a clean install of XP and ingnoring the win 7 altogether. I don't know what else to do.

Ddresh

 

Answer 40

Ddresh,

There is no doubt we are not alone.  There must be an enormous number of applications using Jet 4.0 still running out there.  We have hundreds of users running our application and the only solution we see is to move our Access 2002 product to Access 2010.  The move to Access 2010 will take us considerable time and Access 2010 has its own list of problems.  See http://www.utteraccess.com/forum/Upgrade-path-commercial-t1779005.html for a few of the problems I've encountered to date.  The same Jet 4.0 performance problem existed with ACE 14 and Access 2010 on Windows 7 but at least Microsoft has resolved that problem with the hotfix.  If they wanted to, I believe Microsoft could apply the ACE 14 solution to Jet 4.0.

Until we have an Access 2010 version of our product available, we can only caution our users moving to Windows 7 and recommend they run our application in Windows 7's XP Mode virtual machine.  But not all hardware can run XP Mode so that's still not a complete solution.  The best solution is to stick with Windows XP.

 

---

Rick Collard

Answer 41

Rick,

That's the conclusion I came to but my clients are mostly small mom and pop businesses and at their budget Access 2010 and the required coding changes may never come. This software has been running faithfully and error free in some places for almost nine years.

I read the rants over at the link you provided and I can't even imagine the snake pit we would be walking into if we tried going to 2010. SQL would be way overkill for the size of these places. I guess we will be buying some secondary hard drives.

 

Ddresh

Answer 42

What about Access 2007/ACE 12.0??? That is still under mainstream support. Will Microsoft provide a hotfix for Access 2007 on Windows 7?

Answer 43

Imho ms don't give a pice of cake fixing this obvious bug in the ace/jet/(or win7). The hotfix doesn't solve the problem. You must have an office 2010 product licensed to install that hotfix. What about all the applications using access databases without office?!

Answer 44

Hi everyone

I am running Access 2003 under Windows 7 64-bit and here is what worked for me.

First thing I tried:  Searched through registry for all "MaxBufferSize" entries relating to data sources/handling (so ACE, JET 2.0, JET 3.0, JET 4.0 etc...), then I changed the value of each of these to "50000" (decimal)

After this the processing DID speed up significantly :)

Second thing I tried:  Went to the "MSACCESS.EXE" in the "C:\Program Files (x86)\Microsoft Office\OFFICE11" folder , right clicked and went to the "compatibility" tab, where I set it to run as "Windows XP SP3", then I also ticked the "run as administrator" box.

After a fresh reboot I tried the same processing procedure and the time taken reduced by 50% :) :)

I may not have needed to change ALL the MaxBufferSize entries to 50000, perhaps it was just the Jet 4.0 that did it?  One thing is for sure, I work in a high pressure environment with little or no time for anything other than production work...so now it is working quickly I am not looking back!! lol

Hope this helps some of you out, and good luck to the rest of you!

Sam Kelly

Answer 45

My guess would be the compatibility setting, sine Microsoft does not list Office 2002 (XP) as compatible with Windows 7.

http://www.microsoft.com/windows/compatibility/windows-7/en-us/Search.aspx?type=Software&s=Microsoft%20Office%20XP%20Professional%202002

Answer 46

Sam,

I have tried all of these changes before with no performance improvement with Access 2002 on 32-bit Windows 7.  Since Access 2003 also uses Jet 4.0 I wonder if your 50% improvement was a one time observation.  If you repeatedly time the opening of a single query you will see very erratic performance with Windows 7 but consistently fast times in Windows XP or in XP Mode of Windows 7.  I use the code below with a query that requires a scan (i.e. a criterion on a non-indexed field) on a large table to test.  I am curious.  If you do the same do you get consistent times for all 10 iterations?  I just ran mine again and the times ranges from a low of 4 seconds to a high of 79 seconds. 

Public Sub TimeIt()

    Dim db As DAO.Database, rs As DAO.Recordset
    Dim nOpen As Long, nClose As Long
    Dim i As Long
    
    Set db = DBEngine.Workspaces(0).Databases(0)
    For i = 1 To 10
        nOpen = Timer
        Set rs = db.OpenRecordset("qryTest", dbOpenDynaset)
        rs.Close
        nClose = Timer
        Debug.Print i & ": " & nClose - nOpen
        DoEvents
    Next i
    
End Sub

---

Rick Collard

Answer 47

Just ran into this problem and am very appreciative for this thread.  Machine in question was a 64Bit Windows 7 i5.  It is running Office 2003 (Customer has an older database that was made originally in 97, there are a few bugs preventing a 2007 or 2010 upgrade at this point)

I can say that the queries were / are very slow.  15 second queries are taking one to two minutes.  I had tried the registry fixes suggested on this and another forum with no luck.  Running the database in Windows XP Compatibility mode does fix the problem or make for much faster queries.  This fix will do for our situation, at least until we can correct some things and upgrade the database.