SQL Server Loop attraverso le Righe della Tabella senza Cursore

Da: Daniele Farina | Aggiornamento: 2019-09-12 | Commenti (2) | Correlati: Più > T-SQL

Problema

Youhave sempre sentito dire che si dovrebbe evitare di cursori abbondanti codice T-SQL come SQL Server best practice, becausecursors sono dannosi per la performanceand a volte causare problemi. Ma a volte c’è bisogno di scorrere ildati una riga alla volta, quindi in questo suggerimento vedremo un confronto su come fareun ciclo senza usare il cursore.,

Soluzione

Sappiamo tutti che SQL Server, come ogni database relazionale consente all’utente di eseguire operazioni basate su set di moduli. Inoltre, come fanno molti fornitori di database, SQL Server include un’estensione procedurale che è il linguaggio T-SQL. Aggiunge costrutti trovati in linguaggi procedurali che consentono una codifica più semplice agli sviluppatori. Theseconstructs è stato aggiunto per una ragione e qualche volta questo è l’unico approccio al compito a portata di mano.,

Utilizzando un ciclo While invece di cursori in SQL Server

Se avete mai lavorato withcursors, si può trovare questo titolo un po ‘ confusingbecause dopo tutto, cursors utilizza mentre costrutti per iterare tra le righe. Ma oltre a questo, voglio mostrarti che in alcune circostanze quando usiamo un cursore per iterateover un insieme di righe possiamo cambiarlo in un ciclo while. In questi casi, l’unica sfidasarà quello di scegliere una condizione di uscita corretta.,

Pro e contro dell’utilizzo di cursori per scorrere le righe della tabella in SQL Server

Non tutto è sbagliato con i cursori, ma hanno anche alcuni vantaggi rispetto ad altre tecniche di loop.

• I cursori sono aggiornabili: quando si crea un cursore, si utilizza una query per definirlo utilizzando l’istruzione DECLARE CURSOR. Utilizzando l’opzione AGGIORNA nell’istruzione cursorcreation, è possibile aggiornare le colonne all’interno del cursore.,
• È possibile spostarsi avanti e indietro in un cursore: utilizzando l’opzione di SCORRIMENTO nell’istruzione DECLARE CURSOR è possibile navigare tra i record del cursore in entrambe le direzioni con le opzioni di recupero FIRST, LAST, PRIOR, NEXT, RELATIVE andABSOLUTE. Tieni presente che l’opzione di scorrimento non è compatibile con le opzioni FORWARD_ONLYand FAST_FORWARD.
• I cursori possono essere passati alle stored procedure: se si utilizza l’opzione GLOBAL per creare un cursore, può essere utilizzato in qualsiasi stored procedure o batch eseguito nella stessa connessione. Ciò consente di utilizzare cursori su stored procedure nidificate.,
• I cursori hanno molte opzioni diverse: con i cursori hai la possibilità di usare diverse opzioni che influenzano il modo in cui si comporteranno per quanto riguarda il blocco.
• I cursori non hanno bisogno di una condizione: usando i cursori, stai gestendo un insieme di righe come record. Questo ti permette di muoverti attraverso il cursore senzala necessità di avere una condizione booleana. Ad esempio, è possibile creare un cursorecon il nome dei database che risiedono su un’istanza di SQL Server senza bisogno di una chiave surrogata per funzionare come condizione di test come in un ciclo WHILE.,

Ci sono anche alcuni aspetti negativi che dovresti essere consapevole quando usi cursorsinvece di altre opzioni di loop.

• Se si utilizzano cursori globali nel codice, si corre il rischio di affrontare gli errori a causa della chiusura di un cursore da parte di alcune stored procedure nidificate nel codice.
• Di solito i cursori hanno meno prestazioni di un ciclo equivalente che utilizza un WHILEloop o CTE.

Pro e contro dell’utilizzo di un ciclo While per scorrere le righe della tabella in SQL Server

Ci sono anche vantaggi nell’utilizzare un ciclo WHILE rispetto a un cursore.,

• Mentre i loop sono più veloci dei cursori.
• Mentre i loop utilizzano meno blocchi rispetto ai cursori.
• Meno utilizzo di Tempdb: mentre i loop non creano una copia di data intempdb come fa un cursore. Ricorda che i cursori, a seconda delle opzioni tuutilizzare per crearli può causare la creazione delle tabelle temporanee.

L’elenco successivo descrive gli aspetti negativi dei cicli WHILE.

• Andare avanti o indietro è complesso: per andare avanti o indietro in aloop è necessario modificare dinamicamente la condizione di iterazione all’interno del ciclo.,Ciò richiede particolare attenzione; altrimenti si può finire in un ciclo infinito.
• Il rischio di un ciclo infinito: rispetto a un cursore, non si dispone di un set fisso di dati in loop (cioè i dati restituiti dalla dichiarazione SELECT nella dichiarazione cursor), invece quando si utilizza un ciclo WHILE è necessario definire un boundarywith un’espressione che viene valutata a true o false.

Costruire l’ambiente di test per Cursori e Loop

