SET NOCOUNT SUR l'utilisation de la
Inspiré par cette question où il ya des opinions divergentes sur SET NOCOUNT...
devrions-nous utiliser SET NOCOUNT ON pour SQL Server? Si non, pourquoi pas?
Ce qu'il fait Edit 6, sur 22 Juil 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 déclencheurs, etc., le client recevra plusieurs "lignes xx affectées" et cela cause toutes sortes d'erreurs pour certains ORMs, accès MS, JPA, etc. (voir les modifications ci-dessous)
Contexte:
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 dans SQL Server. Nous l'utiliser partout et un rapide google montre beaucoup de MVP SQL Server sont d'accord aussi.
MSDN dit que cela peut briser un SQLDataAdapter .
maintenant, cela signifie pour moi que le SQLDataAdapter est limité au traitement tout simplement CRUD parce qu'il s'attend à ce que le message "n lignes affectées" corresponde. Donc, je ne peux pas utiliser:
- S'il existe pour éviter les doublons (pas de ligne affectée message) Note: utiliser avec prudence
- LORSQU'il N'existe pas (moins de lignes que prévu)
- filtrer les mises à jour triviales (par exemple Aucune donnée ne change réellement)
- N'importe quel accès à la table avant (comme l'enregistrement)
- Masquer la complexité ou denormlisation
- etc
Dans la question marc_s (qui connaît son SQL trucs) dit ne pas l'utiliser. Cela diffère de ce que je pense (et je me considère comme quelque peu compétent à SQL trop.)
il est possible que je manque quelque chose (n'hésitez pas à souligner l'évidence), mais que pensez-vous là-bas?
Note: Cela fait des années que je n'ai pas vu cette erreur car je n'utilise pas SQLDataAdapter de nos jours.
modifications après commentaires et questions:
Edit: Plus de pensées...
nous avons plusieurs clients: l'un peut utiliser un C# SQLDataAdaptor, un autre peut utiliser le nHibernate de Java. Ceux-ci peuvent être affectés de différentes façons avec SET NOCOUNT ON
.
si vous considérez les procs stockés comme des méthodes, alors c'est une mauvaise forme (anti-pattern) de supposer qu'un traitement interne fonctionne d'une certaine manière pour vos propres fins.
Edit 2: un déclencheur de la rupture de nHibernate question , où SET NOCOUNT ON
ne peut pas être réglé
(et non, ce n'est pas un duplicata de this )
Edit 3: encore plus d'infos, merci à mon collègue MVP
- KO 240882 , problème qui se déconnecte sur SQL 2000 et antérieures
- Démonstration de gain de performance
Edit 4: 13 May 2011
Pauses Linq 2 SQL trop lorsqu'il n'est pas spécifié?
Edit 5: 14 Juin 2011
casse JPA, stocké proc avec des variables de table: est-ce que JPA 2.0 Supporte les variables de table SQL Server?
Edit 6: 15 Aug 2011
la grille de données SSMS "Edit rows" exige que NOCOUNT soit positionné sur: Update trigger with GROUP BY
Edit 7: 07 Mar 2013
plus de détails sur @RemusRusanu:
Does SET NOCOUNT on vraiment faire beaucoup de différence de performances
12 réponses
OK maintenant que j'ai fait mes recherches, voici le deal:
dans le protocole TDS, SET NOCOUNT ON
ne sauve que 9 octets par requête alors que le texte "SET NOCOUNT ON" lui-même est une masse de 14 octets. Je pensais que 123 row(s) affected
était Retourné du serveur en texte clair dans un paquet réseau séparé, mais ce n'est pas le cas. C'est en fait une petite structure appelée DONE_IN_PROC
intégrée dans la réponse. Ce n'est pas un paquet réseau séparé donc pas de roundtrips sont gaspillées.
je pense que vous pouvez vous en tenir au comportement de comptage par défaut presque toujours sans vous soucier de la performance. Il y a cependant des cas où le calcul du nombre de lignes à l'avance aurait un impact sur la performance, comme un curseur vers l'avant seulement. Dans ce cas, NOCOUNT pourrait être une nécessité. A part cela, il n'est absolument pas nécessaire de suivre la devise "utilisez NOCOUNT dans la mesure du possible".
Voici une analyse très détaillée sur insignifiance du réglage SET NOCOUNT
: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount /
il m'a fallu creuser beaucoup pour trouver de vrais chiffres de référence autour de NOCOUNT, donc j'ai pensé que je partagerais un résumé rapide.
- si votre procédure stockée utilise un curseur pour effectuer un grand nombre d'opérations très rapides sans résultat retourné, avoir NOCOUNT OFF peut prendre environ 10 fois plus longtemps que l'avoir allumé. 1 C'est le pire des scénarios.
- si votre procédure stockée ne fonctionne une seule opération rapide sans résultats retournés, réglant NOCOUNT ON donnera une augmentation de performance 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.
-
lorsque SET NOCOUNT est activé, le nombre (indiquant le nombre de lignes affectées par une instruction Transact-SQL) n'est pas retourné. Lorsque SET NOCOUNT est désactivé, le compte est retourné. Il est utilisé avec tout SELECT, INSERT, UPDATE, DELETE statement.
-
le réglage de SET NOCOUNT est défini à l'exécution ou à l'exécution et non à l'exécution.
-
SET NOCOUNT ON améliore la procédure stockée (SP) de la performance.
-
syntaxe: SET NOCOUNT { ON / OFF }
Example of SET NOCOUNT ON:
Example of SET NOCOUNT OFF:
je suppose que dans une certaine mesure c'est un problème DBA vs développeur.
en tant que dev la plupart du temps, je dirais ne pas l'utiliser à moins que vous devez absolument positivement - parce que l'utiliser peut briser votre ADO.NET code (tel que documenté par Microsoft).
et je suppose que comme un DBA, vous seriez plus sur l'autre côté - utilisez-le si possible à moins que vous devez vraiment empêcher son utilisation.
aussi, si vos devs utilisent un jour le "RecordsAffected" étant retourné par ADO.NET 's ExecuteNonQuery
appel de méthode, vous êtes en difficulté si tout le monde utilise SET NOCOUNT ON
car dans ce cas, ExecuteNonQuery retournera toujours 0.
Voir Aussi blog post de Peter Bromberg et vérifier sa position.
Donc vraiment résume à qui fixe les normes :-)
Marc
si vous dites que vous pourriez avoir des clients différents, il y a des problèmes avec classic ADO si SET NOCOUNT n'est pas activé.
J'en fais l'expérience régulièrement: si une procédure stockée exécute un certain nombre d'instructions (et donc un certain nombre de messages" xxx rows affected "sont retournés), ADO semble ne pas gérer cela et jette l'erreur " ne peut pas changer la propriété ActiveConnection d'un objet Recordset qui a un objet de commande comme source."
donc je suis généralement en faveur de l'installer sauf s'il y a une really really really bonne raison de ne pas le faire. vous avez trouvé vraiment très bonne raison pour laquelle j'ai besoin d'aller lire en plus.
au risque de rendre les choses plus compliquées, j'encourage une règle légèrement différente de toutes celles que je vois ci-dessus:
- toujours placer
NOCOUNT ON
au sommet d'un proc, avant de faire n'importe quel travail dans le proc, mais aussi toujoursSET NOCOUNT OFF
encore, avant de retourner n'importe quels recordsets du proc stocké.
ainsi" généralement garder nocount on, sauf si vous retournez un jeu de résultats". Je n'ai pas sachez toutes les façons que cela peut briser n'importe quel code client, cela signifie que le code client n'a jamais besoin de savoir quoi que ce soit sur les internes proc, et ce n'est pas particulièrement onéreux.
en ce qui concerne les détonateurs cassant le NHibernate, j'ai eu cette expérience de première main. Fondamentalement, quand NH fait une mise à jour, il s'attend à un certain nombre de lignes affectées. En ajoutant SET NOCOUNT ON aux triggers, vous obtenez le nombre de lignes de nouveau à ce que NH attendait en corrigeant ainsi la question. Donc oui, je recommande vraiment de l'éteindre pour les détonateurs si vous utilisez NH.
en ce qui concerne L'usage dans SPs, c'est une question de préférence personnelle. J'avais toujours fait tourner le compte des rangs off, mais là encore, il n'y a pas d'arguments vraiment forts dans les deux cas.
sur une autre note, vous devriez vraiment envisager de vous éloigner de l'architecture basée sur SP, alors vous n'aurez même pas cette question.
SET NOCOUNT ON; Le code ci-dessus arrêtera le message généré par le moteur de serveur sql à la fenêtre de résultat frontée après l'exécution de la commande DML/DDL.
pourquoi on fait ça? Comme SQL server engine prend quelques ressources pour obtenir le statut et générer le message, il est considéré comme une surcharge pour le Sql server engine.Nous avons donc activé le message de non-comptage.
Je ne sais pas comment tester SET NOCOUNT entre client et SQL, donc j'ai testé un comportement similaire pour une autre commande SET."
j'ai envoyé une commande de ma connexion modifiant le comportement par défaut de SQL (lire engagé), et elle a été modifiée pour les commandes suivantes. Quand j'ai changé le niveau D'ISOLATION à l'intérieur d'une procédure stockée, cela n'a pas changé le comportement de connexion pour la commande suivante.
Conclusion actuelle,
- Modifier les paramètres à l'intérieur de la procédure stockée ne modifie pas les paramètres par défaut de connexion.
- modifier le paramètre en envoyant des commandes à L'aide de L'ADOCOnnection modifie le comportement par défaut.
je pense que c'est pertinent pour d'autres commandes comme "SET NOCOUNT ON"
SET NOCOUNT ON;
cette ligne de code est utilisée dans SQL pour ne pas retourner les lignes de nombre affectées dans l'exécution de la requête. Si nous n'avons pas besoin du nombre de lignes affectées, nous pouvons l'utiliser car cela aiderait à sauvegarder l'utilisation de la mémoire et augmenterait la vitesse d'exécution de la requête.
if (set no count== off)
{ puis il va conserver les données du nombre d'enregistrements affectés donc réduire les performances } autre { il ne suivra pas l'enregistrement des changements par conséquent, améliorer perfomace } }
je sais que c'est une vieille question. mais juste pour le mettre à jour.
la meilleure façon d'utiliser" SET NOCOUNT ON " est de le présenter comme une première déclaration dans votre SP et de le désactiver à nouveau juste avant la dernière déclaration SELECT.