SET NOCOUNT ON et les performances

-- SET NOCOUNT ON : temps serveur d'une boucle, avec et sans, face à un UPDATE ensembliste
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
-- Une procédure qui exécute @n UPDATE d'une ligne, dans une 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;  -- conteneur jetable : remet les statistiques des procédures à zéro
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

"""Mesure de SET NOCOUNT ON : temps client, octets reçus, paquets écrits par le serveur.
Un relais TCP local ajoute un délai fixe dans chaque sens (latence simulée) et compte les octets."""
import asyncio, os, sys, time, threading, statistics
import pymssql

CIBLE = ("127.0.0.1", 11990)
recu = 0  # octets serveur -> 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:  # octets par seconde : on attend le temps de transmission du segment
                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")  # chauffe
    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 : la réponse du premier relevé

if __name__ == "__main__":
    lancer_relais(12000, 0.0)     # relais sans délai
    lancer_relais(12010, 0.010)   # 10 ms par sens, 20 ms aller-retour
    lancer_relais(12020, 0.010, 10e6 / 8)  # idem, et 10 Mbit/s du serveur vers le client
    scenarios = [
        ("1 appel, 100 000 UPDATE", 100000, 1),
        ("1 000 appels, 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, "relais 0 ms"), (12010, "relais 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]} octets | {res[-1][2]} paquets", flush=True)

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