Per testare questo, userò una tabella con una colonna identity (CursorTestID),una colonna varchar (Filler) e una colonna bigint (RunningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO 

L’idea è di scorrere le righe della tabella ordinate dalla colonna CursorTestID e aggiornare la colonna RunningTotal con la somma del valore della colonna CursorTestID e il valore della colonna RunningTotal della riga precedente.

Ma prima di iniziare, prima dobbiamo generare alcune righe di test con lo script successivo.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

Nello script sopra si noterà che ho usato solo un singolo statementand ho approfittato del separatore batch (il comando GO 500000) come shortcuttper eseguire questa istruzione insert 500000 volte. Puoi leggere di più su questo metodoper ripetere l’esecuzione batch su questo suggerimento: eseguire un batch T-SQL più volte usando GO.

Esempio di un cursore di base per scorrere le righe della tabella in SQL Server

Creiamo un cursore per riempire la colonna RunningTotal. Si noti sul nextscript che ho dichiarato il cursore con l’opzione FAST_FORWARD., Questo viene fatto inorder per migliorare le prestazioni del cursore perché secondo Microsoft theFAST_FORWARD argomento “Specifica un FORWARD_ONLY, READ_ONLY cursore con performanceoptimizations abilitato”” In altre parole, stiamo istruendo SQL Server per usareun cursore di sola lettura che può solo andare avanti e scorrere dalla prima all’ultima riga.

DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO

L’immagine successiva è una cattura dello schermo che mostra l’esecuzione dello script above.As potete vedere, ci sono voluti tre minuti e cinque secondi per aggiornare le 500.000 righe della nostra tabella di test.,

Esempio di un ciclo While di base per scorrere le righe della tabella in SQL Server

Ora riscriverò lo script precedente evitando l’uso di un cursore. You willnotice che contiene un ciclo While che è quasi identico a quello nello script thecursor. Questo è, come ho detto in precedenza, perché anche quando si lavora con i cursoriè necessario utilizzare una struttura di controllo iterativo.

L’immagine successiva è una cattura dello schermo dell’esecuzione dello script sopra. Ha richiesto tempo per eseguire il ciclo while rispetto al cursore.,

Un altro esempio di cursore SQL Server

Prendiamo ad esempio il cursore nel tipStandardize SQL Server data con text lookup and replace function. Una parola ofadvice, per eseguire questo codice, è necessario seguire i passaggi nella punta per createthe ambiente di test.

Ed ecco il codice del cursore:

Se sezioniamo questo codice, possiamo vedere che c’è un cursore che passa attraversoi prodotti della tabella che ho copiato di seguito.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products 

Esempio di cursore SQL Server Convertito in un ciclo While

Per sostituire questo cursore con un CICLO WHILE, è necessario creare una tabella temporanea per implementare una tabella di conteggio. Per tutti coloro che non sanno cos’è un tallytable, possiamo definirlo come una tabella che contiene una coppia di colonne costituite da una chiave e dal suo valore. Nel nostro caso particolare useremo un integerkey sequenziale a partire da 1, quindi possiamo usarlo come iteratore. Questa chiave sarà associata a un ProductID dalla tabella Products.,

All’inizio, poiché la tabella Products ha la chiave ProductID definita come identity potresti essere tentato di ignorare questo passaggio, ma devi considerare che in un caso realeuna riga potrebbe essere stata eliminata, quindi non sarai in grado di utilizzare identitycolumn come iteratore. Inoltre una riga può essere cancellata mentre stiamo eseguendo ourcode e potrebbe portare a errori di esecuzione. Per evitare questo ci accingiamo ad aggiungere aTRY-CATCHblock. Approfondirò ulteriormente la questione.

Prima di iniziare il ciclo WHILE, dobbiamo impostare la sua condizione di avvio e arresto., Per questa materia ho aggiunto due nuove variabili intere denominate @ Iterator e @ MaxIterator.La variabile @ MaxIterator viene utilizzata per mantenere il numero di elementi nel #TallyTabletable e impostiamo il suo valore solo una volta prima di iniziare il ciclo. Il @ Iterator variableis inizializzato a 1, come lo abbiamo definito come il numero iniziale sulla sequenza e aumenteremo il suo valore ad ogni iterazione.

I prossimi passi

• Sei nuovo ai cursori e hai bisogno di un po ‘ di pratica? Nel nexttip troverai una spiegazione, un esempio di cursore facile da capire e altre letture consigliate: SQL Server Cursor Example.,
• Se vuoi convertire i cursori esistenti nel tuo codice per impostare query basate,dai un’occhiata a questo Capitolosql Server Convert Cursorto Set Basato sul tutorial sulle best practice di progettazione del database del server SQL.
• Hai bisogno di un altro esempio sull’utilizzo di un ciclo While? Date un’occhiata a questo tipthat vi mostrerà come dividere le istruzioni DML in batch:Ottimizzare grandi SQL ServerInsert, aggiornare ed eliminare i processi utilizzando batch.
• Nel caso in cui non sai come usare PROVA…CATTURA exceptionhandling, dai un’occhiata a questo suggerimento:SQL Server Prova e Cattura la gestione delle eccezioni.,
• Restate sintonizzati su theSQL Server T-SQL Tipscategory per ottenere più idee di codifica.

Ultimo Aggiornamento: 2019-09-12

Circa l’autore

Daniele Farina, nato a Buenos Aires, in Argentina. Autodidatta, fin dall’infanzia ha mostrato una passione per l’apprendimento.
Visualizza tutti i miei suggerimenti

Risorse correlate

• Altri suggerimenti per gli sviluppatori di database…