Più chiamate simultanee a SqlCommand.BeginExecuteNonQuery che utilizza lo stesso SqlConnection

Ho un codice C # funzionante che usa un SqlConnection per creare tabelle temporanee (ad esempio #Foo), chiama proc memorizzati per riempire quelle tabelle temporanee e restituire i risultati al client C #, usa c # per eseguire calcoli complessi su quei risultati e usa il risultati del calcolo per aggiornare una delle tabelle temporanee create in precedenza.

A causa delle tabelle temporanee utilizzate in tutto il processo, è necessario disporre di una sola SqlConnection.

Ho identificato un collo di bottiglia delle prestazioni nell’aggiornamento della tabella temporanea con i risultati del calcolo. Questo codice stava già eseguendo il batch degli aggiornamenti per impedire al client C # di esaurire la memoria. Ogni batch di dati calcolati è stato inviato a un processo memorizzato tramite SqlCommand.ExecuteNonQuery e la sproc in aula aggiorna la tabella temporanea. Il codice passava la maggior parte del tempo in questa chiamata a ExecuteNonQuery.

Quindi, l’ho modificato in BeginExecuteNonQuery, insieme al codice per attendere i thread e chiamare EndExecuteNonQuery. Questo ha migliorato le prestazioni di circa un terzo, ma sono preoccupato di avere più chiamate simultanee a SqlCommand.BeginExecuteNonQuery utilizzando lo stesso SqlConnection.

Va bene o mi imbatterò in problemi di threading?

Scusa per la lunga spiegazione.

Lo stato dei documenti MSDN:

Il metodo BeginExecuteNonQuery restituisce immediatamente, ma finché il codice non esegue la chiamata del metodo EndExecuteNonQuery corrispondente, non deve eseguire altre chiamate che avviano un’esecuzione sincrona o asincrona sullo stesso object SqlCommand.

Ciò sembra implicare che diversi oggetti SqlCommand possono chiamare BeginExecuteNonQuery prima del completamento del primo SqlCommand.

Ecco un codice che illustra il problema:

