Dans un précédent article, nous avons vu comment ajouter une fonction SQL à un modèle Entity Framework. Ces fonctions peuvent permettre d'utiliser des syntaxes SQL plus exotiques que celles nativement prises en charge par Entity Framework. Nous n'avons pas vu toutefois l'impact très négatif sur les performances de nos requêtes SQL qu'à pu avoir l'usage d'une telle fonction !

Nous constaterons donc dans cet article les dégradations et verrons comment y remédier.

Dégradation des performances

Sur des tables avec un volume de données relativement faible (je ne donnerai pas de chiffre car cela est toujours très subjectif), la méthode précédente ne posait pas de problème dans le contexte où nous l'utilisions. Nous avions accepté le fait que parser du JSON côté SQL Server n'était pas forcément très performant, mais dans notre cas, c'était tolérable... Jusqu'à ce que certaines tables grossissent de façon conséquente. Les temps d'exécution des requêtes ont commencé à grimper, et ce jusqu'à atteindre parfois 30 secondes lors de nos phases de tests.

Evidemment, nous aurions pu tenter de changer totalement notre mode de stockage, mais le contexte présenté étant très simplifié dans ces articles, je dirai juste que l'effort aurait été trop conséquent. Après plusieurs recherches et expérimentations, la conclusion qui s'impose est que la fonction SQL appelée dans la requête empêche SQL Server d'optimiser correctement la requête.

Par curiosité, nous avons tenté de suivre les conseils de différents articles traitant d'optimisation SQL et de réécrire notre scalar UDF en une TVF (Table Valued Function), comme présenté ici. Et les résultats sont édifiants ! Dans les cas les plus extrêmes, les requêtes SQL passent de 30 à 1 s de temps d'exécution ! C'est pourquoi nous allons terminer cet article en expliquant comment écrire une TVF, puis comment l'intégrer dans un contexte d'appel Entity Framework.

Création d'une Table Valued Function

Le principe consiste à écrire une fonction sur une ligne idéalement. Dans notre cas, plutôt que de renvoyer un booléen afin de déterminer si une valeur se trouve dans un tableau json, nous allons retourner le nombre d’occurrences de cette valeur, ce qui aura pour effet de simplifier au maximum notre fonction :

CREATE FUNCTION [dbo].[json_array_count]
(
	@json nvarchar(max),
	@value nvarchar(max)
)
RETURNS TABLE
AS
RETURN (
	SELECT COUNT([key]) [count] FROM OPENJSON(@json) WHERE [value] = @value
);

L'appel de la fonction dans une requête SQL de test serait alors :

select * from dbo.Persons 
where [dbo].[json_array_count](Kinds, 'kind1') > 0

Comme on peut le voir, la requête ne change pas beaucoup, et pourtant les temps d'exécution en sont nettement améliorés. L'article cité précédemment en explique les raisons mieux que je ne le ferais et donne les résultats d'un benchmark tout à fait révélateur du phénomène.

Cependant, le mapping de cette fonction avec Entity Framework est légèrement plus compliqué qu'avec une scalar UDF.

Appel d'une Table Valued Function via Entity Framework

Comme expliqué dans l'article précédent, l'ajout de ce package nuget permet de faciliter l'inclusion d'une fonction SQL au model Entity Framework, lorsque l'on travaille en code first.

Voici la signature de la méthode proxy à ajouter au DbContext :

[Function(FunctionType.TableValuedFunction, "json_array_count", nameof(MyDbContext), Schema = "dbo")]
public IQueryable<CountResult> JsonArrayCount(
    [Parameter(DbType = "nvarchar(max)", Name = "json")] string json,
    [Parameter(DbType = "nvarchar(max)", Name = "value")] string value) { ... }

On notera ici que la fonction n'est pas statique, nous verrons ensuite pourquoi. Le troisième paramètre de l'attribut Function (namespaceName) oit être le nom de la classe DbContext. Les paramètres de la méthode sont eux aussi décorés comme il se doit. Enfin, le retour de la méthode ne peut plus être un type primitif (puisque notre fonction SQL retourne une table) et devient donc IQueryable<T> où T doit avoir la forme requise pour correspondre à la table retournée. Dans notre cas :

[ComplexType]
public class CountResult
{
    public int Count { get; set; }
}

On remarquera l'usage de l'attribut ComplexType et le nom de la propriété Count qui correspond à l'unique colonne présente dans le retour de notre fonction nommée [count].

Contrairement à une méthode proxy d'une scalar UDF, notre méthode ne sera pas directement traduite en SQL, mais appelée avant traduction. Le corps de la méthode est donc important et doit générer le SQL nécessaire à l'appel de la fonction :

var p_json = new ObjectParameter("json", json);
var p_values = new ObjectParameter("value", value);
return this.ObjectContext().CreateQuery<CountResult>($"[{nameof(json_array_count)}](@{nameof(json)}, @{nameof(value)})"
    , p_json, p_value);

Il faut donc créer une variable de type ObjectParameter pour chaque paramètre de la méthode avec le nom du paramètre et sa valeur. La méthode CreateQuery permet ensuite d'insérer un fragment de SQL (attention aux injections SQL).

Voici la méthode complète :

[Function(FunctionType.TableValuedFunction, "json_array_count", nameof(MyDbContext), Schema = "dbo")]
public IQueryable<CountResult> JsonArrayCount(
    [Parameter(DbType = "nvarchar(max)", Name = "json")]string json,
    [Parameter(DbType = "nvarchar(max)", Name = "value")]string value)
{
    var p_json = new ObjectParameter("json", json);
    var p_value = new ObjectParameter("value", value);
    return this.ObjectContext().CreateQuery<CountResult>($"[json_array_count](@json, @value)"
        , p_json, p_value);
}

Ensuite, pour l'appel de la fonction SQL, rien de plus simple :

using (var db = new MyDbContext())
{
    db.Persons.Where(p => db.JsonArrayCount(p.Kinds, "test").Any(v => v.Count > 0));
}

Et voilà ! Comme nous avons pu le constater, la tendance naturelle que nous pouvons avoir de créer une scalar UDF pour abstraire la complexité d'un fragment SQL n'est pas forcément la meilleure. Il peut parfois être judicieux de modifier notre façon de penser et de créer une table valued function, ce qui dans notre cas a permis d'atteindre un niveau d'exécution de requête jusque 30 fois plus rapide ! Le tout parfaitement traité avec Entity Framework.