What could be causes of sluggish database speeds even after upgrades

[matt753]

Prior to today I had a fairly large database, split into front end and back end, the back end residing on a 2-bay Dlink NAS we use as a file server, running in RAID, connected through an old 16 port hub running at 100mbits. Roughly 5-10 users have the front end on their PC and are in the database at the same time.

The problem was the database was very slow moving through records, opening forms, everything was just very sluggish.

I ordered some upgrades that are now in place as of today, a new 24 port cisco switch, the whole network is running gigabit now, a new acer server, one of the mini ones, came with 1TB and runs Window Home Server, as well as a 64GB SSD that the main database is now on (hoping to increase speed there again).

Even after these upgrades are in place, it is only a bit faster. When the backend is on someones computer in a shared folder, or if you have the front and backend on the same computer it is a lot faster. What is the difference between that and how I have it set up on the server? Shouldnt the server be faster? Why is it still sluggish?

[sicarie]

I'd recommend baselining everything - look at the CPU usage on the server (both requesting, and the back-end db), NIC speeds, RAM (and RAM utilization), etc...then also do so on the network equipment.

Then compare that baseline with the "best case" of what the equipment is supposed to be able to do. Whatever's peaked is probably your bottleneck.

If none of that works, it might be a db design issue, but I'd start with profiling your network and systems.

[matt753]

- CPU usage is always < 10%, except when fileindexer.exe is doing stuff
- Not sure how to check NIC speeds, but the whole network is running gigabit, all the cards, cables, cisco switch.


Not sure if it could be a design issue, it runs fine on a local machine (not sluggish) as well as if you share the folder on another network computer and access the front end from another.

[sicarie]

What happens when just one user accesses it? Is it still slow?

[matt753]

Yes seems like its always the same speed if 1 user is on or 10

[matt753]

It is a fairly large database, ~90 pages of VBA code, ~50 tables, ~80 forms. I originally thought it was slow just because of the amount of information its loading, but it runs very smoothly when the front end and backend are on the local machine.

Just to clarify, when its running sluggish is when the front end is on the local and the backend shared on the server.

[sicarie]

And is this an MSSQL db?

I'm going to ask for assistance here - I done single-user access database programs, but haven't gotten too far into the coding - it sounds like there may be something between the system and the code that's running inefficiently if it's only the client.

And just to clarify - these queries run significantly faster when executed on the local machine?

[matt753]

No sorry its an Access running Jet Engine for BE.

Its running faster when it IS on the local machine, forms take almost no time to load, same with any tasks being done. When its running slow is when its shared on the server.

[matt753]

Update: Just tried running the backend off another client machine, so i'm running the FE on my machine, the BE is on another, and it is a lot faster than running it off the server. Not quite as fast as with both them on the local, but significantly faster than running off the server or the old NAS.