Access 2007 Design view performance on Vista vs XP

[cpf_profiles]

Hi

I tried a search but found nought. I have a dual core, 2GB Vista machine and
a single core XP machine both connected to the same 2003 file server. My
application is a sales order processing package ported from Access 97 to
Access 2007. Performance seems broadly comparable (and acceptable) in terms
of create/read/update/delete operations across the two machines on the same
database.

However, when making changes to form or report design, particularly those
with subforms/reports the XP machine opens the forms almost instantaneously,
while the Vista machine pauses for anything up to 30 seconds, displaying "Not
Responding" in the window title. It does open the form, eventually, but it
makes for slow progress.

Note that I am copying the front end accdb file to my local hard disk to
make changes. My back end DBs are also access 2007, and I have set the
subdatasheet property on all tables to "none".

Any thoughts on the cause of this would be gratefully received.

 

[Allen Browne]

I don't have a solution for you, but let's explore this further.

There are specific cases where Access is very slow in design view. The worst
are reports where the field names are not available without running a query.
For example, if the report is based on a crosstab, Access has to run the
query to completion to get the field names, unless you specify them in the
Column Headings property of the query.

A2007 on Vista is slower than previous versions + OSs. It seems to be
constantly doing screen updates, so it can take half a second after you
click in a property before you can type there, and if you type in that time,
the entry may go to the wrong place. I found this improved somewhat once
nVidia released better video drivers.

The next issue is the linked tables. To test if this is a network issue, try
copying the back end to your local hard disk, and use the Linked Table
Manager to reconnect. It would be interesting to know if this makes a
significant difference.

For more general suggestions on improving Access performance, you may have
already read Tony Toews' suggestions at:
http://www.granite.ab.ca/access/performancefaq.htm
Things like the length of the path may make a difference.

 

[cpf_profiles]

Thanks for the informative response, Allen.

There are specific cases where Access is very slow in design view. The worst
are reports where the field names are not available without running a query.
For example, if the report is based on a crosstab, Access has to run the
query to completion to get the field names, unless you specify them in the
Column Headings property of the query.

As it happens, the reports/forms in question have the underlying tables
themselves as their datasource so I don't believe that could be a cause. I
was not aware of this (although it makes perfect sense) so thanks for the
Column heading tip.

A2007 on Vista is slower than previous versions + OSs. It seems to be
constantly doing screen updates, so it can take half a second after you
click in a property before you can type there, and if you type in that time,
the entry may go to the wrong place. I found this improved somewhat once
nVidia released better video drivers.

It doesn't seem to be a GUI based delay. A good example is where I hit
design view on a form with a subform. It typically takes 10 seconds or so
before I get control back. When I wish to modify a control in the subform, it
can take about 30 seconds before I get control back. This just doesn't happen
in XP.

The next issue is the linked tables. To test if this is a network issue, try
copying the back end to your local hard disk, and use the Linked Table
Manager to reconnect. It would be interesting to know if this makes a
significant difference.

I'll try that tomorrow. I recall that the delay was there when I did the
port on a standalone machine, but it could do with testing again. I know
Vista has significant changes to the TCPIP stack but I believe they are meant
to improve performance (and - according to all the articles I've read - do)

For more general suggestions on improving Access performance, you may have
already read Tony Toews' suggestions at:
http://www.granite.ab.ca/access/performancefaq.htm
Things like the length of the path may make a difference.

I recently have been living on that site - and yours! Keep up the good work!

As a footnote, a little history. I created this SOP system about 13 years
ago in Access 2.0 as a "teach myself programming" exercise and as a dig-out
for my brother's fledgling steel manufacture business. I upgraded it to
Access 97 in or around 1999 and it has been running spendidly since as the
core of his sales process. The application and infrastructure have scaled
with his business growth - a feat far from my expectation given the budget.
I'm no MS fanboy (quite the opposite, J2EE on big iron Unix being my bread
and butter) but it is very hard to argue with the amount of functionality
that access delivers for the money.

I realise it seems somewhat foolhardy to jump from a stable NT4/Access 97
based application to a comparatively untried Vista/2007 platform but the
company does not have in-house support and therefore needs to suffer change
as infrequently as possible. I must say my experience of Vista and 2007 are
so far very much at odd with the doomsayers - current thread notwithstanding
of course

 

[Allen Browne]

Interesting background.

Presumably you have followed all Tony's advice.

Let us know if copying the back end on the local hard drive (and then
compacting) makes a difference.

 

[cpf_profiles]

Presumably you have followed all Tony's advice.

Most of it. The Virus scanner was a worry - it's AVG and set to scan *.MD*,
but not *.acc* so I'm reasonably confident that it's not the problem.

Let us know if copying the back end on the local hard drive (and then
compacting) makes a difference.

