Using SQL DataTables to speed up database performance

The issue with performing multiple SQL commands within a for loop.

If like me, you've found yourself with a web-based project that requires operations on hundreds of records simultaneously, you might have found yourself running a SQL insert/update/delete command for every one, but with the use of a C# DataTable, you'll be able to perform an action on all items with one command and it's a fairly simple change to get it all set up.

So an example of your slow running code might be something like this:

using (SqlCommand objCmd = new SqlCommand())
{
    objCmd.Parameters.Add("@itemGUID", SqlDbType.UniqueIdentifier).Value = objPlan.GUID;
    objCmd.Parameters.Add("@itemDate", SqlDbType.DateTime).Value = DateTime.Now;
    objCmd.Parameters.Add("@parentGUID", SqlDbType.UniqueIdentifier).Value = objPlan.Parent.GUID;
                    
   // Execute procedure and perform any other tasks
   // ...
}

That code will more than likely be wrapped in a for/foreach loop and the parameters would be updated using the object given in the loop. That's fine for a small number of operations, but that's a lot of SQL connections if you're doing this for hundreds of objects at the same time and you'll more than likely run into performance issues because of the round trip time for each connection, even if the SQL server is on the same box.

Building a DataTable to use as a SQL command object parameter.

The good news is that we can use a User Defined Table Type as a parameter on a single connection provide an entire table of records held in memory over to a stored procedure. It does introduce a little more complexity on the SQL Server instance, but not too much.

See the definition of a User Defined Table Type as described on the Microsoft TechNet website:

'In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function. For more information about how to define a table structure, see CREATE TABLE (Transact-SQL).'

Within your C# code, you'll need to change the above code to something like this...

using (SqlCommand objCmd = new SqlCommand())
{
    // Create a new DataTable - It's a lot quicker than doing each row individually.
    using (DataTable objDT = new DataTable())
    {
        // Define the Columns within the new DataTable
        objDT.Columns.Add("itemGUID", typeof(Guid));
        objDT.Columns.Add("itemParentGUID", typeof(Guid));
        objDT.Columns.Add("itemDate", typeof(DateTime));

        // Add each line to the new DataTable and make sure there are no duplicates.
        foreach (DeletedItem objDeletedItem in colNewToServerDeletedItems.GroupBy(x => x.ItemGUID).Select(y => y.First()))
        {
            objDT.Rows.Add(
                objDeletedItem.ItemGUID,
                objDeletedItem.ItemParentGUID, 
                objDeletedItem.Date
            );
        }

        // Add the DataTable to the command object.
        SqlParameter dtparam = objCmd.Parameters.AddWithValue("@myRecords", objDT);
        dtparam.SqlDbType = SqlDbType.Structured;

        // Execute procedure and perform any other tasks
        // ...
    }
}

Simple eh? We've defined a table within C# and added the rows to it by iterating the objects within our collection, then we execute the stored procedure as normal.

There are a couple of other changes we need to make this happen though. Remember I said above that it will add a little complexity to your SQL Server instance? Well, we're going to need to define the User Defined Table Type within the database and then add it as a parameter to the stored procedure that we expect to runt he operation.

Creating a SQL User Defined Table Type.

Whilst you can create a User Defined Table Type by coding the create script by hand, I usually find that because we're more than likely just going to perform a CRUD operation or more specifically just the C, U and D parts of a CRUD on the data, we can script the User Defined Table Type straight from the table it'll be used to manipulate.

Within SQL Server Management Studio, right-click the table we'll be performing operations on and expand 'Script Table as', then expand 'CREATE to' and click on 'New Query Editor Window', your scripting work has now been done for you, you just need to remove and constraints and change 'CREATE TABLE [dbo].[mytablename]' to 'CREATE TYPE [dbo].[mytabletypename] AS TABLE'.

Your script to create your User Defined Table Type should now look something like this.

CREATE TYPE [dbo].[mytabletypename] AS TABLE(
	[itemGUID] [uniqueidentifier] NOT NULL,
	[itemParentGUID] [uniqueidentifier] NOT NULL,
	[itemDate] [datetime] NOT NULL)
GO

Go ahead and execute that code and now it's just a matter of editing the stored procedure to use the new table type.

Structuring a stored procedure to use a User Defined table Type as a parameter.

Create a stored procedure similar to the one below, it must match your table and table type names and structures.

Note: The table type parameter must be read only.

CREATE PROCEDURE [dbo].[myprocedure]
	-- User defined table type as parameter
	@myRecords AS dbo.mytabletypename READONLY,
	-- Result
	@Return int OUTPUT
AS
SET NOCOUNT ON;
BEGIN TRANSACTION 

SET @Return = 1;

INSERT INTO mytablename (itemGUID, parentGUID, itemDate)
	SELECT itemGUID, parentGUID, itemDate
		FROM @myRecords

-- Execute
IF (@@error <> 0 OR @Return = -1) 
	BEGIN
		SET @Return = -1
		ROLLBACK TRANSACTION 
	END
ELSE
	BEGIN
	  COMMIT TRANSACTION
	END

Notice that the User Defined Table Type we defined earlier has been given as a parameter and we're able to perform select operations on it as if it were a real table. Using this select, we can insert every row provided into the actual table within the database.