USE FEDERATION statement gets improved performance

June 17th, 2012 Add Your Comments

During the presentation held at the 2012 TechEd event and later on his blog, Cihan Biyikoglu made the announcement that the Windows Azure SQL Database team (former Azure SQL team) has recently improved the latency of the USE FEDERATION statement. That was possible by implementing two layers of caching.

The first layer is a connection pool implemented at Gateway level (in Azure SQL, the Gateway is a physical layer that stands in front of database instances and manages things like connections, translations from logical to physical names and a few other services; more details here).

The second layer is a caching of the Federations map. The information needed to map a federation key value to a federation member is being held in the root database, so the USE FEDERATION statement would normally need to go to the root in order to get that info, but with the Federations map caching in place, the root database is longer hit.

A few month ago, after benchmarking the performance of federated SQL Azure databases, I wrote about the fact that the USE FEDERATION statement proved to be quite costly in terms of performance (my full article is available here). My test was a bit special compared to real world scenarios, in the sense that it consistent in a very big number of very small operations, which translated in a much bigger amount of connections per second than in usual scenarios. Still, the problem existed and the fact that the Windows Azure SQL Database team took action to fix it is really good news.

The USE FEDERATION statement is one of the great things supplied with SQL Azure Federations. Not only is it a very convenient way to route to the correct database based on the federation key value, but it is also a great way of avoiding connection pool fragmentation.

Comments are closed.