Yes, it does make a significant difference. As such, can we assume that the
problem is with Vista network performance? The file server is a Windows 2003
R2 with all updates applied. The cards are all broadcom GB cards, but running
at 100mbps because the swtich is 100mbps max. THere's a planned upgrade to a
gigabit switch recommended by another software vendor (Sage), but I hadn't
expected any performance increase in Access. The BE is fairly small (20MB
"live" with a 28MB "archive"). In terms of records, 1400 jobs with 13000 job
items, 100000 audit trail items and miscellaneous small reference tables.

I'm a little concerned now - is my design-time performance irritation
actually symptomatic of an overall performance issue in waiting?

 

[Allen Browne]

I don't believe it's a network issue.

After reading your post, I decided to run a test.
Front end = MDB (A2000 format) on Vista, opened in A2007.
Back end = MDB on another Vista machine (100Mbps network.)

It took 4 seconds to open a form that contains:
- 2000 records
- 20 combos
- 7 subforms.
One of those subforms is bound to a 4-table query (including outer joins),
and one of the tables in that query has more than 1,000,000 records. Another
subform is bound to a table with 77,000 records.

There is also code in the form's Current event (setting form caption,
showing/hiding fields, handling unbound controls.)

Loading the form a 2nd time took 3 seconds.

Back end is 1/4 GB (250k MB) after compacting. Other than that, it's a
fairly typical Access app: nothing outlandish going on, but much more than a
wiz/template db.

Hopefully that's an encouragement.

 

[cpf_profiles]

I don't believe it's a network issue.

After reading your post, I decided to run a test.
Front end = MDB (A2000 format) on Vista, opened in A2007.
Back end = MDB on another Vista machine (100Mbps network.)

It took 4 seconds to open a form that contains:
- 2000 records
- 20 combos
- 7 subforms.
One of those subforms is bound to a 4-table query (including outer joins),
and one of the tables in that query has more than 1,000,000 records. Another
subform is bound to a table with 77,000 records.

There is also code in the form's Current event (setting form caption,
showing/hiding fields, handling unbound controls.)

Loading the form a 2nd time took 3 seconds.

Back end is 1/4 GB (250k MB) after compacting. Other than that, it's a
fairly typical Access app: nothing outlandish going on, but much more than a
wiz/template db.

Hopefully that's an encouragement.

It is, but drags me back to square one.

If the problem is not a network one, perhaps it is lock files?
Here's a question - My BE tables are arranged across a number of different
files. Would I be better off having them all in one?

 

[Paul Shapiro]

I don't remember your network specifics from earlier in the thread. There
have been issues with Vista networked to older OS's. Vista's network stack
includes some "optimizations" which apparently work well between Vista
machines, but sometimes perform terribly between Vista and older OS's. I
don't remember the specifics, but it might be worth searching if you can't
find any causes within Access.

You mentioned that your backend tables are in many different files. In that
case you can't have relationships between the tables. When you specify
enforced relationships, Access automatically creates hidden indexes for the
foreign keys. You might look at your data model and see if you need to
specify any additional indexes that Access would otherwise create.

 

[cpf_profiles]

Hi Paul, and thanks for the response.

I don't remember your network specifics from earlier in the thread. There
have been issues with Vista networked to older OS's. Vista's network stack
includes some "optimizations" which apparently work well between Vista
machines, but sometimes perform terribly between Vista and older OS's. I
don't remember the specifics, but it might be worth searching if you can't
find any causes within Access.

I believe this is the variable TCP receive window size. XP, 2003R1 and 2000
had a fixed TCP receive window size, which effectively limited their default
throughput to around 5Mbps. Vista (and, AFAIK 2003R2) use a variable TCP
receive window to auto-tune performance. I'm using Windows 2003R2 & Vista,
so I believe I'm OK. I'll delve further into it to check.

You mentioned that your backend tables are in many different files. In that
case you can't have relationships between the tables. When you specify
enforced relationships, Access automatically creates hidden indexes for the
foreign keys. You might look at your data model and see if you need to
specify any additional indexes that Access would otherwise create.

Where relationships exist between tables, they reside within the same file.
Just to re-iterate, the application performance seems fine, this is only on
entering design view. Do I understand you correctly - you are suggesting that
Access may be creating an index each time I enter design view on a subform
that doesn't have a defined relationship?

 

[cpf_profiles]

I believe this is the variable TCP receive window size. XP, 2003R1 and 2000
had a fixed TCP receive window size, which effectively limited their default
throughput to around 5Mbps. Vista (and, AFAIK 2003R2) use a variable TCP
receive window to auto-tune performance. I'm using Windows 2003R2 & Vista,
so I believe I'm OK. I'll delve further into it to check.

And whad'ya know?!! If I disable autotuning on the TCP stack in vista I no
longer suffer from this problem. I don't know why I believed that 2003R2
supported this feature but it doesn't. Either way, until I upgrade to Windows
server 2008 I won't be enabling autotuning on my Vista client.

Here's a good overview of the background to this setting.

http://blogs.msdn.com/wndp/archive/2007/07/05/receive-window-auto-tuning-on-vista.aspx

I wonder what else this might speed up (e.g. Sage)

