Il metodo più veloce per inserti, aggiornamenti, selezioni di SQL Server

Io uso SP e questa non è una domanda SP vs codice in coda “Crea il tuo comando SQL”. Sto cercando un metodo high-throughput per un’app di back-end che gestisca molte piccole transazioni. Io uso SQLDataReader per la maggior parte dei ritorni poiché forward funziona solo nella maggior parte dei casi per me.

L’ho visto fatto in molti modi, e ne ho usato la maggior parte da solo.

  1. Metodi che definiscono e accettano i parametri della stored procedure come parametri stessi e creano utilizzando cmd.Parameters.Add (con o senza specificare il tipo di valore del DB e / o la lunghezza)

  2. Assemblare i parametri SP e i relativi valori in una matrice o tabella hash, quindi passare a un metodo più astratto che analizza la raccolta e quindi esegue cmd.Parameters.Add

  3. Classi che rappresentano tabelle, inizializzando la class in base alle necessità, impostando le proprietà pubbliche che rappresentano i campi della tabella e chiamando metodi come Salva, Carica, ecc.

Sono sicuro che ci sono altri che ho visto ma non riesco a pensare al momento. Sono aperto a tutti i suggerimenti.

Questa risposta si concentra principalmente su operazioni di “selezione” vs aggiornamento / crea / cancella. Penso che sia più raro aggiornare più di uno o pochi record alla volta, quindi penso anche che “selezionare” sia il punto in cui i colli di bottiglia tendono a verificarsi. Detto questo, devi conoscere la tua applicazione (profilo). Il posto migliore per focalizzare i tempi di ottimizzazione è quasi sempre a livello di database nelle query stesse, piuttosto che nel codice client. Il codice cliente è tutto l’impianto idraulico: non è la forza principale della tua app. Tuttavia, poiché l’impianto idraulico tende a essere riutilizzato in molte app diverse, sono solidale con il desiderio di ottenerlo il più vicino ansible ottimale, e quindi ho molto da dire su come build quel codice.

Ho un metodo generico per query / procedure selezionate nel mio livello dati che assomiglia a qualcosa del genere:

