We have about 10 databases that reside on the same server as all our files. The backend is on the users PC and the frontend is is on the server. We have been experiencing performance issues with these databases lately. Sometimes they will open perfectly normal and others it seems like it takes forever for it to open. Does anyone have any suggestion as how to make these databases perform better? Thanks
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 Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Access performance
- Thread starter dleggett
- Start date
- Thread starter
- #3
We have about 4 in particular that are around 5 MB each. These are used pretty much every minute. We have about 110 users each with various permissions to databases. I apologize for posting about wrongly, its the front end that is on the users PC. Sorry about that. as far as the SQL Server/MySQL system, I was mentioning that to my supervisor as neither one of us has that much experience with SQL. We do have chances to learn it though so this may be something that we would consider for the next budget year.
- Thread starter
- #5
Sometimes the Internet is slow, I am currently looking at a product to hopefully that will solve some of my problems here. I am going to talk to my supervisor to see if she will request this in her budget. But on the average day, when Internet is normal, databases are relatively slow. Then on on the next day, they speed back up and suggestions? Just in case you would like to see the product.
Obvious things to check:
Had anything changed on the PC, servers, network switches around the time the performance problems started?
I'd ask your network people to see if they know of anything in this area.
Aside:
If the DBs are only 5Mb each, then you could use MySQL or SQL Server Express at no cost in buying the software, it would simply be your time in migrating the data across and testing.
If you can host it on an existing server, that would mean there would be no infrastructure costs either.
John
Had anything changed on the PC, servers, network switches around the time the performance problems started?
I'd ask your network people to see if they know of anything in this area.
Aside:
If the DBs are only 5Mb each, then you could use MySQL or SQL Server Express at no cost in buying the software, it would simply be your time in migrating the data across and testing.
If you can host it on an existing server, that would mean there would be no infrastructure costs either.
John
What you may find is your applications are inefficient but often the sheer bandwith of your network hides this. Two things to look at first - have you got complex starting forms that have lots of dropdowns? Secondly, have you been careful to add indexes for all frequent queries, and have you avoided anything but very simple SQL? Aggregate functions for example will slow you down.
- Thread starter
- #8
- Thread starter
- #9
JRBarnett, I forgot to mention that we just switched out two switches last week, but we have been having performance issues for a while. We are considering the bandwidth monitoring really hard, due to we are getting ready to get another phone system to merge with other county offices.
A couple of things...expanding on BNPMike's points:
For dropdowns, use Snapshot queries.
For forms, if they're in dyanset mode, try to open on a single record--for instance, use a default criteria of zero for, say, recordID, and then let the user enter criteria for searching/selecting a record or group of records.
Un-Check the (ridiculous) 'feature' of AutoCorrect tracking. I've read that this can cause issues. It's in Options/General.
And like bnpmike said--use indexes on the fields you're to have in the forms' search criteria. With Access not being a server-based database, the 'backend' does not do the processing. So if you have a form opening with all records in it's source table, Access has to schelp them all across the wire. If you use an index, Access only has to schelp the entire index, and then uses file-pointers to select the set of records based on that index. It's still not as good as a server based db, but using the index will help with a front-end/back-end setup.
--Jim
For dropdowns, use Snapshot queries.
For forms, if they're in dyanset mode, try to open on a single record--for instance, use a default criteria of zero for, say, recordID, and then let the user enter criteria for searching/selecting a record or group of records.
Un-Check the (ridiculous) 'feature' of AutoCorrect tracking. I've read that this can cause issues. It's in Options/General.
And like bnpmike said--use indexes on the fields you're to have in the forms' search criteria. With Access not being a server-based database, the 'backend' does not do the processing. So if you have a form opening with all records in it's source table, Access has to schelp them all across the wire. If you use an index, Access only has to schelp the entire index, and then uses file-pointers to select the set of records based on that index. It's still not as good as a server based db, but using the index will help with a front-end/back-end setup.
--Jim
- Thread starter
- #11
- Status
Similar threads
- Locked
- Question
- Locked
- Question