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.
PERMISSION_SETdoes this assembly usually use? Finally, regarding the app domain issue for other assemblies when changing the permissions of this one: that's easy to solve, just create a userWITHOUT LOGINand change authorization of this assembly to that user so it will have its own app domain.