SET NOCOUNT ON Verwendung

Question

Inspiriert durch diese Frage wo es unterschiedliche Ansichten über SET NOCOUNT gibt...

Sollten wir SET NOCOUNT ON für SQL Server verwenden? Wenn nicht, warum nicht?

Was es bewirkt Edit 6, am 22. Juli 2011

Es unterdrückt die Meldung "xx Zeilen betroffen" nach jeder DML. Dies ist eine Ergebnismenge, und wenn sie gesendet wird, muss der Client sie verarbeiten. Es ist winzig, aber messbar (siehe Antworten unten)

Bei Triggern usw. erhält der Client mehrere "xx betroffene Zeilen", was bei einigen ORMs, MS Access, JPA usw. alle möglichen Fehler verursacht (siehe nachstehende Änderungen).

Hintergrund:

Die allgemein anerkannte beste Praxis (dachte ich bis zu dieser Frage) ist die Verwendung von SET NOCOUNT ON in Triggern und gespeicherten Prozeduren in SQL Server. Wir verwenden es überall, und ein kurzer Blick auf Google zeigt, dass auch viele SQL Server MVPs damit einverstanden sind.

Laut MSDN kann dies eine .net SQLDataAdapter .

Nun bedeutet dies für mich, dass der SQLDataAdapter auf völlig einfache CRUD-Verarbeitung beschränkt ist, weil er erwartet, dass die Meldung "n Zeilen betroffen" übereinstimmt. Also, ich kann nicht verwenden:

• IF EXISTS, um Duplikate zu vermeiden (Meldung, dass keine Zeilen betroffen sind) Hinweis: mit Vorsicht zu verwenden
• WHERE NOT EXISTS (weniger Zeilen als erwartet)
• Herausfiltern trivialer Aktualisierungen (z. B. keine tatsächlichen Datenänderungen)
• Führen Sie vorher einen Tabellenzugriff durch (z. B. Protokollierung)
• Komplexität oder Denormlisierung ausblenden
• usw.

In der Frage sagt marc_s (der sich mit SQL auskennt), dass man es nicht verwenden soll. Dies unterscheidet sich von dem, was ich denke (und ich halte mich auch für einigermaßen kompetent in SQL).

Es ist möglich, dass ich etwas übersehe (bitte weisen Sie mich auf das Offensichtliche hin), aber was denken Sie da draußen?

Hinweis: Es ist Jahre her, dass ich diesen Fehler gesehen habe, da ich SQLDataAdapter heutzutage nicht mehr verwende.

Bearbeitet nach Kommentaren und Fragen:

Edit: Weitere Gedanken...

Wir haben mehrere Clients: ein Client kann einen C# SQLDataAdaptor verwenden, ein anderer nHibernate aus Java. Diese können auf unterschiedliche Weise beeinflusst werden mit SET NOCOUNT ON .

Wenn Sie gespeicherte Prozeduren als Methoden betrachten, dann ist es schlechtes Benehmen (Anti-Pattern), davon auszugehen, dass eine interne Verarbeitung auf eine bestimmte Weise für Ihre eigenen Zwecke funktioniert.

Bearbeiten 2: a Trigger, der nHibernate bricht Frage , wobei SET NOCOUNT ON kann nicht eingestellt werden

(und nein, es handelt sich nicht um ein Duplikat von diese )

Edit 3: Noch mehr Informationen, dank meines MVP-Kollegen

• KB 240882 Problem, das Verbindungsabbrüche bei SQL 2000 und früheren Versionen verursacht
• Demo der Leistungssteigerung

Bearbeiten 4: 13. Mai 2011

Bricht Linq 2 SQL auch, wenn nicht angegeben?

Bearbeiten 5: 14 Jun 2011

Bricht JPA, Stored Proc mit Tabellenvariablen: Unterstützt JPA 2.0 SQL Server-Tabellenvariablen?

Bearbeiten 6: 15 Aug 2011

Das SSMS-Datengitter "Zeilen bearbeiten" erfordert SET NOCOUNT ON: Aktualisierungs-Trigger mit GROUP BY

Bearbeiten 7: 07 Mär 2013

Ausführlichere Informationen von @RemusRusanu:
Macht SET NOCOUNT ON wirklich einen so großen Unterschied in der Leistung aus?

Answer 1

Auf die Gefahr hin, die Dinge noch komplizierter zu machen, schlage ich eine etwas andere Regel als die oben genannten vor:

• Immer einstellen NOCOUNT ON am Anfang einer Prozedur, bevor Sie irgendeine Arbeit in der Prozedur machen, aber auch immer SET NOCOUNT OFF bevor die Datensätze aus dem gespeicherten Prozess zurückgegeben werden.