private class SqlCommandData { public SqlCommand Command { get; set; } public IAsyncResult AsyncResult { get; set; } } public static void TestMultipleConcurrentBeginExecuteNonQueryCalls(string baseConnectionString) { var connectionStringBuilder = new SqlConnectionStringBuilder(baseConnectionString) { MultipleActiveResultSets = true, AsynchronousProcessing = true }; using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString)) { connection.Open(); // ELIDED - code that uses connection to do various Sql work SqlDataReader dataReader = null; // in real code, this would be initialized from calls to SqlCommand.ExecuteReader, using same connection var commandDatas = new List(); var count = 0; const int maxCountPerJob = 10000; while (dataReader.Read()) { count++; // ELIDED - do some calculations on data, too complex to do in SQL stored proc if (count >= maxCountPerJob) { count = 0; var commandData = new SqlCommandData { Command = new SqlCommand {Connection = connection} }; // ELIDED - other initialization of command - used to send the results of calculation back to DB commandData.AsyncResult = commandData.Command.BeginExecuteNonQuery(); commandDatas.Add(commandData); } } dataReader.Close(); WaitHandle.WaitAll(commandDatas.Select(c => c.AsyncResult.AsyncWaitHandle).ToArray()); foreach (var commandData in commandDatas) { commandData.Command.EndExecuteNonQuery(commandData.AsyncResult); commandData.Command.Dispose(); } // ELIDED - more code using same SqlConnection to do final work connection.Close(); } } 

Beh, a rischio estremo di ricevere molti voti negativi, devo commentare questo. In primo luogo, questa è una buona domanda e ben affermata per affrontare il potenziale problema specifico che hai citato. Tuttavia, hai trascurato di discutere di questo processo “lungo” che stai cercando di realizzare.

La mia esperienza mi ha pensato una cosa …

Se la domanda a cui stai chiedendo è difficile rispondere, cambia la domanda.

Sebbene io sappia molto poco del tuo problema specifico, penso che ciò si adatti perfettamente alla tua situazione. Come altri hanno già detto … Le tabelle temporanee sono sgradevoli, creare le proprie tabelle per un’attività specifica è ancora più difficile, l’aggiornamento di grandi quantità di dati in SQL è costoso.

Chiediti “puoi evitare tutto?”

Molto spesso le persone scelgono di implementare una logica estremamente complicata nei database perché credono che SQL possa farlo più velocemente. In pratica, questo è un concetto imperfetto, i database sono dispositivi di archiviazione / serializzazione, sono utili per archiviare, aggiornare, localizzare e sincronizzare l’accesso ai dati. Non sono ben equipaggiati per l’elaborazione di operazioni complesse. Anche dopo la bastardizzazione del database da parte di Microsoft (e altri) mediante l’iniezione di linguaggi di sviluppo completo, non può funzionare in modo ottimale come un client ben scritto (* a seconda della complessità delle operazioni, che sospetto tu abbia superato).

Ad esempio, si dispone di un database di circa 2 GB di dati non elaborati. Si desidera produrre un report o un’analisi complessi sull’intero set di dati. Semplicemente, è sufficiente disporre di 2 GB di memoria, borseggiando l’intero database (o la porzione necessaria) in memoria utilizzando dizionari o qualsiasi altra cosa per creare le ricerche necessarie. A seconda di diversi fattori, l’intera operazione probabilmente eseguirà diverse volte più velocemente di SQL, può essere facilmente testata in unità e sarà (IMHO) notevolmente più facile da build, eseguire il debug e gestire rispetto a un brutto assortimento di SPROC che costruiscono SQL dinamico. Anche con più di 2 GB di dati non elaborati, è ansible creare facilmente cache client con diverse tecnologie esistenti (B-Trees, ISAM o simili).

Il prodotto su cui lavoro oggi ha 2.4 TB di dati nel database e non abbiamo una sola dichiarazione sproc, join, o anche una clausola di non uguaglianza.

Ma purtroppo il mio consiglio può o non può essere pertinente alle tue circostanze specifiche poiché non conosco i tuoi obiettivi o vincoli. Spero che, se non altro, ti farà chiedere a te stesso:

“Sto facendo la domanda giusta?”

È ansible utilizzare uno schema produttore-consumatore con 2 thread e 2 connessioni SQL simultanee ma indipendenti.

Il produttore (1 ° thread) ha il DataReader (1a connessione sql) e scrive i risultati su una coda di blocco. Il consumatore (secondo thread) legge dalla coda, ha l’ExecuteNonQuery (2a connessione sql) e scrive nella / e tabella / i temp.

Un’altra idea nel caso in cui i comandi ExecuteNonQuery sono fondamentalmente più INSERT : ExecuteNonQuery ha un overload con un StringCollection per inviare più istruzioni SQL come un’unica operazione.

Può esistere un solo DataReader associato a un object Command e possono esserci numerosi oggetti Command associati alla stessa connessione. L’unica cosa che non puoi fare qui è usare lo stesso comando con parametri diversi.

Tuttavia, quando si avvia una transazione di database (implicita se non esplicita), le risorse associate a tale transazione vengono bloccate fino a quando la transazione non viene confermata o ripristinata e tutti i processi che desiderano interrogare tali risorse vengono messi in coda. SQL Server gestisce le code piuttosto bene. Ho avuto alcuni problemi con deadlock a causa dell’elevato carico del server in SQL Server 2000, ma non c’erano tali problemi con le versioni successive.

È strano che tu abbia effettivamente ricevuto un miglioramento delle prestazioni. Questo mi fa pensare che hai una grande quantità di dati, che richiede tempo per elaborare quando si invia a SQL Server. Durante la trasmissione di blocchi, viene impiegato meno tempo perché la trasmissione dei dati e l’elaborazione dei dati vengono eseguiti contemporaneamente.

Ad ogni modo, non dovrebbe esserci alcun problema.

Tuttavia, considerare l’utilizzo degli assembly CLR (se questa opzione è disponibile) per elaborare le informazioni direttamente nel motore del database, senza traffico TCP.

Sì, davvero una bella domanda.

Forse è ansible utilizzare una funzionalità introdotta in SQL Server 2005, denominata MARS: http://msdn.microsoft.com/en-us/library/ms345109(v=sql.90).aspx

MARS consente di riutilizzare la stessa connessione per le letture e le scritture, tuttavia ha alcune limitazioni e, francamente, non conosco nessuno che la userebbe.

Da quello che vedo però, forse sarebbe ansible guardare il tuo problema da un altro punto. Forse, invece di usare le tabelle temporanee e doverle tenere d’occhio attraverso l’intero processo, che alla fine deve essere sincrono, forse puoi creare una serie di tabelle permanenti che conterranno la colonna aggiuntiva JobId. Quindi non sei vincolato al thread singolo. Puoi avere una tabella che manterrà la cronologia dei lavori. Una volta che inserisci una riga in questa tabella, recuperi scope_identity () e lo aggiungi a tutti gli elementi del tuo algoritmo. Le tabelle possono contenere più di una copia dei risultati alla volta e qualsiasi query che legge o aggiorna i dati utilizza il JobId come identificatore di serie. Se indicizzate correttamente le tabelle, avrete un design molto fluido che sarà molto più scalabile rispetto alla soluzione che state cercando di implementare ora.

Saluti

Piotr

Questa è una domanda se l’esecuzione di istruzioni di modifica dei dati ti aiuta. MARS è l’acronimo di più set di risultati attivi: il set di risultati è il risultato delle SELECT o FETCH e in .NET generalmente indica che è ansible avere più DataReader aperti sulla stessa connessione. Ma qualsiasi operazione di modifica dei dati è considerata atomica e deve essere completata prima che possa essere eseguita un’altra operazione (o il recupero dei dati dal set di risultati può continuare) – leggi qui . Quindi penso che i tuoi comandi asincroni siano e siano ancora eseguiti in sequenza.

È ansible utilizzare più connessioni se la connessione principale crea la tabella temporanea globale ##TempName anziché #Temp . La tabella temporanea globale dovrebbe essere visibile da un’altra sessione mentre la sessione principale è ancora triggers.

Perché non eseguire entrambi i comandi da due richieste di thread asincroni utilizzando ExecuteNonQuery invece di BeginExecuteNonquery e lasciare che il pool di connessioni risolva il problema in un modo più tradizionale? Quindi aspetta il threadpool.

Questo non è sicuramente sicuro. Potrebbe teoricamente funzionare bene per sempre, ma sarà sempre in pericolo di fallire. Peggio ancora, poiché non è supportato, potrebbe fallire in un modo che non si nota, come restituire dati non validi, invece di lanciare un’eccezione.

Ciò che MARS ti permette di eseguire è eseguire comandi contro una connessione mentre stai leggendo un set di risultati. Ciò è utile, ad esempio, se si desidera elaborare ogni riga di un risultato e inviare gli aggiornamenti al database senza caricare prima l’intero set di risultati nell’applicazione. Tuttavia, NON consente di inviare più comandi contemporaneamente alla stessa connessione.

A seconda delle esigenze, potresti essere in grado di utilizzare MARS in modo appropriato per aumentare le prestazioni senza multi-threading. In caso contrario, sarà necessario utilizzare più connessioni, che richiedono l’utilizzo di tabelle temporali globali o tabelle permanenti. Anche in questo caso, dovrai stare attento ad evitare deadlock e progettare le tue query in modo tale che il blocco non distrugga il vantaggio che stai cercando di ottenere da più connessioni.

Non sono sicuro che questa domanda sia ancora importante, ma …

Prova a spostare la linea –

connection.Open ();

giù dall’inizio del tuo utilizzo, a dopo il nuovo sqlCommand ma prima di BeginExecuteNonQuery .. come questo –

  var commandData = new SqlCommandData { Command = new SqlCommand {Connection = connection} }; connection.Open(); // ELIDED - other initialization of command - used to send the results of calculation back to DB commandData.AsyncResult = commandData.Command.BeginExecuteNonQuery();