private static IEnumerable Retrieve(string sql, Action addParameters) { //ConnectionString is a private static property in the data layer // You can implement it to read from a config file or elsewhere using (var cn = new SqlConnection(ConnectionString)) using (var cmd = new SqlCommand(sql, cn)) { addParameters(cmd.Parameters); cn.Open(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) yield return rdr; rdr.Close(); } } } 

E questo mi consente di scrivere metodi di livello dati pubblico che usano metodi anonimi per aggiungere i parametri. Il codice mostrato funziona con .Net 2.0+, ma può essere scritto anche più breve usando .Net 3.5:

 public IEnumerable GetFooChildrenByParentID(int ParentID) { //I could easily use a stored procedure name instead of a full sql query return Retrieve( @"SELECT c.* FROM [ParentTable] p INNER JOIN [ChildTable] c ON c.ParentID = f.ID WHERE f.ID= @ParentID", delegate(SqlParameterCollection p) { p.Add("@ParentID", SqlDbType.Int).Value = ParentID; } ); } 

Mi fermo qui per poterti indirizzare nuovamente al codice appena sopra che utilizza il metodo anonimo per la creazione dei parametri.

Questo è un codice molto pulito, in quanto mette la definizione della query e la creazione dei parametri nello stesso posto, consentendo al tempo stesso di astrarre la connessione del database boilerplate / il codice chiamante in un punto più riutilizzabile. Non credo che questa tecnica sia coperta da nessuno dei punti elenco nella tua domanda, e capita che sia anche dannatamente veloce. Penso che questo copra la spinta della tua domanda.


Voglio continuare, tuttavia, per spiegare come tutto ciò si adatta a tutti. Il resto è abbastanza semplice, ma è anche facile lanciarlo in una lista o simili e sbagliare, in ultima analisi, danneggiando le prestazioni. Quindi, procedendo, il livello aziendale utilizza una fabbrica per tradurre i risultati della query in oggetti (c # 3.0 o successiva):

 public class Foo { //various normal properties and methods go here public static Foo FooFactory(IDataRecord record) { return new Foo { Property1 = record[0], Property2 = record[1] //... }; } } 

Piuttosto che farli vivere nella loro class, potresti anche raggrupparli tutti insieme in una class statica specificamente destinata a contenere i metodi di fabbrica.

Devo apportare una modifica al metodo di recupero originale. Quel metodo “produce” sempre lo stesso object, e questo non sempre funziona così bene. Quello che vogliamo fare in modo diverso per farlo funzionare è forzare una copia dell’object rappresentato dal record corrente, in modo che quando il lettore muta per il prossimo record lavoriamo con dati puliti. Ho aspettato fino a dopo aver mostrato il metodo factory in modo che possiamo usarlo nel codice finale. Il nuovo metodo di recupero si presenta così:

 private static IEnumerable Retrieve(Func factory, string sql, Action addParameters) { //ConnectionString is a private static property in the data layer // You can implement it to read from a config file or elsewhere using (var cn = new SqlConnection(ConnectionString)) using (var cmd = new SqlCommand(sql, cn)) { addParameters(cmd.Parameters); cn.Open(); using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) yield return factory(rdr); rdr.Close(); } } } 

E ora chiameremmo il nuovo metodo Retrieve () in questo modo:

 public IEnumerable GetFooChildrenByParentID(int ParentID) { //I could easily use a stored procedure name instead of a full sql query return Retrieve(Foo.FooFactory, @"SELECT c.* FROM [ParentTable] p INNER JOIN [ChildTable] c ON c.ParentID = f.ID WHERE f.ID= @ParentID", delegate(SqlParameterCollection p) { p.Add("@ParentID", SqlDbType.Int).Value = ParentID; } ); } 

Ovviamente quest’ultimo metodo può essere esteso per includere qualsiasi logica di business aggiuntiva necessaria. Si scopre anche che questo codice è eccezionalmente veloce, perché sfrutta le caratteristiche di valutazione lenta di IEnumerable. Il rovescio della medaglia è che tende a creare molti oggetti di breve durata, e ciò può danneggiare le prestazioni transazionali richieste. Per ovviare a ciò, a volte mi divido n-tier e trasferisco gli oggetti IDataRecord direttamente al livello di presentazione ed evito la creazione di oggetti non necessari per i record che sono semplicemente associati a un controllo griglia subito.

Il codice di aggiornamento / creazione è simile, con la differenza che di solito cambi solo un record alla volta piuttosto che molti.

Oppure, potrei salvarti leggendo questo lungo post e ti dico solo di usare Entity Framework;)

Personalmente sono un grande fan della generazione di codice. Lancio il mio XML homebrew e al momento della compilazione lo eseguo tramite un XSLT per generare i miei file .CS. Descrivo il processo in questo post Utilizzo di XSLT per generare il codice dei contatori delle prestazioni . Sebbene il collegamento discuta la generazione di codice dei contatori delle prestazioni, utilizzo lo stesso processo per generare il mio DAL.

Quindi vorrei creare un XML come:

   SELECT ... FROM Customers WHERE [email protected]  

e quindi XLST lo trasformsrebbe in qualcosa di simile:

 public Customer LoadCustomerByName( SqlConnection conn, SqlTransaction trn, String name) { using (Sqlcommand cmd = new SqlCommand(@"SELECT ... FROM ...", conn, trn)) { cmd.Parameters.AddWithValue("@name", name); using (SqlDataReader rdr = cmd.ExecuteReader ()) { Customer c = new Customer(); // Load c from rdr return c; } } } 

Ora lascio molti dettagli su ciò che effettivamente fa la trasformazione XSLT, ma la cosa veramente importante è che questo metodo mi dà il controllo assoluto su come creo il mio DAL ed è flessibile in ogni aspetto, dal momento che il codice .CS generato è interamente guidato dai miei XSLT. Posso cambiare XLST e questo risulterà nella rigenerazione di ogni singolo metodo nel mio DAL. Semplifica l’esplorazione di varie soluzioni, mi consente di aggiungere strumentazione al codice (come contatori per misurare ogni singola prestazione di query e frequenza di utilizzo) e molti altri.

Questo è ciò che fondamentalmente fanno i vari progettisti VS per te, ma se fai il passo in più per controllare il processo di generazione del codice, hai molta più flessibilità e controllo sul risultato.

Più veloce per il tempo di esecuzione o più veloce per i tempi di programmazione? L’unica cosa che potresti fare per aumentare il throughput in # 1 è usare più thread e connessioni per fare gli inserti – puoi farlo con SQLCommand.BeginExecuteNonQuery

Questo è in giro da un po ‘, ma se si è aperti a usare un micro-ORM, basta usare dapper-dot-net .

Ha una syntax molto pulita, è estremamente veloce e facile da inserire in qualsiasi progetto. Viene utilizzato in produzione su StackOverflow e probabilmente questa pagina è stata creata da esso. Ha il supporto per tutti i soliti metodi SQL come metodi di estensione e supporta async su tutto.

Alcuni confronti sul rendimento:

  • Recupera le prestazioni di vari framework di accesso ai dati .NET ORM
  • Confronto di velocità: Dapper vs EF
  • Confronto delle prestazioni delle entity framework Codefluent

Non si tratta di Insert o di Update, ma ho fatto alcuni benchmark per le velocità di lettura con vari approcci. Di tutto, le rotte di DataTable sembravano più lente del gruppo. . L’approccio di Joel è fondamentalmente il più veloce che puoi ottenere ..

L’unica cosa che non mi piace è che i cannoni return return si trovano all’interno di un try … catch block. Pertanto, la gestione / registrazione delle eccezioni centralizzata non può essere soddisfatta.

Ho usato un appraoch simile, ma ho passato un object IEnumerable come parametro. Quindi non devo usare il rendimento.