Database performance issues

[smikkelsen]

I have a front end / back end database set up with multi user environment. I have front end distributed to each users computer, and a shared back end on the network. It has worked great for nearly a year, but as its grown in size and number of users, I have started experiencing database corruption almost weekly.

I am wondering if moving the backend to a mysql server and linking tables using ODBC would solve this, or if there is a better solution.

Also, I did export all my tables to a mysql backend and connected the front end just to test. Everything seemed to work fine except the fact that it is unreasonably slow.



I have one main table with customer data, and one relational table with "notes" for the customer. The main table has almost 8,000 records, and the notes table has 10,000. I have query on the front end that cuts the 8,000 records down to only about 300 records or so at the most. I then have a form that is based on that query. Is there some major design flaw that i'm missing to cause the database to be so slow? Or is that just what i'm going to get with a mysql database?

[SoftwareMatters]

Firstly I have found 2007 to be the slowest version I've used but the database you have described sounds quite small in terms of complexity and recordcount so you shouldn't be having real performance issuses - what OS are you using? And, how many concurrent users are there?

Some things to try:

If you don't already - compact the back on a daily basis.

Also, I would test the database in a controlled way i.e. single-user locally then single-user over a network then multiple user etc. This should give you an idea where the problem is as it might be your network or pc performance rather than Access.

[smikkelsen]

Thanks for the info. Its actually running plenty fast as it is now. (the front end on the local machine, and the backend on a shared drive).

The performance issues came in with my test ODBC connection to a mysql database.

I just have a bluehost account that I created a new db on and exported all my tables to. I then linked them with ODBC 5.1 and all worked as expected, only i was in some cases waiting 45 seconds for a form to open. This is not the case at all on our network setup now. I just wanted to move everything to mysql server so it can be accessed from the office and from home without needing a vpm connection. I also hoped it would solve our weekly corruption problem. I got some #deleted# records today again and had to run some code to recover table again.

Thanks again for the help.

EDIT:
Sorry forgot to asnwer your question!

We have 9 max users, but usually only 3 or 4 at once. On heavy times of day, we might have 6 at once, but not usually.

Thanks!

[pbaldy]

I agree that you don't have much data. I don't think your problem is mysql, it's that you're working over the internet. Here's a pretty thorough article on the topic:

http://www.kallal.ca/Wan/Wans.html

Note down a ways the section that starts with "Use a true client to server setup", which offers some alternatives. You might be able to get your application to work well over the internet, but you need to, as Albert says, "optimize your Access
application". As you currently have things, I suspect it's pulling that whole 10k table over the wire, which is why it takes so long. You need to come up with ways to stop that. If a form is used for data entry, make sure to open it that way so no existing records get pulled. I haven't used mysql but I use SQL Server a lot. I assume mysql has similar tools that let you force processing to the server so only results get sent over the wire, not entire tables. Examples are pass-through queries, stored procedures and views.