SQLCLR Assembly Much Faster When Unloaded

Question

I have a SQLCLR assembly that does a simple JSON deserialization using the LitJson package on a SQL Azure Managed Instance. This CLR is called from a table-valued function that just returns the JSON properties as a table (in theory faster than the built-in JSON handling in T-SQL).

The weird thing is that the assembly runs much faster when unloaded (i.e. when it doesn't show up in sys.dm_clr_loaded_assemblies) than when it is loaded. For some color, I can deserialize 1,000 records in ~200ms when it is unloaded, and the same 1,000 records take ~7 seconds when the assembly is loaded.

I have a workaround, which is that at the beginning of my query I toggle the PERMISSION_SET back and forth from UNSAFE to EXTERNAL_ACCESS which forces an unload of the assembly, but this feels like a hack. The assembly should be faster loaded than unloaded.

Any thoughts here would be greatly appreciated. The code is sketched out below -- nothing fancy going on there at all.

[SqlFunction(FillRowMethodName = "FillRowMessageParser", IsDeterministic = true)]
    public static IEnumerable ParseRows(string MsgText)
    {
        DatabaseRow[] myRows;
        //LitJson doing its work here 
        myRows= JsonMapper.ToObject<DatabaseRow[]>(MsgText);

        return myRows;
    }

    public static FillRowMessageParser(object obj, out SqlChars Field1, out SqlChars Field2, [bunch more out fields here])
    {
         var myRow = (DatabaseRow)obj;

         //Set a bunch of fields to the out variables here
         Field1 = new SqlChars(myRow.Property1);
         //whole bunch more here

         //loop through some nested properties of the myRow class
         foreach (var x in myRow.Object1)
         {
              switch(x.Name)
              {
                 case "1": Field2 = new SqlChars(x.Value); break;
                 //whole bunch more here
              }
         }
    }

The SQL component looks something like this:

DECLARE @JSON NVARCHAR(MAX) = 
(
SELECT 
    TOP 1000
        MessageID,
        JSON_QUERY(MessageText) AS MessageText
FROM MyTable
ORDER BY 1 ASC
FOR JSON AUTO
)

DECLARE @Start DATETIME2
DECLARE @End DATETIME2

SET @Start = SYSDATETIME()

SELECT *
FROM MyCLRTableValuedFunction(@JSON)

SET @End = SYSDATETIME()

SELECT DATEDIFF(MILLISECOND,@Start, @End) --Time CLR takes to process

UPDATE

It appears the issue has to do with the LitJson package itself. We ended up trying JsonFx as another package that does not require any unsupported SQL Server .NET libraries (shoutout to @SolomonRudzky for the suggestion), but for whatever reason the performance of that package in deserialization, which is what our exercise is about, wasn't as good as the native T-SQL JSON handling (at least for our dataset). So we ended up moving off SQLCLR and back to T-SQL for this process. The performance in T-SQL still isn't as good as the unloaded LitJson package, but its good enough for our needs and avoids too many wonky workarounds with unloading the assembly on every call to the CLR.

Answer 1

While I cannot provide a definitive answer at the moment due to not having time to fully review the LitJSON code, I did look over it briefly and am guessing that this odd behavior is the result of using static class variables (mostly collections) to cache values during processing. I can't think of anything else that would different from the first execution to subsequent runs outside of:

1. Assembly isn't already loaded into memory on the first run
2. Static class variables are initialized but likely don't contain any values during execution (unless initialization loads data that is simply read on all executions)

Doing such things does usually improve performance, but there is a nuance when doing such things in SQLCLR: AppDomains in SQL Server are shared across sessions. This means that shared resources are not thread safe. This is why typically (i.e. outside of marking the assembly as UNSAFE) you are not allowed to use writable static class variables (you will get an error saying that they need to be marked readonly). However, in this particular case, there are two breakdowns of this rule that I see:

1. changes made in 2015 (and merged 2 full years later) seem to indicate that the desire was to get LitJSON to work in an assembly marked as SAFE, hence all static class variables were marked as readonly, and additional changes were made to accommodate this. you can see those changes here: https://github.com/LitJSON/litjson/commit/1a120dff7e9b677633bc568322fa065c9dfe4bb8 Unfortunately, even with those changes, even if it did "work" in a SAFE assembly, the variables are still static and are hence still shared. For some technical reason it is permitted to add/remove items from a readonly collection, so on a practical level, they aren't truly read-only. This can definitely lead to unexpected "odd" behavior.
2. If the intention of the changes mentioned above were to allow the assembly to work while being marked as SAFE, then clearly something has changed since that SQLCLR-based commit 4.5 years ago given that not marking it as UNSAFE now results in the following error (according to the OP):

   The protected resources (only available with full trust) were: All The demanded resources were: Synchronization, ExternalThreading So, currently the code requires being marked as UNSAFE, in which case, none of the changes made to mark the static class variables as readonly were necessary ;-).

Regardless, I don't think this code is thread safe. In fact, you might be able to see "odd" behavior by doing multiple executions, each one with a different JSON document that varies in structure (at least number of elements).

Again, this is not definitive, but is more likely than not. In which case, I'm guessing that the great performance of the first execution is due to the code doing things that would not actually work in production. Of course, you do have a hard-coded structure (the output row schema is compiled into the code) so I suppose that eliminates the case of passing in different structures, but it's still not clear what the effect would be if two sessions execute this with different JSON documents at the exact same millisecond.