Access 2010 very slow (20-30 times slower) compared to same application in Access 2002 - is this expected

Question

Hi

I have a large Access 2002 application that runs fine on WinXP, and except for a few annoying variations, like not being able to change the printer in a report at runtime, runs at the same speed on Windows 7 64 bit. On attempting to use the paid Microsoft support to get help on some of these small issues on Window 7 they said they could not open a ticket on a deprecated application, ie Access 2002 (the problem is really with Win7). Their suggestion is to upgrade to 2010 and then ask the qiestions.

I have done this but the upgraded application runs extremely slowly, too slow to deploy to existing users who have an expectation of a certain speed. An example is a versatile find that uses tables of indexed words, around 1,000,000 entries) to locate records containing any of these words in any order. The speed is <1 sec for single word lookup in Access 2002 - the same search in the .accdb 2010 application, with the exact same code, is 30-40 seconds!!!!  Every piece of code that depends on data access runs sluggishly, in most cases below any standard acceptable to end-users. In effect the apppilcation that runs perfectly in Access 2002 is unusable in 2010 on either WinXP or Win7!!

Has anyone got any idea why this could be happening? The access 2002 uses the Jet database engine for returning records. 2010 uses ACE. I am presuming that data access code such as

Dim rst as Dao.Recordset       Set rst = currentDB.openrecordset("Select * From tblStockInner Join etc etc..") should work without changes as it has for many years in 2002. 

Is Microsoft aware of this huge speed reduction in data access between 2002 and 2010? Is there anything developers can do to get around it? Do upgraded applications need an entire rewrite of all data access routines? Am I missing something or has the Access team dropped the ball on this one?  I expect some changes from one version to another but this is extreme and in the wrong direction - surely the goal is the ACE engine is faster, or at least the same as Jet.  As the Jet engine is no longer supported (since 2006), and recent changes in dual core processors have caused serious issues with searches using Jet on Vista and windows 7, at some stage, perhaps on Windows 8,  Jet will become completely unusable with no prospect of fixes- however it looks like ACE is currently unusable in code that run perfectly under 2002. 

Any insights greatly appreciated.

 

Answer 1

Yes, they are aware of this issue. Like this one.

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/86e59bc0-3524-45be-89d0-3528cfea842b

 

Answer 2

This is not the same problem dealt with in the link you provide. The issue I have is all on WinXP. Several queries run 10-20 slower, on the same machine running WinXP, when converted to ACCDB under Access 2010 vs .mdb in Access 2002.  The only difference is the application has been converted to run in 2010.

The problem in the link has to do with an issues I am familiar with and most likely is due to how Jet and ACE handle multicore processors. I have had the problem in Vista and received a hot fix 18 months ago for it which was recently undone due to other problems it caused.  The symptoms were sometimes the same query takes a sunstantially longer, but fixed, time to run. On running again it is fast.

The problem I am having is there is a very consistent response time, about 10-20 times slower on Access 2010 than the same query on 2002 on the same WinXp machine.

And trying to diagnose the problem is further complicated and stretched out by the Installer running everytime I change between versions of Access - this problem has been around for 10 years - is it not time it was fixed - Sagekey, a small Canadian company,  has a fix for it- why can't Microsoft with all its resources?

Answer 3

Hi Mgnagle,

We are doing the research about your problem. There might be some delay about the response. Appreciate your patience.

Best Regards,

---

Bruce Song [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

Hi mgnagle,

I'll start off my response by noting that performance issues like these are challenging to troubleshoot under any circumstances, and much more so via the forums.  I'm glad that you looked at opening a support case in the first place, and do want to throw the idea out there again now that you're able to reproduce the problem with Access 2010.  At the same time, I'll be happy to roll up my sleeves and see what we can do here. 

I know that typically the performance issues in AccessVandal's link seem to be related to the operating system, and that they're not usually associated with Windows XP.  I can tell you though that I have seen performance cases solved by adjusting MaxBufferSize and/or MaxLocksPerFile on XP machines as well.  Have you tried adjusting either of these settings?  Assuming you're talking about using Access 2007 or 2010 on a 32-bit machine, you'd want to make changes to those values in this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE

Typically we've set MaxBufferSize to 50K and MaxLocksPerFile to 1M in testing this angle. 

Out of curiosity, are you able to save your new ACCDB file back to the MDB format, and if so, do you notice any difference in behavior when that MDB is running with Access 2010?

Beyond that, we're probably looking at the shotgun approach.  Do you see any change in behavior if you make any of these changes:

1. Set your Default record locking setting to Edited record, then set the Open databases by using record-level locking to no
2. We've had some luck with perf issues disabling Oplocks:  http://support.microsoft.com/kb/296264
3. If you're opening the database from a file share, can you check on whether you see better performance when you bring that file local?
4. Turning off the Name Autocorrect options has been another go-to performance troubleshooting step that's worked well for us in the past
5. Have we tried running a compact and repair over the database?  Sorry--that's one of those questions that I have to ask . . . 

Kirk Beller
Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 5

I would bet this is the main culprit:

"If you're opening the database from a file share, can you check on whether you see better performance when you bring that file local?"

I witnessed this on 2007 as well. Something really changed with the ACE engine with regards to performance on a network.

I wonder, are there any sysinternals programs that could monitor network traffic as Access 2010 is running ?

 

Answer 6

HI Kirk

Thanks for the suggestions - The problem is now fixed, by accident. As part of preparing a cut down 2010 project for MS support, with 2 linked tables and 1 query, I managed to reproduce the slow behaviour. Great!! As one of the tables had >million recs I decided to Compact both the backend, and the frontend before sending. On testing again the problem was gone!!!! 

I have known all about /decompile and compact and have done it for the past 10 years before deploying anything to customers. Without it the customers experienced ramdom and weird problems - a well known issue. I am surprised that a simple 2010 project, developed and run on the same computer now requires a Compact (this now includes the /decompile as part of the compact) to run correctly. 

A simple solution but it does not make sense. As developers we now have to wonder what other trivial action might make things go significantly faster. There are enough other gotchas in 2010 without this type of hidden effect.

Answer 7

How is that research going?  Anything to report?

Answer 8

How is that research going?  Anything to report?

Yes, and are you using the latest and greatest release of Access ?

That would be 14.0.6112.5000

 

Answer 9

mgnagle,

Not sure about the backend database file, what version was it anyway?

Maybe, when you compact the BE with Access2010, that might help. In fact, 2010 when compacting reduce the file size even more compared with 2002.

I use 2000 BE, when compacted with 2010 there is a little improvement in speed. But my BE file size is too small to verify.

Answer 10

While I am despising the Installer running every time I start Access, you can look to see of 'Name AutoCorrect Option' (File>Options>Current Database) are turned off. This feature is used during development, to cascade updates changes of field and table names throughout the database automatically where subsequent objects depend on them (excluding Macros and Code). By default (when a new database is created), this feature is activated. There was little drag in the older versions, but since 2007, there can be a significant drag factor, which grows tremendously with larger databases. When the feature is activated, Access is tracking and logging new implementations and updates. Turn the features off in your run-time copy and you should experience a significant increase in speed.

Also, Windows Update and Anti-virus Programs allocate a lot of resources. Since the implementation of XML based applications, AV's take longer to scan them. Place the database in a trusted server location that does not get scanned as frequently. Set Windows Update to run once a week, rather than every day.