A View Inside My Head

Tuesday at the NWNUG meeting, Steven Smith spoke on various ways to squeeze performance out of your ASP.NET applications.  This was a fantastic talk, and gave me plenty to think about (since ASP.NET is not my forte, I only consider myself to have an intermediate skillset on this topic).

One suggestion that he made involved caching database writes.  That is, instead of immediately writing logging-type information to the database for every request, which is a relatively expensive operation considering the small payload size, that you could accumulate them in a short-term cache, and then perform the write operation periodically.  Fewer database calls = faster performance.

In his example, he spoke of his advertisement server that might serve many impressions per second, but he doesn't want each impression to incur an expensive database write.  So, he keeps track of the activity locally, and then persists to the database every 5 seconds using a single database call containing multiple data points.

The code that Steve demonstrated utilized XML to contain the data within a single block of text (read: can be passed in as a single parameter to a stored procedure):

Now, consuming XML from T-SQL is an area that I know very well, so I cringed a little bit when Steve showed the actual stored procedure code itself:

CREATE PROCEDURE dbo.BulkLogCustomerViews
   @@doc text -- XML Doc...
AS

DECLARE @idoc int

-- Create an internal representation (virtual table) of the XML document...
EXEC sp_xml_preparedocument @idoc OUTPUT, @@doc

-- Perform UPDATES
UPDATE TopCustomerLog
SET TopCustomerLog.ViewCount = TopCustomerLog.ViewCount 
    + ox2.viewCount
FROM OPENXML (@idoc, '/ROOT/Activity',1)
          WITH ( [customerId]  NCHAR(5)
        , viewCount int
        ) ox2
WHERE TopCustomerLog.[customerId] = ox2.[customerId]
 
-- Perform INSERTS
INSERT INTO TopCustomerLog
     ( CustomerID
     , ViewCount
     )
SELECT [customerId]
     , viewCount
FROM OPENXML (@idoc, '/ROOT/Activity',1)
          WITH ( customerId  NCHAR(5)
        , viewCount  int
        ) ox
WHERE NOT EXISTS (SELECT customerId FROM TopCustomerLog 
                  WHERE TopCustomerLog.customerId = ox.customerId)

-- Remove the 'virtual table' now...
EXEC sp_xml_removedocument @idoc
 

Now, to Steve's credit, this code works just fine, and can probably be used as-is on all versions of SQL Server from 7.0 through 2008.  But, since we really don't write ASP applications consisting entirely of Response.Write any longer, I'd like to see Steve update his demo to use more modern techniques on the database as well.  ;-)

The first thing that he could do is update the procedure to utilize the XML data type that was first introduced in SQL Server 2005.  This would simplify the code a little bit, and would get rid of the dependency on the COM-based MSXML.dll, which the sp_xml_preparedocument and OPENXML() uses.

CREATE PROCEDURE dbo.BulkLogCustomerViews
   @doc xml
AS

-- Perform UPDATES
UPDATE TopCustomerLog
SET    TopCustomerLog.ViewCount = TopCustomerLog.ViewCount 
       + ox2.viewCount
FROM   (
       SELECT T.activity.value('@customerId', 'nchar(5)') 
              as CustomerID, 
              T.activity.value('@viewCount', 'int') viewCount
       FROM   @doc.nodes('/ROOT/Activity') as T(activity)
       ) ox2
WHERE  TopCustomerLog.[customerId] = ox2.[customerId]
 
-- Perform INSERTS
INSERT INTO TopCustomerLog
     ( CustomerID
     , ViewCount
     )
SELECT [customerId]
     , viewCount
FROM (
       SELECT T.activity.value('@customerId', 'nchar(5)') 
              as CustomerID, 
              T.activity.value('@viewCount', 'int') viewCount
       FROM   @doc.nodes('/ROOT/Activity') as T(activity)
       ) ox
WHERE NOT EXISTS ( SELECT customerId FROM TopCustomerLog 
                   WHERE TopCustomerLog.customerId = ox.customerId )

Note that the XML data type in SQL Server doesn't need to be a well-formed document.  In this case, Steve could just pass in series of "Activity" elements (no "ROOT" element would be required by SQL Server, so he would also be able to simplify the .NET code that actually creates the XML string):

<Activity customerId="ALFKI" viewCount="5" />
<Activity customerId="ANATR" viewCount="7" />

Consequently, the XPath (XQuery, actually) within the nodes() method of the stored procedure code would need to change as well:

But, we can kick this up a notch and use some SQL Server 2008 features as well. First, there's new "Upsert" capabilities (MERGE statement) that tries to simplify what Steve does with the UPDATE followed by INSERT:

CREATE PROCEDURE dbo.BulkLogCustomerViews
   @doc xml
AS

MERGE TopCustomerLog AS target
USING (SELECT T.activity.value('@customerId', 'nchar(5)') 
              as CustomerID, 
              T.activity.value('@viewCount', 'int') as viewCount
       FROM   @doc.nodes('Activity') as T(activity)) AS source 
ON    (target.CustomerID = source.CustomerID)
WHEN  MATCHED 
      THEN UPDATE SET target.ViewCount = target.ViewCount + source.viewCount
WHEN NOT MATCHED
      THEN INSERT (CustomerID, ViewCount)
      VALUES (source.CustomerID, source.viewCount);

One more thing that could be done to further simplify this T-SQL is to use a Table-valued Parameter instead of the XML.  This would allow Steve to pass a fully populated table of data into the stored procedure and consume it directly by the MERGE statement.

The first step is to create a T-SQL type that defines the table structure of the parameter (this is a one-time operation, unless the table structure changes):

CREATE TYPE CustomerViewType AS TABLE 
( 
    CustomerID nchar(5) NOTNULL,
    ViewCount intNOTNULL
);

Now, a parameter can be defined of this type, and used just like any other table-value variable:

ALTER PROCEDURE dbo.BulkLogCustomerViews
   @views CustomerViewType READONLY
AS

MERGE TopCustomerLog AS target
USING @views AS source 
ON    (target.CustomerID = source.CustomerID)
WHEN  MATCHED 
      THEN UPDATE SET target.ViewCount = target.ViewCount 
                      + source.viewCount
WHEN NOT MATCHED
      THEN INSERT (CustomerID, ViewCount)
      VALUES (source.CustomerID, source.viewCount);

On the ADO.NET side, the table-valued parameter could be represented as a DataTable object (other options also exist), and can be assigned directly as the value of the stored procedure's parameter object:

// Create a data table, and provide its structure
DataTable customerViews = new DataTable();
customerViews.Columns.Add("CustomerID", typeof(string));
customerViews.Columns.Add("ViewCount", typeof(int));

// Fill with rows
customerViews.Rows.Add("ALFKI", 5);
customerViews.Rows.Add("ANATR", 7);

using (SqlConnection conn = new SqlConnection("..."))
{
  SqlCommand cmd = conn.CreateCommand();
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.CommandText = "dbo.BulkLogCustomerViews";

  SqlParameter param 
        = cmd.Parameters.AddWithValue("@views", customerViews);

  conn.Open();

  cmd.ExecuteNonQuery();
}