Same application running with different speed in different Countries using same database server ?

From the title of your post I'd say that two applications that are geographically distant are connected to exactly the same third computer which hosts the database. In such configuration, speed at which you observe database operations is greatly determined by the network round-trip between the application and the database.

Consider simplest case of a SELECT statement issued to the database server:
1. App sends packet over network specifying the SELECT statement
2. Database server sends back first packet containing, say, 50 rows of the result
3. Database server sends back second packet containing, say, remaining 30 rows of the result; this packet may include information that there are no more packets to follow.

In this case, total time required to execute the statement is this:
1. Time spent by the request to reach database server (say half of the round-trip)
2. Time spent by database server required to execute SELECT statement and produce the first row of response (so-called time-to-first-byte, but only limited to the server this time)
3. Time required for data to reach from database server to application (say again half of the round-trip)
4. Time required to push all data to the network from the database server side - this is total size of data divided by network speed (e.g. 2 MB of data in response, sent over 100 Mbit network would roughly take 160 msec).

From this analysis, SELECT statement response would be available to the application in time which is sum of these:
1. Network round-trip
2. Execution time at the database server
3. Time required to write data into given network, which depends on the bandwidth

In more complex cases application might have to communicate with database more than once in order to get complete response (this is the case with CLOBs in Oracle). So round-trip might be included couple of times into this sum, which further devastates the results.

For all these reasons, it is always suggested to keep applications near the database. If database has to be located elsewhere, it is suggested to create a service which is near the database, e.g. XML Web service. Then remote applications would not connect directly to database but rather to the Web service, and then in such way that communication is so-called coarsely grained. This means that application sends exactly one request to the service, but request may be quite complex. Then service does quite complex work with the database, which includes many very short round-trips to the database. Finally, service creates complex response object and sends it back to the application.

Hope this explains things with your application.

Zoran

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ajay Gautam15y ago
Thanks for reply.

Our database and application both are in same country means in country B on same machine
Queries/sp's are fine, they are taking 3 seconds to return the records

Database content are same in both databases and country b has only 1 database and country has 2 databases.

Any other idea?
Zoran Horvat15y ago
P.S. Try to ping the database server from application servers and paste the results here (you may omit IPs if you find it inconvenient to appear on the forum).

Zoran
Zoran Horvat15y ago
From the title of your post I'd say that two applications that are geographically distant are connected to exactly the same third computer which hosts the database. In such configuration, speed at which you observe database operations is greatly determined by the network round-trip between the application and the database.

Consider simplest case of a SELECT statement issued to the database server:
1. App sends packet over network specifying the SELECT statement
2. Database server sends back first packet containing, say, 50 rows of the result
3. Database server sends back second packet containing, say, remaining 30 rows of the result; this packet may include information that there are no more packets to follow.

In this case, total time required to execute the statement is this:
1. Time spent by the request to reach database server (say half of the round-trip)
2. Time spent by database server required to execute SELECT statement and produce the first row of response (so-called time-to-first-byte, but only limited to the server this time)
3. Time required for data to reach from database server to application (say again half of the round-trip)
4. Time required to push all data to the network from the database server side - this is total size of data divided by network speed (e.g. 2 MB of data in response, sent over 100 Mbit network would roughly take 160 msec).

From this analysis, SELECT statement response would be available to the application in time which is sum of these:
1. Network round-trip
2. Execution time at the database server
3. Time required to write data into given network, which depends on the bandwidth

In more complex cases application might have to communicate with database more than once in order to get complete response (this is the case with CLOBs in Oracle). So round-trip might be included couple of times into this sum, which further devastates the results.

For all these reasons, it is always suggested to keep applications near the database. If database has to be located elsewhere, it is suggested to create a service which is near the database, e.g. XML Web service. Then remote applications would not connect directly to database but rather to the Web service, and then in such way that communication is so-called coarsely grained. This means that application sends exactly one request to the service, but request may be quite complex. Then service does quite complex work with the database, which includes many very short round-trips to the database. Finally, service creates complex response object and sends it back to the application.

Hope this explains things with your application.

Zoran