| Author |
Topic |
|
brace77
Starting Member
25 Posts |
 Posted - 2009-09-08 : 06:27:43
|
| Hello.I am writing a client server application in Delphi. This application runs many queries against the DB. When used in LAN all works fine, but if I try to access the server using a VPN (for example running the application from a laptop outside the office that is connected to the office LAN through a VPN) I experience incredible latency, even if the data is small.The sample application does:- Connection to DB- Timer is started- Query is executed- Timer is closed when Query returns.- Query execution time is displayed.I made the test with a simple query that returns a single integer (61 for example), by reading a single record/field from a table and with a bigger query that returns a full table (100 records/10 fields/ integer or varchar (100) data).What it is surprising is that for running the light query (1 integer returned) and the heavy query (the full table returned) I have those results:- LapTop with mobile slow connection (approx average bandwith 20kbps): 1500ms for light query, 1550 for heavy query- Connection from another office with ADSL (approx average bandwith 150kbps): 200ms for light query, 240 for heavy queryso what it is surprising it is that in both cases there is a fixed latency + some time for retrieving the data.Why this? The db connection is still open, while here it looks like that there is a kind of slow handshaking (due to latency) everytime I execute a query.Is this behaviur normal?Is it because of VPN?Using a direct connection to Database can help? (I mean ecposing SQLServer with external IP and connecting to it)May anyone help me? |
|
|
brace77
Starting Member
25 Posts |
Posted - 2009-09-09 : 09:30:07
|
| I tried also without a VPN Connection, by trying with a Server with external IP Address. I didn't notice changes, so timing is almost the same as vpn.So I am at the starting point: can anyone help me in identifying the reaason of the long latency in every query run remotely even if no data is returned? |
 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-09-09 : 10:03:01
|
| Connecting an application directly to a db server over any wide area link is probably a bad idea. I would be inclined to create a middle tier where the db server is and get the client to connect to the middle tier using .Net Remoting, Web Services etc.You should raise your latency problem with your VPN provider.ps. If you already have your application, and do not have too many remote users, you may want to consider using something like Citrix for the remote connections. |
|
|
|
brace77
Starting Member
25 Posts |
Posted - 2009-09-10 : 02:53:04
|
| Thanks a lot for the answer.Could you please shortly explain me the advantages in perfomance speed of a multitier apporoach related TO MY CASE? Can you please explain me why should I expect low latency with multitier? Because webservices are more performing? Otherwise said: which is the low-level reason why they are more performing than a direct server connection?Thanks again. |
|
|
|
brace77
Starting Member
25 Posts |
Posted - 2009-09-10 : 02:54:47
|
quote:
Originally posted by brace77
Thanks a lot for the answer.Could you please shortly explain me the advantages in perfomance speed of a multitier apporoach related TO MY CASE? Can you please explain me why should I expect low latency with multitier? Why webservices are more performing? Otherwise said: which is the low-level reason why they are more performing than a direct server connection?Thanks again.
|
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-09-10 : 05:10:38
|
| Not connecting directly to the db merely enables your application to cope better with high latency.Latency problems quite often occur with wide area links. It is well worth logging the latency to remote locations so if slow downs happen the problem can be quickly returned to your customer's network group and their ISP. |
|
|
|
brace77
Starting Member
25 Posts |
Posted - 2009-09-10 : 06:59:58
|
| "Not connecting directly to the db merely enables your application to cope better with high latency."==============You mean that an approach where I am not directly linked to the db, like all things that are not part of a transaction can be run parallely in threads and I should plan a UI that is not dependant on query results but that can load query results as they come available? Thanks. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2009-09-10 : 08:18:01
|
| You may want to consider Parallel and asynchonous processing but that would be a different topic.The main idea is that the middle tier handles the marshalling of the data to and from the client. This saves the db server from trying to open and maintain connections over slow and potentially unreliable links. (ie As far as the db server is concerned the application is local.) This is particularly true if you need to handle transactions in the application as long transaction times could severely effect the db server performance.As far as the db server is concerned, the same effect can be achived with remote control programs, Citrix etc, as the client is run locally and only the screens are moved to the remote location.The problem with remote control programs is the cost of the programs, the amount of hardware they require and application feeling sluggish to the user. If you write your own client, typeing etc is at normal speed and a certain amount of data can be cached so that communication to the middle tier occurs only when it needs to. Another alternative you may want to consider is a Web application, using something like ASP.NET. |
|
|
|
brace77
Starting Member
25 Posts |
Posted - 2009-09-11 : 03:13:17
|
| Thanks a lot for your clear explanation.My application is written for "everlasting connections". For sure the ASP.NET approach would be very good, but this means a full rewriting, something not doable at least now.I will consider which technology Delphi has to offer and which is the cost of implelemtation (Delphi has these technologies but I am not sure the implementation in my software will be possible without rewriting of 100% of DB routines... And in this case it is not a choice anyway).We are also using the Citrix approach but the problems are exactly the ones you mentinoed. Windows Server 2008 allows also to do what Citrix does, but the cost of licenses is high (without a reason, in my opinion). Thanks. |
|
|
|
|
Query execution latency on VPN?