General Performance on Terminal Server 3

[Najemikon]

Hi,

I've been running a small database for some time on a Terminal Server. It all works fine, except for speed. As I've developed it, it's got slower & slower.

When I first created it, there were three main forms, working pretty much independently of each other, just sharing customer & engineer information.

Now I've added several more forms to administer the system on a slightly larger & more intuitive scale. All good, but it's very sluggish.

For the most part, it's an inconvenience, but the main form uses a list box to display current 'incidents'. There is a combo box that selects different sorts & filters (basically loads a new query as the listbox rowsource). There's maybe 50 records in the queries at any one time, but it takes 20-30 seconds to load, which is a pain!

But not as much as when I'm editing; selecting design view can take as long as a minute on any form, while editing VBA is horrendous when switching between modules.

The database is split into front & back ends, but stored on the same drive. There are about 10 users, but an average of 5 at anyone time.

The frontend is an MDE file (shared), while I exclusively use an MDB version in case of editing. There is no difference in performance between the two frontends, but they use the same backend.

Code is compiled & I've regularly ran the 'Compact & Repair' option on both the front & back ends. I've also disabled 'Name Auto Correct' after reading a tip on this site. It did make a huge difference to speed, but that was ages ago & it's moved on a great deal since then.

Obviously being a server environment, it would be great to have the frontend local, but with it being Terminal Server, the client PCs are either old PCs with minimal software (no Access!) or even just dedicated clients.

Any general advice about running databases in this sort of environment would be much appreciated!

Thanks,


Jon

 

[jsteph]

Since it sounds like the major slowdown is comboboxes...

...One of the major issues I've seen with comboboxes using a query as source is that most people just leave the Access default of 'Dynaset', instead of changing it to Snapshot. For a read-only combobox, there's no reason to use a dynaset query as the source.

Check this first, especially in a multi-user situation. Also, check the sorting and criteria on the combobox queries, make sure you have appropriate indexing.

If the source for this lookup data will be generally static, it makes sense to index everything you might possibly need--you can do 32 indexes per table. My rule is use 'em all up if you have to for a table with little or no update/insert but lots of reading.
--Jim

 

[Najemikon]

That sounds very interesting, Jim, & makes perfect sense. Now I come to think of it, the other forms seem to pause most at combos. I'd never given it a thought before!

When the query is displayed in a listbox, there's no choice, it has to be read only. The nature of the database means I only have a couple of update/append queries & they have nothing to do with this form.

I'm out on site tomorrow, but will look at this & report back as soon as I can.

Thanks for that! You've made me very hopeful.

 

[MoLaker]

What is your server hardware (cpu speed and RAM, mainly)? Commonly server boxes are made more for file server functions and not necessarily as an application server. It is not uncommon for a server to be in the 400-500Mhz cpu speed range. There may be multiple cpu's capable of good multi-tasking, but still on the slower side by today's desktop standards when it comes to running an app. Usually, a server is fairly beefy in RAM, but it should be looked at as part of your troublshooting process.

Perhaps a good test of your application would be to place a copy (FE & BE) on a decent desktop and see if it still suffers speed problems. If not, maybe the server is just not up to snuff as a terminal server.

 

[Najemikon]

I've done that, Molaker & indeed it is faster on a single desktop.

I'm not sure of our current specification on the server, but we are an IT Services provider, specialising in HP server hardware & it has just been upgraded. It is definitely an application rather than a file server. The performance on this database is the only thing that hasn't improved since the new box went in!

Interestingly, if I VPN to the server from home, I get a better response than on the LAN! Not a huge improvement, just a more consistent one. However, I normally only connect out of hours, so I'm the only one logged in. This led me to test responses from different parts of the LAN, thinking a switch or cabling was to blame. But I even stuck a 10mbs hub on my PC to cause a bottle-neck & it was no worse.

 

[MoLaker]

Hmmm, sounds like you've done your homework. Are you in a position to be able to run the application locally on the server? Even if the server hardware is adequate, there is a setting whereby you set what the server's role is to be - application server, file server, etc. This could have some bearing as it affects cpu priorities. If the application is slow locally, and the server role is appropriate, then it would seem to be an application issue. If the application runs fairly speedly locally, then it could be a network bottleneck or terminal server's setup or both. I'm not familiar enough with terminal server to discuss installation and setup on top of the operating system, but I suspect there are "things" that can have a performance impact.

 

[markphsd]

I've run Access databases from Terminal Server. It's the easiest, fastest way to optimize the performance. This is because Access is a desktop based database, Terminal Services keeps the db right on the same computer. The main problem with Access not the multiuser environment, but the amount of data that has to be transfered across the network, Terminal Services removes this requirement.

The vpn comment is odd, but once again, Terminal server is much more friendly to networks then Access is.

With that in mind, this wouldn't be a network issue, it is either your App or something that is slowing down the server. Try stopping all services and applications running, then run your DB.

You might have several problems going on, one with the local network and one with the

good luck

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[MoLaker]

I just noted this from your first posting

But not as much as when I'm editing; selecting design view can take as long as a minute on any form, while editing VBA is horrendous when switching between modules.

This doesn't sound much like a DB design problem. Some sort of corruption, maybe. It still sounds more like a server configuration problem. Take another look at the server RAM. When users run applications on terminal server, each session is isolated from the others and cannot share RAM. This means a terminal server needs a LOT of RAM if several users are running apps concurrently. Watch the swapfile activity. You may be surprized even if the server has several gig of RAM.

Here's a whitepaper article that might be informative.

http://www.windowsitpro.com/Windows/Article/ArticleID/21813/21813.html

This could explain your VPN speed issue if you attached via VPN after hours when few or no other users were connected.

 

[willir]

Before we start adding more RAM, upgrading the terminal server, let's check the AutoCorrect setting.

From the menu...
"Tools" -> "Options" -> "General" tab
Look at the section NameAutoCorrect

Disable "Track name AutoCorrect info"
This option and the sister variables (Performa and Log) should only be used for development phase of the database.

Other things...
Decompile the database peridocially.
Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times

And more stuff on performance...
Slow Performance When You Access Files on Servers
You Experience Slow File Server Performance and Delays Occur When You Work With Files That Are Located on a File Server

Richard

 

[Najemikon]

Thanks for all the replies. I am confident the server is running well below it's thresholds in all respects. I know what your saying about RAM, Molaker, as our configurations are always built on number of users vs RAM. Basically, as if each session were a seperate PC. The more I thought about it, the more the problem seemed to point at the LAN. Combined with a couple of tips from this thread, the database is running very quickly now.

Jim, your suggestion of switching the listbox form from dynaset to snapshot made the biggest & most obvious difference, then Richard's decompile seemed to smooth out the other forms, so thank you to you both. That last one made sense as although it's a small database, it was never designed from the ground up to do what it's doing now. Rather, it's evolved. There was bound to be some issues in the code.

Before making those changes, we looked at the client PCs. Terminal Server may run fine from even a Win95 client, but a poor graphics system on that client becomes a bottleneck. I've tweaked the older machines to squeeze more hardware acceleration & refresh rates out of them, which has helped a great deal. This explains why it ran so well from the console & over the VPN.

Thank you for all your comments. I honestly can't see how the database could run any faster! Some of the more stubborn users are actually logging time now it works properly!