Reporting and Network Latency issues

100Mbps notice the b is small it means Bits . 8 Bits make a byte so 100Mbs/8 = 12.5 MBps( 12 Mega bytes per second--size of music file)

In a local LAN network maximum speed is about 100 Mbps which means 12.5MB per second. Considering you are using the entire connection to your self.

Best possible speed of Copper wire in LAN is 1000Mbps which means 125MB per second.

Fiber optic cable maximum speed upto 100Gbps -- 12.5 GB ( Giga byte per second -- Entire movie trilogy in one second)

Note --Above is theoritical numbers, we will have delays due to network card and other latency issues

If I connect from my machine in india using reporting tool to database server in US .It takes more time 300second compared to 30 seconds when developer in US is connecting from local machine reporting tool to database.

Answer --- This is due to delay of 300ms when connecting from india to US db server. For details how 300ms delay causes page to wait for 6 min you need to understand below

Basics - Signal is transmitted from india to US via a under sea link. Or in some cases through satelite link. There are many companies which operate under sea link (check in google) and point from where these undersea link start in india.

Note -- propogation delay would be there even if you only person using this link.

Propagation delay - distance /speed of signal in copper wire or fibre optic in KM ---

so from india to US ----- 5400/197863 = 23ms delay

Serialization delay --- packet size in bits/transmission rate in bits per second

Usually packet sizes are in KB so only if your packet size is big in MB. It will be a big issue OR if your transmission rate is low that is you are having 50kbps phone modem link. It will be a MAJOR issue

Latency due to TCP ---Transmission control protocol . Key point how it works are

1. Handshake between source and destination

2. Received acknowledgement

3. Requesting missing bits
4. Windowing technique -- Adjusting the transmission rate depending on the ability of network to handle it. So for high latency network though bandwidth is there it will send lesser data in a window

Most import point -- So when TCP is transfering, if there is a delay of 100 ms (it is always round trip delay) because TCP always waits for acknowledgment before next packet is sent.

Hey , All this is in milliseconds not even seconds. So why should we bother??

Link having 50 ms (milli seconds)  gives page in 3 seconds

Link having 300ms gives page in 11 seconds.

So user will not be pleased with 11 seconds

How to check the round trip time for connecting from your computer

Ping your database server and notice the RTT round trip time. You want it to be within 100ms at the maximum

Now check the RTT round trip time from your application server

Question -- Sql server returns data in few minutes but reporting tool takes 10 min to return full data.

Ans -- TCP protocol adjust the transmission speed on the speed of the tool that is how fast is it able to take the data supplied by the source. So since sql developer does no processing of its own . It just stores file in excel . The output is much faster compared to reporting tool which builds its own file. The speed at which tool is able to build file affects the speed at which data is transferred from source