SET NOCOUNT ON usage

Question

Inspiré par cette question où il y a des opinions divergentes sur SET NOCOUNT...

Faut-il utiliser SET NOCOUNT ON pour SQL Server ? Si non, pourquoi ?

Ce qu'il fait Edit 6, le 22 Jul 2011

Il supprime le message "xx lignes affectées" après tout DML. Il s'agit d'un jeu de résultats et lorsqu'il est envoyé, le client doit le traiter. C'est minuscule, mais mesurable (voir les réponses ci-dessous).

Pour les triggers, etc., le client recevra plusieurs "xx lignes affectées" et cela provoque toutes sortes d'erreurs pour certains ORM, MS Access, JPA, etc.

Le contexte :

La meilleure pratique généralement acceptée (je pensais jusqu'à cette question) est d'utiliser SET NOCOUNT ON dans les déclencheurs et les procédures stockées de SQL Server. Nous l'utilisons partout et une rapide recherche sur Google montre que de nombreux MVPs SQL Server sont d'accord aussi.

MSDN dit que cela peut casser un .net SQLDataAdapter .

Cela signifie pour moi que l'adaptateur de données SQLDataAdapter est limité à un simple traitement CRUD car il s'attend à ce que le message "n lignes affectées" corresponde. Donc, je ne peux pas utiliser :

• IF EXISTS pour éviter les doublons (message no rows affected) Note : à utiliser avec précaution
• WHERE NOT EXISTS (moins de rangs que prévu)
• Filtrez les mises à jour triviales (par exemple, aucune donnée ne change réellement).
• Effectuer tout accès aux tables avant (comme la journalisation)
• Masquer la complexité ou la dénormalisation
• etc.

Dans la question, marc_s (qui connaît son SQL) dit de ne pas l'utiliser. Cela diffère de ce que je pense (et je me considère comme assez compétent en SQL aussi).

Il est possible que j'oublie quelque chose (n'hésitez pas à me faire remarquer ce qui est évident), mais qu'en pensez-vous ?

Note : cela fait des années que je n'ai pas vu cette erreur car je n'utilise plus SQLDataAdapter de nos jours.

Modifications après commentaires et questions :

Edit : Plus de pensées...

Nous avons plusieurs clients : l'un peut utiliser un SQLDataAdaptor en C#, un autre peut utiliser nHibernate depuis Java. Ceux-ci peuvent être affectés de différentes manières avec SET NOCOUNT ON .

Si vous considérez les procs stockés comme des méthodes, il est alors malvenu (anti-modèle) de supposer qu'un traitement interne fonctionne d'une certaine manière pour vos propres besoins.

Edit 2 : a Question sur le déclenchement de nHibernate où SET NOCOUNT ON ne peut pas être réglé

