0

I have developed an application which connects to a database over a WAN.

As long as I'm using the application on my local network, everything works fine. When I connect over the WAN, the database request takes several seconds. The user experiences long load times when using the application from his location. The loading times are so long, that it isn't workable.

Most of the time, the query selects all the data from a table. Sometimes it only selects one line.

I think this is due to latency on the WAN. Does anyone know how I can make my queries "latency-proof"?

2

You can improve the WAN link or improve the query to make it more efficient. Other than that you are limited by the laws of physics as the data has to travel back and forth through the link there is no getting around that.

  • "Laws of Physics": The distance between New York and London is ~5600 Km. Speed of light is ~300,000 Km/Sec. The time it takes an electron to travel from NY to London is ~5600/300,000 = ~0.019 Sec, or ~19 milliseconds. That is not the reason for his multi-second latency. – SQLRaptor Apr 6 at 15:42
  • @SQLRaptor That would fall under "Impove the WAN link". That is also assuming the entire connection can be made fiber optic and ignoring the fact that data does not travel at the full speed of light through a fiber cable not to mention all the other things that have to happening like routing and switching and going through the OS all of which are much slower. – mbwasi Apr 7 at 2:08
1

You should be querying the minimal amount of data (don't filter in the application), and if you are doing multiple inter-related queries see if they can be reduced to a single query.

If you want performance, don't run database connections over a WAN, install an server application providing a suitable API to a local DB and then connect to the API service.

0

What is the ping time across the WAN?

  • Collect multiple queries into a Stored Procedure. This way one CALL goes across the WAN, rather than those multiple queries.

  • Do more processing in the server, less in the client. For example, if you return 10000 rows for munching on in the client, see if you can change so that a more complex SELECT is run, but it returns only 10 rows. (This assumes the throughput of the WAN is also worse than localhost.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.