ADP - SQL Server - Lag Problems

[KeithWinDev]

Hi, I am seeking enlightenment on what appears to be a whole bunch of worms in trying to troubleshoot massive legacy problems.
My client is currently using an ADP front end built "on the fly" by many different developers who "came and went" over the last 10 years. (Views have been used on the 70+ tables rather than Stored Procedures for most queries).
Originally the application was using the Access DB engine (no problems), then my client, having created a new office in the N of England, decided to migrate to SQL Server 2003 with the object of sharing a common database for the 2 offices(the server being physically situated in London) accessed by the Liverpool office through a 2Mb leased line, and by London through their VPN.
Whilst preparing for the "merge", both offices currently have their own database (using the same ADP front end)on a VPN, and vision on each database is acquired from the remote site via the leased line.
The problems are the following:
(a) Access from London to Liverpool using the ADP takes 10 seconds (OK). The same access from London to Liverpool using RDP "up" and ADP "down" takes over 2 minutes (if anything happens at all) !!!
(b)Access from Liverpool to London always takes over 3 minutes !!

When we merged the 2 databases on SQL Server2003 (London accessing through the local VPN, and Liverpool through the 2Mb pipe), things were fine for about 10 minutes, then as more users logged on, the whole thing just ground to a nasty halt.

We have checked the BT line and all "normal" connections, and everything is fine. We have verified locking mechanisms on the DB, and these appear OK too.

Does anybody have a magic wand (except to rip the whole thing apart and start again)?

Any ideas would be greatly appreciated !

 

[cjowsey]

I don't think there is a magic wand for something like this. I suspect that there are a lot of issues that need to be addressed and that (if it is cost justified) you will probably need to redesign and redevelop the system at some time in the near future. However that is a bit like an internet medical diagnosis without seeing and touching the patient.

There are a number of things you can do to try and speed the system up on a piecemeal basis. You just have to know when to stop because re-engineering a live system step by step is not usually the cheapest way to go. I am sure that you will have tried at least some of the ideas below, so I apologise in advance if it seems like I am trying to teach you how to suck eggs. (For non-English speakers, this is an old colloquial expression).

Poor database design is one of the biggest performance killers. 70+ tables do not mean that the database is well designed or properly normalised. Without knowing a lot more about the application, I don't know if that is a lot of tables for the application or not very many. Review the database schema and see how well normalised it is. Be very careful about rushing into redesigning the database because a system that old and written by many people will have a lot of "gotchas" that will take a lot of time to fix and test.

I assume the views are a legacy of the original Access queries. Are these well written? It could be that they are joining on tables that are not required or returning too many columns (watch out for any “SELECT * FROM …” constructs. They are a good sign of sloppy design). You could start converting some of the more critical ones to stored procedures. This could also take a lot of time and budget. It is probably better in the short term to make sure you are using server filters where possible on the forms to limit the amount of data being transferred.

Check that indexes have been defined properly on the tables and are being used by the views. You can profile them with the SQL Server management tools.

Does every user have their own copy of the ADP or ADE? It makes a big difference as Access is not designed as a multi-user client. For the remote users set up a unique folder on the terminal server for each user with a copy of the application. It will eliminate a lot of conflicts with printers, toolbars and personalised settings.

Why are you using a VPN in London? If the users are all on the same site and the same LAN, just use the UNC address to access the database.

2MB is a lot of bandwidth, but it all depends what else is using that pipe. Is there any other traffic on it besides your application? Is it throttled in any way at the Liverpool end?

Unless you need high quality graphics, set your RDP display to the lowest colour setting you can get away with. You don't need to waste bandwidth on 24 bit colour for plain grey screens. Disable remote sound and desktop backgrounds and enable local bitmap caching.

Try and identify the number of users at which the system starts to slow down markedly. If that number is "n", is the degradation the same if n users all log on in the same location and if n/2 users log on in both locations?

Are there any forms in the application or particular functions that have a noticeably greater performance hit? Are there any entry and exit functions in the code to allow usage to be logged and profiled?

I hope there is something in here that helps you a bit.