SQL Azure Federation - how much data before performance benefits?
- by Donald Hughes
To avoid premature optimization, I don't want to implement SQL Azure's Federation too early. Is there a rule of thumb for how much data a table would need to have before seeing performance benefits from sharding? I know there won't be a precise answer as there are too many variables to consider, especially with much of SQL Azure's resources being hidden/unknown.
To put it into several, more concrete examples, would Federation improve performance in any of the below table scenarios:
100,000 rows (~ 200 MB)
1,000,000 rows (~ 2 GB)
10,000,000 rows (~ 20 GB)
100,000,000 rows (~ 200 GB)
For the sake of elaboration, we can assume this is the largest table that would be federated, which consists of order details, which is joined to an orders table with a 'customer_id' foreign key, which would be the distribution key.
This is a fairly standard multi-tenant, CRUD order entry system, with a typical assortment of reporting needs (customer order totals by day/month/year, etc).
