Why do database applications not work well over VPN? I have tried a few proprietary applications that are SQL driven and they are painfully slow, but even something as simple as ACT or QuickBooks will not work well over VPN. What is the reason behind this?
Because the latency and speed of your VPN are slower than the local LAN. Unless you have symmetric 100 megabit connections to the Internet for both locations it's just not going to be as fast as connecting on the local LAN.
If your ADSL connection runs at 640Kbit/s upload and you are comparing a
100Mbit/s LAN you can see the difference.
100 vs 0.64. The VPN connection is up to 156 times slower.
But you have to ask why is that speed being used SO MUCH - the answer is probably that your computer is trying to read through files that are on the VPN system - this is very inefficient. What you want is a client-side (your computer) that gets results from a server (at the other end of the VPN) which is where the data files are scanned.
There is no specific reason - database apps can be written to work well over a WAN.
I have tried a few
These apps use SQL queries - but there is an "old saying" - any programmer can defeat good tools and networks.....
what seems to cause the issue is writing the app so that there are large numbers of separate transactions that happen serially - add latency within a transaction and everything slows down in proportion.
now consider that a LAN may have turnaround times well below 1 mSec, and a typical broadband link will have 10s or 100s of mSec, and slower equipment to traverse such as home routers, multiple firewalls, VPN gear, and you see where the difference comes from.
i have seen lots of apps that could have been written so that 1 query is sent, and then the results are streamed back (which means 1 turnaround for a set of results) - but they do a "give the next record" type query (1 per record).
What is the reason behind this?
bad programming, and more seriously, poor or inappropriate testing.
the biggest single reason i come across is that the developers always seem to have the latest machines, modern fast servers with few other users and and access on a local LAN.
and then you roll it out and suddenly realise it doesnt like your old machines, slow WAN links, remote workers......
Make them work on a remote broadband link and your apps will suddenly get better :)