Backend Database Slows system Down w/ Multiple Users???

[jwoods7]

I have created a system that attaches to a back end (data) file for an accounting/inventory system. The data file is located on a central server, the program is on various computers around the business. Each one looks to the server to attach the data.

Each computer alone runs blazingly fast with the data. The problem comes when 2 or more users are attempting to look at the system. It slows down tremendously. What used to take less than 1 second to finish now takes 10-20 seconds, with just one more user on. Adding 2 or 3 more doesn't seem to have the same effect, it's only when multiple users have the program open.

The biggest slowdown is just opening forms. Not as much running queries or adding data.

I'm hoping someone has thoughts on this, I know it's not the connection to the server because the same machine will run fast when all other users close out. This is consistent from computer to computer.

Please advise, I have created a great system....EXCEPT FOR THIS! ARGH!

 

[Trendsetter]

You don't say if you have the application side of the system on each PC and they attach across the network OR if both the front end and back reside on the server and are attached at each connection.
If you are using Access as you backend you will be putting a great load across your network because every user passes all records across the LAN when querying the backend.
You need to ensure that the backend is compacted and that the design is as efficient as possible.
there will always be a performance hit when going multi-user across a LAN but the efficiency & bandwidth of the LAN can have a major bearing on performance.

 

[jwoods7]

Thanks TrendSetter,
I have the program on each computer, then each of them look across the LAN to the server. The linked tables have the location of the data file as their location.

I got this idea from another application of the same style that did it, but didn't have that issue in their program.

 

[jmcclain]

I'm assuming that the front-end (located on the local pc) has the forms / queries/ reports / code and the backend (located on the server) just has the tables?

Is there one specific table that seems to be slowing things down?

In my multi-user app, the performance hit is when several users are querying the same large table.

Just some thoughts.

 

[jwoods7]

Thanks for those thoughts. It could be, but I notice a slowdown immediately when starting the program. Your correct, the linked tables, forms and queries are all on the program (PC) side of things, the tables are all that is in the data file.
It's strange because the only thing I have it doing on startup is a couple of functions. I'm assuming that it attaches itself and brings the current data from the server data file as well. Without anyone else in the program, startup is almost instantaneous. If someone is already in the system, the startup form shows for a good 10 seconds.

On startup I have a form "xStart Page" that has a timer interval of 1000, then on timer does some various functions, opens another form and closes itself. Could it be the queries that are being run at that time (which i don't believe are that big and complicated) are slowing it down? So much so that when nobody has the program started it won't even be a noticeable lag?

 

[Trendsetter]

The timer event can be a killer in many Access apps. The action takes command of the processor and everything else takes second place...
Do away with the timer until you have the forms loaded I think you will see a big improvement in performance

 

[jwoods7]

Thanks for the advice. It did improve but not by that much, it's hard to tell now that it's not waiting for the timer.

This doesn't explain why opening forms is slower as well though. For example, opening a page that has a List Box (which displays invoices created today) is a lot slower as well.

 

[vbajock]

what op system, etc is running on the server?

 

[jwoods7]

Windows 2000 Professional is on the server.

I tried compacting the data file on the server but didn't make any difference.

 

[vbajock]

Not a real server. Win pro is workstation stuff. Is someone using it as a workstation while you are serving up data? There is a setting somewhere where you can optimize the box for application or background use. I am not at a 2000 box, but I believe it is accessed from Control Panel/System and then there is a button some where for performance improvements. Set it to optimize for background use.

 

[jwoods7]

Thanks,
No it's running as a server only. Nobody uses it at a desktop.

I found the place where you can optimize for background use...haven't seen a performance increase but maybe it needs to be restarted.

I believe the slowdown is due to some programming that I haven't done that the other program did. It was ran on the same desktops, same LAN, and same server but did it faster...more efficiently maybe. I looked at the size of the old data file (from the other program) and it is around 53 mb, mine is only 15 mb. Relatively speaking the program I created starts much quicker (would take 30-60 seconds on old program) but when it comes to viewing forms and reports internally the old program didn't have a performance decrease when other users were viewing system. It took the same 30-60 seconds for startup and ran normally once started. My system shows a decline when system is being viewed which makes me think they took the extra time loading to do something additional.

 

[jwoods7]

I'm trying another angle on this issue:

Want to optimize both the backend database and the frontend programs to try to minimize the delay.

I'm not sure if this is the right area to go since the program runs quickly if only one user is viewing data but it's worth a try.

I've tried optimizing the relationships and using the Performance Analyzer built in to Access but I'm not really sure what to look for or what makes a difference. The performance analyzer gave tips on creating indexes in some places, relationships in others but I'm not sure when to have what type of relationship. Does anybody have any good articles or tips on optimizing databases for networks or any other ideas?

Thanks in advance