Avoid slowdowns while using off-site database

The basic layout of my problem is this:

• Website (ASP.NET/C#) hosted at a dedicated hosting company (location 1)
• Company database (SQL Server) with records of relevant data (location 2).
• Location 1 & 2 connected t开发者_运维知识库hrough VPN.
• Customer visiting the website and wanting to pull data from the company database.
• No possibility of changing the server locations or layout (i.e. moving the website to an in-office server isn't possible).

What I want to do is figure out the best way to handle the data acces in this case, minimizing the need for time-expensive database calls over the VPN. The first idea I'm getting is this:

When a user enters the section of the website needing the DB data, you pull all the needed tables from the database into a in-memory dataset. All subsequent views/updates to the data is done on this dataset. When the user leaves (logout, session timeout, browser closed etc) the dataset gets sent to the SQL server.

I'm not sure if this is a realistic solution, and it obviously has some problems. If two web visitors are performing updates on the same data, the one finishing up last will have their changes overwriting the first ones. There's also no way of knowing you have the latest data (i.e. if a customer pulls som info on their projects and we update this info while they are viewing them, they won't see these changes PLUS the above overwriting issue will arise).

The other solution would be to somehow aggregate database calls and make sure they only happen when you need them, e.g. during data updates but not during data views. But then again the longer a pause between these refreshing DB calls, the bigger a chance that the data view is out of date as per the problem described above.

Any input on the above or some fresh ideas would be most welcome.

Cache static data(asp.net caching function) and create as little as possible queries.

Try to create a two step process for database updates with some client callbacks where needed to make the client feel like they have a responsive website. (Ajax would shine in this situation)

If you haven't already, firstly I would take a good look at the bandwidth available and make sure that you definitely have a problem with remote DB calls.

I think your in-memory dataset idea may well be difficult to implement effectively. Perhaps the ideal solution would be to write an API for your database layer. This could be implemented on a web service next to the database in location 2. It might need you to rewrite your forms though, which may not be an option.