Best Practices for Reducing Network Traffic – Part 2, Examples

In the second half of this two part podcast, Rob Steward provides examples of the impact reducing network traffic can have on database application performance. The podcast runs for 3:18.

Click on the following link to listen to the podcast: http://blogs.datadirect.com/media/RobSteward_ReducingNetworkTraffic_2.mp3

One of the examples we use in the book is you should be in manual commit mode as long as that makes logical sense for your application. What do I mean? The tip that we give in the book is every standards based data access API that you can think of, ODBC, JDBC, ADO.NET, OLE DB, they all by default put you in what’s called auto commit mode. Now what auto commit mode means is that when I execute a statement, it is automatically committed; therefore, I do not have to start transaction, execute a statement, and then do a commit. That just all happens when you begin transaction, the commit happens under the covers. I don’t see it as the application programmer. But on some databases that can incur an extra round trip because if the underlying database does not natively support an auto commit mode – some of them do, some of them do not – what those drivers, or those ODBC drivers, JDBC drivers, ADO.NET data providers, what they’re required to do by the specification is they have to issue those commits underneath the covers.

So if you do 1,000 inserts, what you may actually be incurring is 2,000 network round trips. One round trip for each insert, and then a successive round trip for a commit. Most programmers do not realize that that is the case. But if you go with the manual commit mode, where you explicitly – through the API say, ‘begin a transaction,’ you put this in your code, ‘give me 1,000 inserts,’ and then you explicitly, through that API, do a commit – now what you’ve done is instead of 2,000 network round trips, you have 1,002. So you’ve got a begin transaction, you’ve got 1,000 inserts, and then you’ve got a commit. That’s 1,002 versus 2,000 round trips in an auto commit, where even though you don’t realize it, you’re doing an insert and the driver underneath you is also issuing a commit as soon as that insert is done.

So, those network round trips can be a significant amount of time that you spend accessing data. I’ve seen cases where – particularly over a WAN, and I mentioned this earlier, or over a VPN, because of the package sizes of the latencies within those networks – when you tweak a few things like I just said, you may see 150% improvement on your overall performance because you’ve reduced those network round trips in half.

Is network traffic important to the overall performance of your application? Absolutely! It will always matter. It will matter even more in conditions where your network latency, like on the internet or across a wide area network or a VPN, where that network is a little bit slower than say your local area network within your building. Network traffic is a big deal. Reduce the number of round trips, and use all the techniques you can to reduce the actual size of the data coming across the wire.