Also "generell nocount eingeschaltet lassen, außer wenn Sie tatsächlich eine Ergebnismenge zurückgeben". Ich wüsste nicht, wie dies einen Client-Code kaputt machen könnte. Es bedeutet, dass der Client-Code nie etwas über die Interna der Proc wissen muss, und es ist nicht besonders lästig.

Answer 2

Was die Auslöser betrifft, die NHibernate zerstören, so habe ich diese Erfahrung aus erster Hand gemacht. Wenn NH ein UPDATE durchführt, erwartet es eine bestimmte Anzahl betroffener Zeilen. Durch Hinzufügen von SET NOCOUNT ON zu den Triggern wird die von NH erwartete Anzahl von Zeilen wiederhergestellt, wodurch das Problem behoben wird. Also ja, ich würde auf jeden Fall empfehlen, es für Trigger auszuschalten, wenn Sie NH verwenden.

Was die Verwendung in SPs angeht, so ist dies eine Frage der persönlichen Vorliebe. Ich hatte die Zeilenzählung immer ausgeschaltet, aber andererseits gibt es keine wirklich überzeugenden Argumente für die eine oder andere Seite.

Im Übrigen sollten Sie wirklich in Erwägung ziehen, von der SP-basierten Architektur wegzukommen, dann stellt sich diese Frage gar nicht erst.

Answer 3

Ich wollte mich selbst vergewissern, dass 'SET NOCOUNT ON' weder ein Netzwerkpaket noch einen Roundtrip speichert

Ich habe einen Test SQLServer 2017 auf einem anderen Host verwendet (ich habe eine VM verwendet) create table ttable1 (n int); insert into ttable1 values (1),(2),(3),(4),(5),(6),(7) go create procedure procNoCount as begin set nocount on update ttable1 set n=10-n end create procedure procNormal as begin update ttable1 set n=10-n end Dann habe ich die Pakete an Port 1433 mit dem Tool 'Wireshark' verfolgt: Schaltfläche "Erfassungsfilter" -> "Port 1433".

exec procNoCount

Dies ist das Antwortpaket: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 42 d0 ce 40 00 40 06 84 0d c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e5 9c be fb 85 01 50 18 0030 02 b4 e6 0e 00 00 04 01 00 1a 00 35 01 00 79 00 0040 00 00 00 fe 00 00 e0 00 00 00 00 00 00 00 00 00

exec procNormal

Dies ist das Antwortpaket: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 4f d0 ea 40 00 40 06 83 e4 c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e8 b1 be fb 8a 35 50 18 0030 03 02 e6 1b 00 00 04 01 00 27 00 35 01 00 ff 11 0040 00 c5 00 07 00 00 00 00 00 00 00 79 00 00 00 00 0050 fe 00 00 e0 00 00 00 00 00 00 00 00 00

In Zeile 40 sehe ich "07", das ist die Anzahl der "betroffenen Zeile(n)". Sie ist in dem Antwortpaket enthalten. Kein zusätzliches Paket.

Es handelt sich jedoch um 13 zusätzliche Bytes, die eingespart werden könnten, was sich aber wahrscheinlich nicht mehr lohnt als die Reduzierung von Spaltennamen (z. B. "ManagingDepartment" auf "MD").

Ich sehe also keinen Grund, es für die Leistung zu verwenden.

BUT Wie bereits von anderen erwähnt, kann es ADO.NET zerstören und ich bin auch über ein Problem mit Python gestolpert: MSSQL2008 - Pyodbc - Vorheriges SQL war keine Abfrage

Also wahrscheinlich immer noch eine gute Angewohnheit...

Answer 4

Ein Ort, der SET NOCOUNT ON wirklich helfen kann, ist, wenn Sie Abfragen in einer Schleife oder einem Cursor durchführen. Dies kann zu einer Menge Netzwerkverkehr führen.

CREATE PROCEDURE NoCountOn
AS
set nocount on
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO

CREATE PROCEDURE NoCountOff
AS
set nocount off
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO

Wenn Sie die Client-Statistiken in SSMS aktivieren, wird ein Lauf von EXEC NoCountOn et EXEC NoCountOff zeigt, dass bei der NoCountOff-Variante 390 KB zusätzlicher Datenverkehr auftrat:

Wahrscheinlich ist es nicht ideal, Abfragen in einer Schleife oder einem Cursor zu machen, aber wir leben auch nicht in einer idealen Welt :)

Answer 5

SET NOCOUNT ON;

Diese Codezeile wird in SQL verwendet, um die Anzahl der betroffenen Zeilen bei der Ausführung der Abfrage nicht zurückzugeben. Wenn wir die Anzahl der betroffenen Zeilen nicht benötigen, können wir diese Zeile verwenden, um Speicherplatz zu sparen und die Geschwindigkeit der Ausführung der Abfrage zu erhöhen.