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.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cpf_profiles" <(E-Mail Removed)> wrote in message
news:4F08250C-C12A-4FF7-B653-(E-Mail Removed)...
>
> 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.

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cpf_profiles" <(E-Mail Removed)> wrote in message
news:FDFB54BF-A826-4C2E-A69A-(E-Mail Removed)...
> 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" wrote:
> 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?

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cpf_profiles" <(E-Mail Removed)> wrote in message
news25F2131-074F-4B32-B717-(E-Mail Removed)...
> "Allen Browne" wrote:
>> 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" wrote:

> 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?

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" <(E-Mail Removed)> wrote in message
news:F01670A7-30D4-497D-9499-(E-Mail Removed)...
> "Allen Browne" wrote:
>
>> 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?

Hi Paul, and thanks for the response.

"Paul Shapiro" wrote:

> 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" wrote:

> "Paul Shapiro" wrote:
>
> > 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.

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/2...-on-vista.aspx

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

Thanks for your help, lads.