Les dangers de nvarchar(max) dans SQL pour les développeurs Entity Framework

Comprendre le problème : comportement par défaut dans Entity Framework

Tim commence par décrire un scénario courant d'Entity Framework dans lequel un développeur C# définit un modèle avec des champs tels que Prénom et Nom. Lorsque la table est créée automatiquement dans SQL Server à l'aide de migrations, le schéma généré définit ces champs de type chaîne à nvarchar(max) par défaut.

Comme l'explique Tim, cela se produit parce qu'Entity Framework ne connaît pas la taille de chaîne appropriée à attribuer, et choisit donc la voie la plus sûre, en attribuant la longueur maximale par défaut. Cela signifie que chaque colonne nvarchar peut contenir jusqu'à 2^31-1 caractères, avec une taille de stockage maximale de l'ordre du gigaoctet.

Cette décision semble pratique, mais elle cache des coûts de performance dangereux.

Exemple de configuration avec deux tables : nvarchar(max) vs longueur fixe

Pour mettre en évidence le problème, Tim crée deux tableaux identiques :

• Utilisateurs : avec nvarchar(50) pour les noms et prénoms.

• UsersToTheMax : avec nvarchar(max) pour les mêmes champs.

À 2:39, Tim explique comment il a rempli les deux tables avec 1 million de lignes identiques à l'aide de Dapper, en s'assurant que seul le type de données nvarchar diffère.

Cette configuration lui permet d'effectuer une comparaison cohérente entre une colonne Unicode de longueur fixe et une colonne max de longueur variable.

Comparaison des requêtes et des plans d'exécution

Tim utilise la requête SQL suivante sur les deux tables :

SELECT * FROM dbo.Users ORDER BY LastName ;
SELECT * FROM dbo.UsersToTheMax ORDER BY LastName ;

À 3:34, il active le plan d'exécution réel pour analyser ce que le serveur SQL fait en interne lorsqu'il exécute ces requêtes.

Remarque : ce test ne concerne pas le temps d'exécution total sur les différentes machines. Tim insiste sur la comparaison des requêtes sur le même serveur avec les mêmes données, afin d'isoler l'impact de nvarchar(max) sur les performances.

Les résultats choquants

Les plans d'exécution révèlent une différence majeure :

• La requête sur nvarchar(50) n'utilise que 2 % du coût du lot.

• La requête sur nvarchar(max) utilise 98 % du coût.

Comme le dit Tim, cela signifie que la requête maximale est 50 fois plus coûteuse en termes de traitement par SQL Server, même si les entrées de données des colonnes sont identiques et relativement petites.

En termes de temps CPU :

• Le tri de nvarchar(50) prend 107 ms.

• Le tri de nvarchar(max) prend 339 ms.

Mais la plus grande différence réside dans une opération de parallélisme spécifique :

• Durée fixe : 0.43s

• Durée maximale : 22.17s

C'est plus de 50 fois plus lent, même avec des données identiques.

Différences de consommation de mémoire

Tim se penche sur les subventions de mémoire, c'est-à-dire la quantité de mémoire que SQL Server alloue à chaque requête :

• nvarchar(50) query : 340MB

• nvarchar(max) query : 641 MO

En soi, il s'agit d'un signal d'alarme, mais lorsque l'on teste des colonnes non mises en cache, l'impact est encore plus important :

• Longueur fixe sur FirstName : 357MB

• Longueur maximale du prénom : 8.5GB

Cette augmentation se produit parce que SQL Server ne sait pas quelle taille la valeur nvarchar peut avoir lorsqu'elle est définie comme max, et réserve donc un bloc de mémoire plus important pour accueillir la taille maximale.

Pourquoi nvarchar(max) est-il si cher ?

À 9:15, Tim explique la raison sous-jacente. Le type de données nvarchar(max) :

• Prend en charge jusqu'à 2^31-1 caractères Unicode et consomme jusqu'à 2 Go d'espace de stockage.

• SQL Server doit stocker la valeur en dehors de la ligne si elle n'est pas adaptée, en utilisant un pointeur au lieu d'un stockage direct dans la ligne.

• Ne peut être indexé de la même manière que les colonnes de longueur fixe.

En conséquence :

• Vous ne pouvez pas indexer une colonne nvarchar(max), ce qui signifie que SQL Server doit trier ou filtrer l'ensemble des données sans optimisation.

• Cela concerne des opérations telles que ORDER BY, WHERE ou JOIN sur des champs nvarchar(max).

Ce comportement entraîne une utilisation importante de la mémoire, une charge du processeur et des ralentissements, simplement en choisissant la mauvaise longueur de données des caractères.

Recommandation finale de Tim

Comme le dit Tim en conclusion :

"Dans vos requêtes Entity Framework, assurez-vous de spécifier la taille de toutes les chaînes de caractères."

Définissez toujours vos propriétés de chaîne avec un nombre maximal de caractères, comme nvarchar(100) ou nvarchar(255), en fonction des données attendues. Cette modification mineure garantit :

• Espace de stockage optimisé

• Prise en charge de l'indexation

• Réduction du coût des requêtes

• Meilleure cohérence des performances

En définissant une longueur appropriée, vous rendez votre schéma de base de données plus efficace et évitez les pièges des paramètres par défaut paresseux.

Conclusion

La vidéo de Tim Corey délivre une leçon essentielle : l'utilisation de nvarchar(max) comme longueur par défaut pour les champs de chaîne dans SQL peut nuire aux performances, sans même que l'on s'en rende compte. SQL Server alloue une mémoire excessive, saute des index et augmente les coûts de l'unité centrale, même pour des entrées de texte Unicode normales telles que des noms ou des adresses.

Ce qu'il faut retenir ? Comprenez le type de données nvarchar et évitez max à moins que vous n'en ayez vraiment besoin pour les champs susceptibles de stocker des documents volumineux ou des contenus de longueur variable.

En spécifiant la taille des chaînes de caractères, vous n'économisez pas seulement des octets et de la mémoire, vous rendez également votre code Entity Framework et SQL plus efficace, plus évolutif et plus robuste. En suivant les conseils de Tim, vous vous assurez que votre application n'est pas lente par conception.

Pour tous ceux qui travaillent avec des bases de données en .NET, il s'agit d'une pratique exemplaire qui devrait faire partie de votre boîte à outils standard. Visitez le site de Tim Channel pour d'autres vidéos sur SQL.