Running Totals: SQLCLR version

[SqlProcedure]
public static void up_runningTotals()
{
    string tsqlStatement = "SELECT customerID, qty, 0 AS qtySum FROM orders ORDER BY customerID";

    SqlConnection cn = new SqlConnection("context connection=true");
    cn.Open();

    SqlCommand cmd = new SqlCommand(tsqlStatement, cn);
    
    SqlPipe myPipe = SqlContext.Pipe;
    SqlDataRecord record = new SqlDataRecord
        (
        new SqlMetaData("customerID", SqlDbType.Char,1),
        new SqlMetaData("qty", SqlDbType.Int),
        new SqlMetaData("qtySum", SqlDbType.Int)
        );
    
    string myCustomerID = "";
    Int32 myQtySum = 0;

    SqlDataReader dr = cmd.ExecuteReader();
    myPipe.SendResultsStart(record);
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            if (myCustomerID != dr["customerID"].ToString())
                myQtySum = 0;

            myCustomerID = dr["customerID"].ToString();
            myQtySum += Convert.ToInt32(dr["qty"]);

            record.SetValue(0, Convert.ToChar(dr["customerID"]));
            record.SetInt32(1, Convert.ToInt32(dr["qty"]));
            record.SetInt32(2, myQtySum);

            myPipe.SendResultsRow(record);
        }
    }
    myPipe.SendResultsEnd();

    dr.Close();
    cmd.Dispose();
    cn.Dispose();
}

Comments

# AlessandroD said on ottobre 19, 2007 12.27 :

Porca miseria sarebbe bello se SQL avesse dei costrutti nativi che, dato un resulset ordinato, permettesse di accedere subito alla riga precedente (e magari pure successiva), proprio per poter riusare quanto elaborato in precedenza per produrre quel qualcosa di necessario nella riga corrente: modalità che è appunto l'approccio sui sui è basata la soluzione a cursori (T-SQL o CLR).

SQL 2008 magari ha novità in questo senso??? Così l'ombra dell'elaborazione row-set fuori dall'engine arretrerebbe per far spazio alla luce dell'elaborazione dataset dell'engine! :-D

# abenedetti said on ottobre 19, 2007 12.50 :

Forse nel 2011... :-)

Sai quanto sarebbe contento Marc.? :-D

# orsocurioso said on ottobre 19, 2007 01.26 :

Questi confronti sono molto interessanti ed utilissimi, e confermano un test "poco formale" e fatto al volo in cui avevo notato che, quando i record sono tanti, le soluzioni CLR erano piu' performanti dei cursori.

[... vista la mia origine, .Net ero solo contento ;-))) ]

Grazie ancora.

  Franco

# AlessandroD said on ottobre 19, 2007 01.50 :

> quando i record sono tanti, le soluzioni CLR

> erano piu' performanti dei cursori

>

Occhio però a non generalizzare, solo in contesti molto precisi l'uso dei cursori è più performante, e cioè appunto dove il risultato di una riga si basa su quello precedente, avendo ovviamente definito un criterio di ordinamento.

In generale invece le elab. riga per riga fatte da noi (cioè fuori dall'engine) sono il male :-)

# abenedetti said on ottobre 19, 2007 01.57 :

In generale per operazioni set-based non c'è nulla di migliore del TSQL.

Per operazioni record per record o per operazioni cpu intensive verifico anche altre strade.

# Andrea Benedetti Blog said on settembre 23, 2012 11.23 :

Qualche anno fa (sono quasi 5 !!!) scrivevo di cursori, complessità lineari, complessità esponenziali