We are currently running an Access database with roughly 15 users on a LAN. The DB is split with a front end and backend on the server and users are experiencing extremely slow response times. Database size both back end and front end is only about 8mb total in size. Server is NT 4.0 based and up to this point we have not had problems of this type. Can anyone give us a place to start troubleshooting?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Database crawls on LAN 3
- Thread starter bill7r
- Start date
-
1
- #21
Herb's right, so far as I know - the back end server does NOT have "MSACCESS.EXE" running as a process (probably) that a remote user has access to (no pun intended). Thus no matter what fancy things you try (and I've tried 'em all in 7 years), every single bit of every single byte of data in every single record in a table or tables is sent down to the client machine for processing.
One thing that was mentioned that CAN actually make a big difference is the virus scanning mechanism in place - when you have an active virus scanner sitting there just inside the NIC, checking on every byte coming across the wire, it can have a disastrous effect on throughput, which is pretty much what you're measuring. Since there are so many permutations and percolations of virii scanners out there, you will need to experiment to see what and where your bottlenecks are, but I can tell you this: I had one client machine one time disable their own virus scanner as a test, and it speeded up a FE/BE application by around 25 percent, if I recall. It was a noticeable improvement.
![[soapbox]](/data/assets/smilies/soapbox.gif "[soapbox] [soapbox]")
I have found McAfee to be the worst offender in this category, BTW...
How many of you believe in telekinesis? Raise my hand...
Another free Access forum:
More Access stuff at
One thing that was mentioned that CAN actually make a big difference is the virus scanning mechanism in place - when you have an active virus scanner sitting there just inside the NIC, checking on every byte coming across the wire, it can have a disastrous effect on throughput, which is pretty much what you're measuring. Since there are so many permutations and percolations of virii scanners out there, you will need to experiment to see what and where your bottlenecks are, but I can tell you this: I had one client machine one time disable their own virus scanner as a test, and it speeded up a FE/BE application by around 25 percent, if I recall. It was a noticeable improvement.
I have found McAfee to be the worst offender in this category, BTW...
How many of you believe in telekinesis? Raise my hand...
Another free Access forum:
More Access stuff at
petermeachem
Programmer
It doesn't matter whether the back end pc has Access or not. An Access database is just a file. MySql or SQLServer both have programmes running on the server, Access does not.
However you phrase the query, in general the entire Access table will be copied to your computer. This is why Access is slow on a network.
One thing, Access has a 5 second write back cache delay. I should like to know the mechanism behind this. What code is running the delay? Peter Meachem
peter@accuflight.com
However you phrase the query, in general the entire Access table will be copied to your computer. This is why Access is slow on a network.
One thing, Access has a 5 second write back cache delay. I should like to know the mechanism behind this. What code is running the delay? Peter Meachem
peter@accuflight.com
"However you phrase the query, in general the entire Access table will be copied to your computer. This is why Access is slow on a network."
Not true. A JET query in which all fields with criteria entered are indexed doesn't need to search through entire tables. The index enables JET to get to the desired records very quickly and with relatively little network traffic. That's why it's important to make sure you're indexing the fields that have criteria specified in your queries. Can speed up results by orders of magnitudes, and reduce network traffic at the same time. -- Herb
Not true. A JET query in which all fields with criteria entered are indexed doesn't need to search through entire tables. The index enables JET to get to the desired records very quickly and with relatively little network traffic. That's why it's important to make sure you're indexing the fields that have criteria specified in your queries. Can speed up results by orders of magnitudes, and reduce network traffic at the same time. -- Herb
petermeachem
Programmer
If you compare resulting network traffic when the backend is Access and when the backend is SQL Server, you will see a marked difference. Access will transfer a lot of data when a form recordsource is activated (by button or when the form is opened), SQL will transfer a great deal less.
A big difference to form opening time can be made by removing the forms recordsource. Often, a form will display, say, data for today. Often the form recordsource says SELECT * FROM Table, and the form open event will say SELECT * FROM table WHERE TheDate is today. The form recordsource will pull the table over and the form open will replace it.
Actually measuring network traffic with different databases is very revealing and does show why Access will typically be very slow to open a form and then perform well when scrolling through records. Peter Meachem
peter@accuflight.com
A big difference to form opening time can be made by removing the forms recordsource. Often, a form will display, say, data for today. Often the form recordsource says SELECT * FROM Table, and the form open event will say SELECT * FROM table WHERE TheDate is today. The form recordsource will pull the table over and the form open will replace it.
Actually measuring network traffic with different databases is very revealing and does show why Access will typically be very slow to open a form and then perform well when scrolling through records. Peter Meachem
peter@accuflight.com
hoggbottom59
Technical User
15 Users - I assume you're using an SQL Server on the Back-End.
If you're using Ms Access on the Back-End I'm surprised it works!
Our database is front-end and back-end in MS Access. As soon as we have 3 users it slows right down. Access isn't really meant to be multi-user. It works for a few users but starts to really slow after that.
Leon.
If you're using Ms Access on the Back-End I'm surprised it works!
Our database is front-end and back-end in MS Access. As soon as we have 3 users it slows right down. Access isn't really meant to be multi-user. It works for a few users but starts to really slow after that.
Leon.
petermeachem
Programmer
If you use what I might call Wizard Access yes. If you spend time looking at what is happening on the network you can speed it up a great deal. It obviously depends on the size of the forms and the tables too. However you do get to a point where you may as well use VB and SQL Server/MySql because you then have much better control of what is going on. You do lose all the really good this you get with Access like Reports, easy to use, easy to modify etc etc. Peter Meachem
peter@accuflight.com
peter@accuflight.com
Similar Problem as mentioned earlier.
I have access 97 that just runs from shared files on a file server (NT 4). sometime it can take uo to 5 minutes to open a file, even when there is no other user using it, and network traffic is low (late nights![[thumbsdown]](/data/assets/smilies/thumbsdown.gif "[thumbsdown] [thumbsdown]")
).
Running compact and repair sorts the problem for a while (compact can take ten minutes on a 6MB file, though)
We currently have ten users, but generally there are only one or two in any one database at any one time. I have checked, and the ldb files are correctly deleted. if I copy one of the 'slow' databases to a local drive, it is still slow, but compact and repair sorts it.
Any suggestions as to what is causing this?
Another question for the people who have tried it - we have NT SBS 4.0, so have SQL server - but I have never used it. how easy is it to convert over, and are there advantages to it.
It would not really be possible to have the front end on the local workstations as each project we do has its own customised database, and we start 4 or 5 a week, so they have to stay on the server - would using access front end and sql tables, both on the server, be any faster?
Ta
Jonathan
I have access 97 that just runs from shared files on a file server (NT 4). sometime it can take uo to 5 minutes to open a file, even when there is no other user using it, and network traffic is low (late nights
).Running compact and repair sorts the problem for a while (compact can take ten minutes on a 6MB file, though)
We currently have ten users, but generally there are only one or two in any one database at any one time. I have checked, and the ldb files are correctly deleted. if I copy one of the 'slow' databases to a local drive, it is still slow, but compact and repair sorts it.
Any suggestions as to what is causing this?
Another question for the people who have tried it - we have NT SBS 4.0, so have SQL server - but I have never used it. how easy is it to convert over, and are there advantages to it.
It would not really be possible to have the front end on the local workstations as each project we do has its own customised database, and we start 4 or 5 a week, so they have to stay on the server - would using access front end and sql tables, both on the server, be any faster?
Ta
Jonathan
Interesting reading.....
I have been running a FE/BE system for a few years now, started in A2.0 and now resides in A2K. Some details:
FE size: 15 meg
BE(1) size: 600 meg
BE (2) size: 350 meg
USERS: 15 to 30
Originally running Novell and then the IS swithched to NT.
BRAKES!!!
Anyway...
The three largest contributors to slowdown on our systems turned out to be:
1 - Mcaffee
2 - Server record lock tracking
3 - second round table links
You can work around the first two using your "friendly?" IS guru's and...
Disable and reconnect the table links on the slow machines.
Best of luck....
Rhonin
"seppuku is best shared with a friend..."
I have been running a FE/BE system for a few years now, started in A2.0 and now resides in A2K. Some details:
FE size: 15 meg
BE(1) size: 600 meg
BE (2) size: 350 meg
USERS: 15 to 30
Originally running Novell and then the IS swithched to NT.
BRAKES!!!
Anyway...
The three largest contributors to slowdown on our systems turned out to be:
1 - Mcaffee
2 - Server record lock tracking
3 - second round table links
You can work around the first two using your "friendly?" IS guru's and...
Disable and reconnect the table links on the slow machines.
Best of luck....
Rhonin
"seppuku is best shared with a friend..."
petermeachem
Programmer
What do you mean by 'second round table links'? Do you mean there is a difference in performance between copying a file linked to a server around and relinking on each users pc? Peter Meachem
peter@accuflight.com
peter@accuflight.com
FAO WildHare and others who suggested checking the virus scanner.....
I found that the scanner was by default checking all files named *.md? Because of the way multiusers write to the database, it seemed to scan the file every time a change was made, both on local workstations, and on the server. I went through and added *mdb files to the ignore list...and performance jumped about 50%, and we no longer had the occasionally extreme delays in opening or closing databases.
Thanks Guys!
I found that the scanner was by default checking all files named *.md? Because of the way multiusers write to the database, it seemed to scan the file every time a change was made, both on local workstations, and on the server. I went through and added *mdb files to the ignore list...and performance jumped about 50%, and we no longer had the occasionally extreme delays in opening or closing databases.
Thanks Guys!
the slpitting of the Backend and Frontend is a good start.
when the response times of the database begings to slow down have your network admin check to see if there is any "locking" going on. Whether it be on your database or another one. The more "locks" that occurr the SLOWER the response time of any access database. Some users still have just a single database with mulitiple users which down the road causes all kinds of problems. HTH
when the response times of the database begings to slow down have your network admin check to see if there is any "locking" going on. Whether it be on your database or another one. The more "locks" that occurr the SLOWER the response time of any access database. Some users still have just a single database with mulitiple users which down the road causes all kinds of problems. HTH
Guest_imported
New member
- Jan 1, 1970
- 0
another thing to consider is network speed. if it's a small office. 8 computers, a simple 100mbs hub and nics network would work fine. if it's larger you might want to use 100mbs switches to keep network speed up to par.
BonCourage
Programmer
Bill7R,
One of the other respondees (Super Speed) described an exact scenario I had with split databases and crawling-through-concrete speed on a network. The solution? Go to Tools>Options>General tab in Access. Uncheck (deselect) all Auto Name Correct options. That's it! When opening forms, etc. with these selected, Access has to verify the names of ALL objects on all forms when opening anything. This seems to get worse with subsequent users. The Auto Name Correct feature is great during programming but terrible during run time. Let me know if this works.
One of the other respondees (Super Speed) described an exact scenario I had with split databases and crawling-through-concrete speed on a network. The solution? Go to Tools>Options>General tab in Access. Uncheck (deselect) all Auto Name Correct options. That's it! When opening forms, etc. with these selected, Access has to verify the names of ALL objects on all forms when opening anything. This seems to get worse with subsequent users. The Auto Name Correct feature is great during programming but terrible during run time. Let me know if this works.
BonCourage, I tried turning off the auto name and it seems to have improved performance,
Someone else from this forum advised me to docmd.showallrecords on the close event and that also improved performance.
Performance seems like a big problem in my experience since sometimes the newwork boys just want to blame the issue on Access as opposed to investigating it and determining the true nature of the problem. Along that thought, Is there a guide or procedure that would help a developer at roll out time? A simple list of things to disable or other quick solutions to improve performance. (I have always believed in eliminating the simple things first, ya know, Is the printed plugged in? before ya take the thing apart.)
Someone else from this forum advised me to docmd.showallrecords on the close event and that also improved performance.
Performance seems like a big problem in my experience since sometimes the newwork boys just want to blame the issue on Access as opposed to investigating it and determining the true nature of the problem. Along that thought, Is there a guide or procedure that would help a developer at roll out time? A simple list of things to disable or other quick solutions to improve performance. (I have always believed in eliminating the simple things first, ya know, Is the printed plugged in? before ya take the thing apart.)
BonCourage
Programmer
I have not heard of such a guide (even Microsoft had trouble identifying the Auto Name Correct snafu) but Tek-Tips seems to be a good place to enlist other experienced programmers to contribute to a "Roll-out Guide". In other courses by Microsoft (ie: Mastering Visual Basic for Enterprises), the necessary steps are well defined, but I have not seen Access treated as an Enterprise model. Maybe Microsoft has its interests in SQL, XML, etc. It really never took Access much beyond a desktop environment (without unresolved problems like we've been discussing). If you begin this "Roll-out guide", I will be glad to contribute what little I know. Good luck.
Just for the record:
I had an Access 97 db with a hugely complex query which pulled data from various spreadsheets and a back end Access 97 database. With many many calculated fields, it would take about 15 minutes to export the query to Excel using Docmd.Transferspreadsheet.
From the suggestions above, I moved the query to the
back end Access 97 database and used DAO to access it.
By also using .CopyFromRecordset, the whole recordset is now transferred to Excel in less than 30 seconds.
It seems like a pseodo client server solution to me.... ;-)
I had an Access 97 db with a hugely complex query which pulled data from various spreadsheets and a back end Access 97 database. With many many calculated fields, it would take about 15 minutes to export the query to Excel using Docmd.Transferspreadsheet.
From the suggestions above, I moved the query to the
back end Access 97 database and used DAO to access it.
By also using .CopyFromRecordset, the whole recordset is now transferred to Excel in less than 30 seconds.
It seems like a pseodo client server solution to me.... ;-)
I too have a location w/ extremely slow performance. Running Access 97. Just for information, I cannot find any way to turn the Auto Correct Name off in Access 97. Not on the Options|General tab or any other Option tab. And it does not show up in Access help. I did check Access 2000, and it does exist on the General tab.
Any ideas on how I can shut it off in Access 97?
Jdemmer
Any ideas on how I can shut it off in Access 97?
Jdemmer
- Status
Similar threads
- Locked
- Question
- Locked
- Question