Thanks for your help, lads.

 

[DAVID]

I've got an idea that some of the problems might
be with <newer> systems. <Older> systems never
use auto-tune. <Newer> systems use auto-tune if
they think you understand it. Vista is a client
that understands auto-tune, and some of the
problems seem to happen when <both> sides allow
auto-tune.

(david)

 

[cpf_profiles]

I've got an idea that some of the problems might
be with <newer> systems. <Older> systems never
use auto-tune. <Newer> systems use auto-tune if
they think you understand it. Vista is a client
that understands auto-tune, and some of the
problems seem to happen when <both> sides allow
auto-tune.

Hi David - In this case, File Server and Workstations were brand new, using
the very latest releases & patches of the OSs (from 2 months ago!). Vista
uses auto-tune by default, Windows 2003R2 cannot auto-tune. So in my case it
was when the client auto-tunes but the server does not.

 

[Paul Shapiro]

I think it's Windows Server 2003 SP2 that enables the auto-tune, not R2. I'm
not sure if it's clear that it works better even when both sides are
auto-tuning.

 

[Tony Toews [MVP]]

Thanks for all the hard work you did in figuring out this problem.
I've added it to the Access Performance FAQ and put it on my blog.
If you would like to email me your name, first name only is fine, or
whatever you feel comfortable with I'd be happy to add that to the web
page.

Thanks again, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[knieder]

Thanks for all the hard work you did in figuring out this problem.
I've added it to the Access Performance FAQ and put it on my blog.
If you would like to email me your name, first name only is fine, or
whatever you feel comfortable with I'd be happy to add that to the web
page.

Thanks again, Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

I have a user who has XP with Access 2007. She has a query that
accesses an Oracle 10g database and returns about 7 rows. Time to
execute is less than 1 second. If she clicks on the Design View icon
it takes about 10-15 MINUTES to open. Same problem when she tries to
save it. Any ideas?

 

[Tony Toews [MVP]]

I have a user who has XP with Access 2007. She has a query that
accesses an Oracle 10g database and returns about 7 rows. Time to
execute is less than 1 second. If she clicks on the Design View icon
it takes about 10-15 MINUTES to open. Same problem when she tries to
save it. Any ideas?

I have no idea. Let me run this one past my fellow MVPs and
Microsoft.

(Of course the unofficial MS response will be to move to SQL Server.
<smile> Just kidding. No, really I am kidding.)

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I have a user who has XP with Access 2007. She has a query that
accesses an Oracle 10g database and returns about 7 rows. Time to
execute is less than 1 second. If she clicks on the Design View icon
it takes about 10-15 MINUTES to open. Same problem when she tries to
save it. Any ideas?

One suggestion was to ensure that Name AutoCorrect is turned off.
Can you repro that situation? MS would love to have that situation
repro'd so they can get their team to take a look at it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Pieter Wijnen]

Haven't seen that kind of problem since the early days of 32 bit Windows.
Back then it was related to the Oracle ODBC driver info beeing written to
c:\windows\odbc.ini, as far as I recall.
It's a far shot, but who knows?

Pieter

PS traditionally (I don't know wether it still holds true), I've had better
experience using the Oracle ODBC drivers instead of Microsoft's Oracle ODBC
driver

 

[david s.]

i also have this similar issue. i have a small BE on a network server
(windows server 2003 sb) and multiple FE on the same server (for each user)
the app works fine with multiple users concurrently logged on and using their
FE. the problem is when i am making design changes: it takes forever to
,[yes, i do mean FOREVER], a)open in design view b)select controls c)save
changes (and even longer if working with subforms) i have found that if all
FE are closed then my design time performance is dramatically improved. more
info: i access the program via terminal services session. if i choose to copy
to my hard drive and relink the tables, i can work with no problems. also mdb
is access 2000 file format running with 2007. my symptoms are exactly like
those of the originator of this post, without the vista / XP issue.

 

[Nicholas Meyer]

great

i also have this similar issue. i have a small BE on a network server
(windows server 2003 sb) and multiple FE on the same server (for each
user)
the app works fine with multiple users concurrently logged on and using
their
FE. the problem is when i am making design changes: it takes forever to
,[yes, i do mean FOREVER], a)open in design view b)select controls c)save
changes (and even longer if working with subforms) i have found that if
all
FE are closed then my design time performance is dramatically improved.
more
info: i access the program via terminal services session. if i choose to
copy
to my hard drive and relink the tables, i can work with no problems. also
mdb
is access 2000 file format running with 2007. my symptoms are exactly like
those of the originator of this post, without the vista / XP issue.

Haven't seen that kind of problem since the early days of 32 bit Windows.
Back then it was related to the Oracle ODBC driver info beeing written to
c:\windows\odbc.ini, as far as I recall.
It's a far shot, but who knows?

Pieter

PS traditionally (I don't know wether it still holds true), I've had
better
experience using the Oracle ODBC drivers instead of Microsoft's Oracle
ODBC
driver