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

Question

Hi All I am using infomix database in Country A. I made an application using VB6 which is working fine in Country A. Now same database is used in Country B. But in this case my application working very slow. What can be reason behind slow speed of same exe in Country B ? How can i troubleshoot with speed? Any suggestions? Thanks Ajay

Ajay Gautam · Answer

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 Horvat · Answer

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 Horvat · Answer

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