SET NOCOUNT ON and performance

-- SET NOCOUNT ON: server time of a loop, with and without, against a set-based UPDATE
SET NOCOUNT ON;
USE master;
IF DB_ID(N'q_nocount') IS NOT NULL
BEGIN
    ALTER DATABASE q_nocount SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE q_nocount;
END;
CREATE DATABASE q_nocount;
ALTER DATABASE q_nocount SET RECOVERY SIMPLE;
GO
USE q_nocount;
CREATE TABLE dbo.Compteur (id int PRIMARY KEY, valeur int NOT NULL);
INSERT dbo.Compteur (id, valeur)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0 FROM sys.all_columns;
GO
-- A procedure that runs @n single-row UPDATE statements, in a transaction
CREATE OR ALTER PROCEDURE dbo.BoucleSansNocount @n int AS
BEGIN
    DECLARE @i int = 0;
    BEGIN TRAN;
    WHILE @i < @n BEGIN UPDATE dbo.Compteur SET valeur += 1 WHERE id = 1 + @i % 1000; SET @i += 1; END;
    COMMIT;
END;
GO
CREATE OR ALTER PROCEDURE dbo.BoucleAvecNocount @n int AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @i int = 0;
    BEGIN TRAN;
    WHILE @i < @n BEGIN UPDATE dbo.Compteur SET valeur += 1 WHERE id = 1 + @i % 1000; SET @i += 1; END;
    COMMIT;
END;
GO

DROP TABLE IF EXISTS dbo.Ligne;
CREATE TABLE dbo.Ligne (id int PRIMARY KEY, valeur int NOT NULL);
INSERT dbo.Ligne SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0 FROM sys.all_columns a CROSS JOIN sys.all_columns b;
GO
CREATE OR ALTER PROCEDURE dbo.LigneParLigneSansNocount AS
BEGIN
    DECLARE @i int = 1;
    BEGIN TRAN;
    WHILE @i <= 100000 BEGIN UPDATE dbo.Ligne SET valeur += 1 WHERE id = @i; SET @i += 1; END;
    COMMIT;
END;
GO
CREATE OR ALTER PROCEDURE dbo.LigneParLigneAvecNocount AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @i int = 1;
    BEGIN TRAN;
    WHILE @i <= 100000 BEGIN UPDATE dbo.Ligne SET valeur += 1 WHERE id = @i; SET @i += 1; END;
    COMMIT;
END;
GO
CREATE OR ALTER PROCEDURE dbo.Ensembliste AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Ligne SET valeur += 1;
END;
GO
DBCC FREEPROCCACHE;  -- disposable container: resets the procedure statistics
GO
EXEC dbo.LigneParLigneSansNocount;
EXEC dbo.LigneParLigneAvecNocount;
EXEC dbo.Ensembliste;
GO 5
SELECT OBJECT_NAME(object_id) AS proc_, execution_count,
       total_elapsed_time / execution_count / 1000 AS ms_moyen,
       min_elapsed_time / 1000 AS ms_min, max_elapsed_time / 1000 AS ms_max,
       total_worker_time / execution_count / 1000 AS cpu_ms_moyen
FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID();
GO

"""SET NOCOUNT ON measurement: client time, bytes received, packets written by the server.
A local TCP relay adds a fixed delay in each direction (simulated latency) and counts the bytes."""
import asyncio, os, sys, time, threading, statistics
import pymssql

CIBLE = ("127.0.0.1", 11990)
recu = 0  # bytes server -> client

async def relais(lecteur, ecrivain, delai, compter, debit=None):
    global recu
    file = asyncio.Queue()
    async def ecrire():
        while True:
            t, data = await file.get()
            if data is None:
                ecrivain.close(); return
            attente = t - time.monotonic()
            if attente > 0:
                await asyncio.sleep(attente)
            ecrivain.write(data); await ecrivain.drain()
            if debit:  # bytes per second: wait for the segment's transmission time
                await asyncio.sleep(len(data) / debit)
    tache = asyncio.create_task(ecrire())
    while True:
        data = await lecteur.read(65536)
        if not data:
            await file.put((0, None)); break
        if compter:
            recu += len(data)
        await file.put((time.monotonic() + delai, data))
    await tache

def lancer_relais(port, delai, debit=None):
    async def client(r, w):
        sr, sw = await asyncio.open_connection(*CIBLE)
        await asyncio.gather(relais(r, sw, delai, False), relais(sr, w, delai, True, debit), return_exceptions=True)
    async def main():
        srv = await asyncio.start_server(client, "127.0.0.1", port)
        async with srv: await srv.serve_forever()
    threading.Thread(target=lambda: asyncio.run(main()), daemon=True).start()
    time.sleep(0.3)

def mesure(port, sql, appels):
    global recu
    cn = pymssql.connect(server="127.0.0.1", port=port, user="sa", password=os.environ["SQLCMDPASSWORD"], database="q_nocount", autocommit=True)
    cn.cursor().execute("EXEC dbo.BoucleAvecNocount @n = 1")  # warm-up
    cur = cn.cursor()
    cur.execute("SELECT num_writes FROM sys.dm_exec_connections WHERE session_id = @@SPID"); w0 = cur.fetchone()[0]
    recu = 0
    t0 = time.perf_counter()
    for _ in range(appels):
        cur.execute(sql)
        while cur.nextset():
            pass
    dt = time.perf_counter() - t0
    octets = recu
    cur.execute("SELECT num_writes FROM sys.dm_exec_connections WHERE session_id = @@SPID"); w1 = cur.fetchone()[0]
    cn.close()
    return dt, octets, w1 - w0 - 1  # -1: the response to the first reading

if __name__ == "__main__":
    lancer_relais(12000, 0.0)     # relay with no delay
    lancer_relais(12010, 0.010)   # 10 ms each way, 20 ms round trip
    lancer_relais(12020, 0.010, 10e6 / 8)  # same, plus 10 Mbit/s from server to client
    scenarios = [
        ("1 call, 100,000 UPDATE", 100000, 1),
        ("1,000 calls, 10 UPDATE", 10, 1000),
    ]
    passes = int(sys.argv[1]) if len(sys.argv) > 1 else 3
    for nom, n, appels in scenarios:
        for port, reseau in ((12000, "relay 0 ms"), (12010, "relay 10 ms"), (12020, "10 ms + 10 Mbit/s")):
            for proc in ("BoucleSansNocount", "BoucleAvecNocount"):
                res = [mesure(port, f"EXEC dbo.{proc} @n = {n}", appels) for _ in range(passes)]
                temps = ", ".join(f"{r[0]:.2f}" for r in res)
                print(f"{nom} | {reseau} | {proc} | {temps} s | {res[-1][1]} bytes | {res[-1][2]} packets", flush=True)

USE master;
ALTER DATABASE q_nocount SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE q_nocount;