 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2008Access 2007 Design view performance on Vista vs XP
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.  SignatureAllen 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. > I tried a search but found nought. I have a dual core, 2GB Vista machine > and [quoted text clipped - 19 lines] > > 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. SignatureAllen 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. > Thanks for the informative response, Allen. > [quoted text clipped - 67 lines] > notwithstanding > of course :) > 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. SignatureAllen 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. >> Presumably you have followed all Tony's advice. > [quoted text clipped - 20 lines] > 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. > [quoted text clipped - 20 lines] > > 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. >> I don't believe it's a network issue. >> [quoted text clipped - 29 lines] > 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. > 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? > > 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 [quoted text clipped - 8 lines] > 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-vist a.aspx I wonder what else this might speed up (e.g. Sage) Thanks for your help, lads. 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) > Hi Paul, and thanks for the response. > [quoted text clipped - 22 lines] > Access may be creating an index each time I enter design view on a subform > that doesn't have a defined relationship? > I've got an idea that some of the problems might > be with <newer> systems. <Older> systems never [quoted text clipped - 3 lines] > 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. 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. >> I've got an idea that some of the problems might >> be with <newer> systems. <Older> systems never [quoted text clipped - 10 lines] > it > was when the client auto-tunes but the server does not. 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 SignatureTony 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/ > 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. [quoted text clipped - 9 lines] > 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? >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 SignatureTony 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/ >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 SignatureTony 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/ 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 >>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 [quoted text clipped - 7 lines] > > Tony 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 [quoted text clipped - 18 lines] > > > > Tony 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 [quoted text clipped - 38 lines] >> > >> > Tony |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.