Running Totals: SQLCLR version

Published 19 ottobre 07 11.00 | abenedetti

Riprendo il post "Running totals: cursori, complessit√† lineari, complessit√† esponenziali" per presentare una terza soluzione basata su SQLCLR, ovvero tramite una stored procedure costruita ad hoc e, quindi, verificarne le performance.

Questo il codice C#:

[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();
}

Questi i risultati sulla mia macchina:

Num Record Soluzione TSQL Soluzione cursore Soluzione SQLCLR
30 0 106 10
300 30 96 203
3000 1160 513 250
30000 53060 2643 1030

Risultati interessanti, no?

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

This Blog

Syndication