DATABASE

SQL Azure: Building a Shard (part 4) - Updating and Deleting Records in the Shard & Adding Records to the Shard

1/31/2011 5:27:51 PM

5. Updating and Deleting Records in the Shard

At this point, you're ready to see how updates and deletes take place through the shard. Updates and deletes against the databases in the shard can either be performed for records in a given database or against all databases. At a high level, here are some guidelines you can use to decide on an approach:

  • Update or delete records in a single database. You update or delete one or more records in a database when you already know the database GUID to use. This is the case when you use the shard to retrieve records, because the database GUID is provided for all records returned.

  • Update or delete records across databases. Generally speaking, you update or delete records across databases in the shard whenever you don't know which database a record is in, or when all records need to be evaluated.

To update or delete records in a single database, you must provide a command parameter that contains the database GUID to use. Here's the code that updates a single record in the shard. On lines 1 through 7, the code creates a command object that calls a stored procedure that requires two parameters. On line 9, the code adds the database GUID to use. This extra parameter is removed by the shard library before making the call to the requested database:

1) cmd.CommandText = "sproc_update_user";
2) cmd.CommandType = CommandType.StoredProcedure;
3)
4) cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
5) cmd.Parameters["@id"].Value = int.Parse(labelIDVal.Text);
6) cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 20));
7) cmd.Parameters["@name"].Value = textBoxUser.Text;
8)
9) cmd.Parameters.Add(new SqlParameter(
PYN.EnzoAzureLib.Shard._GUID_, labelGUID.Text));
10)
11) ExecuteShardNonQuery (cmd);

Note that calling a stored procedure isn't required for this code to run. All that is required is that a SqlCommand object be used; the SQL code may very well be inline SQL.

Deleting a record from the shard is virtually identical. The command object is created with the required stored procedure parameters from lines 1 through 5. On line 7, the code adds the database GUID to use:

1) cmd.CommandText = "sproc_delete_user";
2) cmd.CommandType = CommandType.StoredProcedure;
3)
4) cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
5) cmd.Parameters["@id"].Value = int.Parse(labelIDVal.Text);
6)
7) cmd.Parameters.Add(new SqlParameter(
PYN.EnzoAzureLib.Shard._GUID_, labelGUID.Text));
8)
9) ExecuteShardNonQuery (cmd);

NOTE

The ExecuteShardNonQuery method behaves differently if it has no database GUID parameter (it executes the query against all databases), if it has a database GUID parameter with a value (it executes the query against the specified database), or if it contains a database GUID parameter with a NULL value (it executes the query against the next database in the shard using round-robin). You see how to use round-robin calls when adding records in the shard shortly.

Figure 7 shows the sample application updating a record from the shard. When you click Reload Grid, a SELECT statement is issued against the shard, which returns the database GUID for each record. Then, when you select a specific record, the record details are loaded in the right section of the screen, along with the record's database GUID. At this point, the record can be updated or deleted.

Figure 7. Sample application updating a record in the shard

Because records are being updated or deleted, the client code clears the cache to force future SELECT statements to fetch records from the databases in the shard. The shard library exposes a ResetCache method that does just that. You can improve this logic by also performing the same update or delete operation of records in the cache.

Updating or deleting records across databases in the shard is even simpler. The following code executes an inline SQL statement using a SqlCommand object. Because no database GUID is provided, this statement executes the statement across all databases in the shard. When you perform updates or deletes across databases, it's important to set the parallel flag correctly, as shown on line 1:

1) PYN.EnzoAzureLib.Shard.UseParallel = checkBoxParallel.Checked;
2) cmd.CommandText = "UPDATE TestUsers2 SET LastUpdated = GETDATE()";
3) cmd.CommandType = CommandType.Text;
4) ExecuteShardNonQuery (cmd);

6. Adding Records to the Shard

You see how easy it is to add records to the shard databases. This shard works best from a performance standpoint when all databases in the shard have a roughly equal number of records; this is because parallel processing is performed without any deterministic logic. As a result, the more spread out your records are in the shard, the faster it is. You can add records in the shard in two ways:

  • In a single database. If you're loading the shard for the first time, you may decide to load certain records in specific databases. Or you may decide to load one database with more records than others, if the hardware is superior.

  • Across databases. Usually, you load records in the shard without specifying a database. The shard library uses a round-robin mechanism to load records.

Adding a record in a specific database is no different than updating or deleting a record in a database; all you need to do is create a SqlCommand object, set the INSERT statement, and add a SqlParameter indicating the database GUID to use.

Adding one or more records across databases requires a slightly different approach. The round-robin logic stores the last database used to insert records in the shard. The shard library exposes two methods to perform inserts:

  • ExecuteShardNonQuery. As you've seen previously, this method extends the SqlCommand object and executes statements against the next database in the shard (round-robin) if the GUID parameter is NULL. This convention is used to let the shard library know that it should move its internal database pointer to the next database in the shard for the next round-robin call.

  • ExecuteParallelRoundRobinLoad. This method extends List<SqlCommand> and provides a mechanism to create a collection of SqlCommand objects. Each SqlCommand object contains an INSERT statement to execute. This method adds a NULL database GUID and calls ExecuteShardNonQuery to execute all the statements with round-robin support. This construct simplifies loading a shard quickly by spreading INSERT statements evenly across all databases.