(et non, ce n'est pas un doublon de ce )

Edit 3 : Encore plus d'informations, grâce à mon collègue MVP

• KB 240882 problème de déconnexion sur SQL 2000 et antérieurs
• Démonstration du gain de performance

Edit 4 : 13 mai 2011

Casse aussi le SQL Linq 2 quand il n'est pas spécifié ?

Edit 5 : 14 Jun 2011

Casse JPA, proc stocké avec des variables de table : JPA 2.0 prend-il en charge les variables de table du serveur SQL ?

Edit 6 : 15 Aug 2011

La grille de données SSMS "Edit rows" nécessite SET NOCOUNT ON : Mise à jour du déclencheur avec GROUP BY

Edit 7 : 07 Mar 2013

Plus de détails en profondeur de @RemusRusanu :
Est-ce que SET NOCOUNT ON fait vraiment une grande différence en termes de performances ?

Answer 1

Ok, maintenant que j'ai fait mes recherches, voici l'affaire :

• Dans le protocole TDS SET NOCOUNT ON ne permet d'économiser que 9 octets par requête. ce qui ne semble pas beaucoup. J'avais l'habitude de penser que 9 row(s) affected a été renvoyé par le serveur en texte clair mais cela ne semble pas être le cas. Il s'agit en fait d'un petit paquet de messages appelé DONE_IN_PROC .

• Microsoft encourage en fait l'utilisation de SET NOCOUNT ON dans les procédures stockées, conformément au livre " Amélioration des performances et de l'évolutivité des applications .NET " (page 541). Je ne baserais pas mes décisions de conception sur SqlDataAdapter.

Donc je pense que vous pouvez vous en tenir à SET NOCOUNT ON si le coût est inférieur à celui du passage à une autre technologie. J'envisagerais quand même d'abandonner SqlDataAdapter car vous ne savez toujours pas quel genre de bizarrerie de conception vous rencontrerez ensuite.

EDIT : @racingsnail a fait remarquer que le délai d'aller-retour du réseau est un plus grand tueur de performance que la taille du paquet. Il a raison mais un second paquet réseau ne causerait pas le même retard que la latence aller-retour car les paquets seraient envoyés en tandem et ne nécessiteraient pas d'accusé de réception. Il se peut donc que le retard soit bien moindre que la latence aller-retour réelle du réseau.

Answer 2

Il m'a fallu beaucoup de temps pour trouver des chiffres de référence réels concernant NOCOUNT, et j'ai donc décidé de partager un bref résumé.

• Si votre procédure stockée utilise un curseur pour effectuer un grand nombre d'opérations très rapides sans retour de résultats, le fait de désactiver NOCOUNT peut prendre environ 10 fois plus de temps que de l'activer. 1 C'est le pire des scénarios.
• Si votre procédure stockée n'effectue qu'une seule opération rapide sans retour de résultats, l'activation de NOCOUNT permet d'améliorer les performances d'environ 3 %. 2 Cela correspondrait à une procédure typique d'insertion ou de mise à jour.
• Si votre procédure stockée renvoie des résultats (c'est-à-dire que vous SÉLECTIONNEZ quelque chose), la différence de performance diminuera proportionnellement à la taille de l'ensemble de résultats.

Answer 3

• Lorsque SET NOCOUNT est ON, le compte (indiquant le nombre de lignes affectées par une instruction Transact-SQL) n'est pas retourné. Lorsque SET NOCOUNT est OFF, le compte est retourné. Elle est utilisée avec toute instruction SELECT, INSERT, UPDATE, DELETE.

• Le paramètre de SET NOCOUNT est défini au moment de l'exécution ou du lancement et non au moment de l'analyse syntaxique.

• SET NOCOUNT ON améliore les performances des procédures stockées (SP).

• Syntaxe : SET NOCOUNT { ON | OFF }

Exemple de SET NOCOUNT ON :

Exemple de SET NOCOUNT OFF :

Answer 4

Je suppose que dans une certaine mesure, c'est une question de DBA contre développeur.

En tant que développeur, je dirais surtout de ne pas l'utiliser à moins que vous ne soyez absolument obligé de le faire, car il peut casser votre code ADO.NET (comme le documente Microsoft).

Et je suppose qu'en tant que DBA, vous seriez plutôt de l'autre côté - utilisez-le autant que possible, sauf si vous devez vraiment empêcher son utilisation.

De plus, si vos développeurs utilisent parfois les "RecordsAffected" renvoyés par l'outil ADO.NET ExecuteNonQuery vous avez des problèmes si tout le monde utilise la méthode SET NOCOUNT ON puisque dans ce cas, ExecuteNonQuery retournera toujours 0.

Voir également l'article de Peter Bromberg article de blog et vérifier sa position.

Il s'agit donc de savoir qui fixe les normes :-)

Marc

Answer 5

Si vous dites que vous avez peut-être aussi des clients différents, il y a des problèmes avec ADO classique si SET NOCOUNT n'est pas activé.

Une situation que je rencontre régulièrement : si une procédure stockée exécute un certain nombre d'instructions (et donc un certain nombre de messages "xxx lignes affectées" sont renvoyés), ADO semble ne pas gérer cela et affiche l'erreur suivante "Impossible de modifier la propriété ActiveConnection d'un objet Recordset dont la source est un objet Command".

Donc, je préconise généralement de le mettre sur ON, sauf s'il y a un vraiment vraiment Vous avez peut-être trouvé la très bonne raison pour laquelle je dois me documenter davantage.