Enzo SQL Shard

This library provides a Shard technology that allows you to spread the load of database queries over multiple databases easily (SQL Server and SQL Azure). Uses the Task Parallel Library (TPL) and caching for high performance.

ANNOUNCEMENT: ENZO SHARD 2.0 BETA IS NOW AVAILABLE FOR PREVIEW. THE BETA ADDS SUPPORT FOR SQL AZURE FEDERATION AND DISTRIBUTED QUERIES. LOOK FOR V2.0 IN THE DOWNLOADS SECTION, OR CLICK HERE http://enzosqlshard.codeplex.com/releases/view/72791.

For more information about our company, visit http://www.bluesyntax.net.

I published a White Paper about data scalability here: http://www.bluesyntax.net/files/enzoframework.pdf. The white paper introduces three types of scaling models: compressed, linear and expanded. This library helps you build an expanded shard. To build a linear shard or a compressed shard, visit https://scale.bluesyntax.net.

Details

This library allows you to perform all the usual tasks in database management: create, read, update and delete records. Except that your code can execute against two or more databases seemlessly. Using the usual SqlCommand object, spread the load of your commands to multiple databases to improve performance and scalability. This library uses a Horizontal Partion Shard, which requires your tables to be partitionned horizontally. The download comes with a sample application that shows you how to use the shard library and gives you execution time in milliseconds of your commands.

Here is a sample code that allows you to fetch records from the USERS table across multiple databases. As you can see, extension methods on the SqlCommand object makes this program easy to read; new Shard methods have been added to SqlCommand that allow you to execute reads, writes and insert operations in parallel.

  SqlCommand cmd = new SqlCommand();

  // Set the parallel option
  PYN.EnzoAzureLib.Shard.UseParallel = true;
  PYN.EnzoAzureLib.Shard.UseCache = true;

  cmd.CommandText = "SELECT * FROM USERS";
  DataTable data = cmd.ExecuteShardQuery();
  dataGridView2.DataSource = data;

A sample application is provided in this project allowing you to see the shard definition. Each database making up the shard is shown in this screen. The databases are loaded from a configuration file, or can be added manually through the user interface.

Caching

This library allows you to selectively cache your records so you do not have to perform additional roundtrips to the database. The caching library used is from the Enterprise Library, which is now included in .NET 4.0.

Task Parallel Library (TPL)

And for additional performance, the library uses the TPL, which is also part of .NET 4.0. You can turn this feature on or off and see the impact on performance when you query your Shard using parallel threads.

Round Robin Inserts

And if you need to insert many records in the shard, you can use the ExecuteParallelRoundRobinLoad method, which takes a List of command objects and spreads their execution in a round-robin manner to all the underlying databases in the shard.

Performance

The shard library can improve performance drastically depending on the type of data and number of records you have. For example the screenshot below shows that the application is returning 2 records in 2.6 seconds (each record contains a PDF file of a few megabytes each). The second image shows the same two records, however each record is stored in a separate database. Since fetching records from a shard uses multithreading, the same two records took 1.4 seconds to be fetched. You can see the database GUID being added dynamically from the shard library showing you which database each record came from.

Requirements

This library runs only with the .NET 4.0 framework.