SQL & CLR Base64 Conversion Performance

Article
02/26/2009

As a follow-up to my previous post I thought I would have a look at the performance of this type of conversion using the XML query and T-SQL type conversions versus the equivalent code written in C# and executed by the SQL CLR engine.

The first thing was to convert the code to the equivalent T-SQL functions:

 CREATE FUNCTION ConvertToBase64SQL(@toEncode varchar(max)) RETURNS varchar(max)
  AS
    BEGIN
        DECLARE @xml xml
        DECLARE @bin varbinary(max)
        SELECT @bin = CAST(@toEncode as varbinary(max))
        SET @xml = (SELECT * FROM (SELECT @bin as data) as row FOR XML AUTO,  BINARY BASE64)
        RETURN @xml.value('(/row/@data)[1]', 'varchar(max)')
    END

 CREATE FUNCTION ConvertFromBase64SQL(@toDecode varchar(max)) RETURNS varchar(max)
  AS
    BEGIN
        DECLARE @xml xml
        DECLARE @bin varbinary(max)
        SELECT @xml = CAST( '<row data="' + @toDecode + '" />' as xml )
        SELECT @bin = @xml.value('(/row/@data)[1]', 'varbinary(max)')
        RETURN CAST(@bin as varchar(max))  
  END

Secondly to create the equivalent code in C# and deploy this to the SQL instance:

     [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertToBase64CLR(SqlString toEncode)
    {
      byte[] toEncodeAsBytes = System.Text.ASCIIEncoding.ASCII.GetBytes(toEncode.ToString());
      SqlString returnValue = System.Convert.ToBase64String(toEncodeAsBytes);
      return returnValue;
    }

     [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString ConvertFromBase64CLR(SqlString toDecode)
    {
        byte[] DecodedBytes = System.Convert.FromBase64String(toDecode.ToString());
        SqlString returnValue = System.Text.ASCIIEncoding.ASCII.GetString(DecodedBytes);
        return returnValue;
    }

Finally compare the performance:

 DECLARE @Counter INT = 100000
DECLARE @Output varchar(max)
DECLARE @StartTime DateTime = GetDate()
WHILE @Counter > 0 
BEGIN 
    SET @Output =  dbo.ConvertFromBase64SQL(dbo.ConvertToBase64SQL('This is a small test message'));
    SET @Counter = @Counter  - 1
END
PRINT @Output
PRINT 1.0*DATEDIFF(MS,@StartTime,GetDate())/1000
-- 25.440000 seconds

 DECLARE @Counter INT = 100000
DECLARE @Output varchar(max)
DECLARE @StartTime DateTime = GetDate()
WHILE @Counter > 0 
BEGIN 
    SET @Output =  dbo.ConvertFromBase64CLR(dbo.ConvertToBase64CLR('This is a small test message'));
    SET @Counter = @Counter  - 1
END
PRINT @Output
PRINT 1.0*DATEDIFF(MS,@StartTime,GetDate())/1000
-- 2.663000 seconds

The results indicate that whilst the C# CLR implementation is significantly faster, the benefit of this performance difference (almost 10 times faster) it would likely only have a real impact if there where a large number of documents being converted.

I have not run this test on any larger documents which may result in a different performance results.

I think the real point here is that today there are a number of ways of doing these types of operations and you need to keep your mind open and evaluate the performance of your chosen implementation.

<Gary>

SQL & CLR Base64 Conversion Performance

Additional resources