The following code shows how the client prepares the call to ExecuteParallelRoundRobinLoad. Line 1 creates a collection of SqlCommand objects. Then, on line 3, an outer loop executes for each value found in the userName array (this is a list of names to add to the shard). From lines 5 to 16, a SqlCommand object is created for each name to INSERT and is added to the collection. Line 22 makes the actual call to ExecuteParallelRoundRobinLoad. Finally, on line 23, if all goes well, the library's cache is cleared:

1) List<SqlCommand> commands = new List<SqlCommand>();
2)
3) foreach (string name in userName)
4) {
5) if (name != null && name.Trim().Length > 0)
6) {
7) SqlCommand cmdToAdd = new SqlCommand();
8) cmdToAdd.CommandText = "sproc_add_user";
9) cmdToAdd.CommandType = CommandType.StoredProcedure;
10)
11) cmdToAdd.Parameters.Add(
12) new SqlParameter("name", SqlDbType.NVarChar, 20));
13) cmdToAdd.Parameters["name"].Value = name;
14)
15) commands.Add(cmdToAdd);
16) }
17) }
18)
19) // Make the call!
20) if (commands.Count > 0)
21) {
22) commands.ExecuteParallelRoundRobinLoad();
23) Shard.ResetCache();
24) }

NOTE

The call to ExecuteParallelRoundRobinLoad is different in two ways from all the other methods you've seen so far. First, there is no need to add the database GUID parameter; it creates this parameter automatically with a NULL value. Second, this method executes on a List<SqlCommand> object instead of SqlCommand.

Figure 8 shows the sample application screen that creates the array of names to load in the shard. Six names are added in the shard using round-robin, as previously described.

Figure 8. Sample application adding records using round-robin

 
Other  
  •  SQL Azure: Building a Shard (part 3) - Reading Using the Shard & Caching
  •  SQL Azure: Building a Shard (part 2) - Managing Database Connections
  •  SQL Azure: Building a Shard (part 1) - Designing the Shard Library Object
  •  SQL Azure: Designing for High Performance - General Performance Concepts
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Creating and Altering Tables
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Managing Internationalization Considerations
  •  Programming Microsoft SQL Server 2005 : Deployment (part 2) - Testing Your Stored Procedures
  •  Programming Microsoft SQL Server 2005 : Deployment (part 1) - Deploying Your Assembly
  •  Programming Microsoft SQL Server 2005 : CLR Stored Procedures and Server-Side Data Access
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 3) - Using the Resource Governor
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 2)
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Controlling Execution Plans (part 1)
  •  Reporting Services with SQL Azure : Deploying the Report & Creating a Subreport
  •  Reporting Services with SQL Azure : Creating the Report Design
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 2) - Using RANK, DENSE_RANK and NTILE
  •  SQL Server 2008 : Explaining Advanced Query Techniques - Applying Ranking Functions (part 1) - Using ROW_NUMBER
  •  SQL Server 2008 : Demystifying Data Types - Computed Columns
  •  Programming Microsoft SQL Server 2005: Overview of SQL CLR - Visual Studio/SQL Server Integration
  •  Programming Microsoft SQL Server 2005: DDL Triggers and Notifications
  •  Programming Microsoft SQL Server 2005: Enabling CLR Integration
  •  
    Top 10
    Exchange Server 2010 : Active Manager - Automatic database transitions & Best copy selection
    Exchange Server 2010 : Breaking the link between database and server
    iPhone 3D Programming : Drawing an FPS Counter (part 2) - Rendering the FPS Text
    iPhone 3D Programming : Drawing an FPS Counter (part 1) - Generating a Glyphs Texture with Python
    Mobile Application Security : Mobile Geolocation - Geolocation Methods & Geolocation Implementation
    Mobile Application Security : SMS Security - Application Attacks & Walkthroughs
    Transact-SQL in SQL Server 2008 : Table-Valued Parameters
    Transact-SQL in SQL Server 2008 : New date and time Data Types and Functions
    Windows 7 : Working with User Accounts (part 2)
    Windows 7 : Working with User Accounts (part 1)
    Most View
    Dynamically Downloading Content with Silverlight
    Registry ACLs
    Server-Side Browser Detection and Content Delivery : Mobile Detection (part 3) - Transcoders
    Management Tools in SQL Server 2008
    Embed a Web Browser in Your Application
    ASP.NET State Management Techniques : The Role of the Global.asax File
    SQL Azure: Building a Shard (part 2) - Managing Database Connections
    Algorithms for Compiler Design: SYNTAX-DIRECTED TRANSLATION SCHEMES TO SPECIFY THE TRANSLATION OF VARIOUS PROGRAMMING LANGUAGE CONSTRUCTS
    Sharepoint 2007: Upload a File - Upload a File from the Web Interface
    Windows Phone 7 Development : Push Notifications - Implementing Tile Notifications
    ASP.NET AJAX Extensions : Selective Page Updates with Partial Rendering
    Security Changes in IIS 7.0 : Reducing Attack Surface Area (part 1)
    Caching Page Content
    The Language of Apple Platforms : Exploring the Objective-C File Structure
    Send an Email via SMTP
    CSS for Mobile Browsers : CSS Techniques
    Search for a File or Directory
    Programming .NET Security : Asymmetric Encryption Explained (part 2) - Creating the Encrypted Data
    Exchange Server 2010 : Utilize the Availability Options for Servers Based on Role (part 2) - Increase Mailbox Database Availability
    Programming Symmetrical Encryption (part 3) - Encrypting and Decrypting Data