Introduzione

Nei database relazionali, le operazioni vengono eseguite su un insieme di righe. Ad esempio, un’istruzione SELECT restituisce un set di righe denominato set di risultati. A volte la logica dell’applicazione deve funzionare con una riga alla volta anziché con l’intero set di risultati contemporaneamente. In T-SQL, un modo per farlo è usare un CURSORE.

Se possiedi capacità di programmazione, probabilmente useresti un ciclo come FOR o WHILE per iterare un elemento alla volta, fai qualcosa con i dati e il lavoro è fatto. In T-SQL, un CURSOR è un approccio simile e potrebbe essere preferito perché segue la stessa logica. Ma attenzione, prendi questa strada e potrebbero verificarsi problemi.

Ci sono alcuni casi in cui l’uso di CURSOR non crea un gran casino, ma generalmente dovrebbero essere evitati. Di seguito, mostreremo alcuni esempi in cui l’utilizzo di un CURSOR crea problemi di prestazioni e vedremo che lo stesso lavoro può essere svolto in molti altri modi.

Ai fini di questa dimostrazione useremo il database AdventureWorks2012 e diciamo che vogliamo ottenere alcuni dati da. table, per ogni prodotto che richiede meno di un giorno per la produzione, cioè da table ..

Un esempio di cursore

Iniziamo usando un CURSORE e scriviamo la seguente sintassi:

Dopo una breve pausa caffè , la query ha terminato l’esecuzione, restituendo 833 righe nel tempo mostrato di seguito. È importante menzionare che le sintassi scelte sopra sono solo a scopo dimostrativo e non ho effettuato alcuna regolazione dell’indice per accelerare le cose.

Nell’esecuzione del cursore, abbiamo due passaggi. Fase uno, il posizionamento, quando il cursore imposta la sua posizione su una riga dal set di risultati. Fase due, il recupero, quando ottiene i dati da quella specifica riga in un’operazione chiamata FETCH.

Nel nostro esempio, il cursore imposta la sua posizione sulla prima riga restituita dal primo SELECT e recupera il valore ProductID che corrisponde alla condizione WHERE nella variabile @id. Quindi il secondo SELECT utilizza il valore della variabile da cui ottenere i dati. e la riga successiva viene recuperata. Queste operazioni vengono ripetute finché non ci sono più righe su cui lavorare.

Infine, la sintassi CLOSE rilascia il set di risultati corrente e rimuove i blocchi dalle righe utilizzate dal cursore e DEALLOCATE rimuove il riferimento al cursore.

Le nostre tabelle demo sono relativamente piccole e contengono circa 1.000 e 500 righe. Se dovessimo scorrere le tabelle con milioni di righe, durerebbe una notevole quantità di tempo e i risultati non ci farebbero piacere.

Diamo al nostro cursore un’altra possibilità e rimuoviamo il commento dalla riga –LOCAL STATIC. Ci sono molti argomenti che possiamo usare nella definizione di un cursore, più su questo su questo link CURSOR Arguments, ma per ora concentriamoci sul significato di queste due parole.

Quando si specifica la parola chiave LOCAL, l’ambito del cursore è locale rispetto al batch in cui è stato creato ed è valido solo in questo ambito. Al termine dell’esecuzione del batch, il cursore viene rilasciato automaticamente. Inoltre, il cursore può essere referenziato da una procedura memorizzata, da un trigger o da una variabile di cursore locale in un batch.

La parola chiave STATIC crea una copia temporanea dei dati utilizzati dal cursore in tempdb in una tabella temporanea . Qui abbiamo alcuni trucchi. Un cursore STATICO è di sola lettura ed è anche indicato come cursore snapshot perché funziona solo con i dati dal momento in cui è stato aperto, il che significa che non visualizzerà alcuna modifica apportata nel database sul set di dati utilizzato dal cursore.Fondamentalmente, nessun aggiornamento, eliminazione o inserimento effettuato dopo che il cursore è stato aperto sarà visibile nel set di risultati dei cursori a meno che non chiudiamo e riapriamo il cursore.

Sii consapevole di questo prima di utilizzare questi argomenti e controlla se corrisponde alle tue esigenze. Ma vediamo se il nostro cursore è più veloce. Di seguito abbiamo i risultati:

12 secondi sono molto meglio di 4 minuti e 47 secondi, ma tieni presente il limitazioni spiegate sopra.

Se eseguiamo la sintassi utilizzando questo argomento LOCAL READ_ONLY FORWARD_ONLY otteniamo gli stessi risultati. Il cursore READ_ONLY FORWARD_ONLY può essere fatto scorrere solo dalla prima riga all’ultima. Se manca la parola chiave STATIC, il cursore è dinamico e utilizza un piano dinamico, se disponibile. Ma ci sono casi in cui un piano dinamico è peggiore di uno statico.

Cosa succede quando si rimuove il commento -LOCAL FAST_FORWARD?

FAST_FORWARD equivale a READ_ONLY e FORWARD_ONLY cursore ma ha la capacità di scegliere il piano migliore tra statico o dinamico.

LOCAL FAST_FORWARD sembra essere la scelta migliore per la sua flessibilità di scegli tra un piano statico o dinamico, ma FORWARD_ONLY fa anche molto bene. Sebbene abbiamo ottenuto il risultato in media in 12 secondi utilizzando entrambi questi metodi, questi argomenti dovrebbero essere adeguatamente testati prima di sceglierne uno.

Ci sono molti modi per ottenere lo stesso risultato, molto più velocemente e con minore impatto sulle prestazioni.

Alternativa al cursore

Un metodo è usare un JOIN e, come possiamo vedere di seguito, i risultati sono notevolmente migliori.

Innanzitutto, dobbiamo abilitare il tempo delle statistiche per misurare il tempo di esecuzione di SQL Server e creare un INNER JOIN tra due tabelle,. e . nella colonna ProductID. Dopo aver premuto il pulsante ESEGUI, abbiamo prodotto gli stessi risultati in 330 ms, rispetto alle 04:47 del metodo del cursore, e abbiamo un sorriso stampato in faccia.

Non abbiamo bisogno di scorrere ogni riga per ottenere ciò di cui abbiamo bisogno, non abbiamo più cicli, nessuna clausola while, nessun iter azioni, stiamo invece lavorando con set di dati, ottenendo ciò che vogliamo più velocemente e scrivendo meno codice.

Un uso appropriato del cursore

Ora che abbiamo visto quanto danno un Il cursore può fare, vediamo un esempio in cui possiamo usarlo.

Supponiamo di voler selezionare la dimensione e il numero di righe solo per alcune tabelle da un database. Per ottenere ciò, otterremo tutti i nomi di tabella in base ai criteri da information_schema.tables e utilizzando un CURSOR eseguiremo il ciclo di ciascuno di quel nome di tabella ed eseguiremo la stored procedure sp_spaceused passando un nome di tabella alla volta per ottenere le informazioni di cui abbiamo bisogno .

Useremo lo stesso database AdventureWorks2012 e otterremo tutte le tabelle dallo schema Sales che contiene il nome “Sales”. Per ogni nome di tabella restituito, vogliamo vedere tutte le informazioni da information_schema.tables.

Di seguito abbiamo la sintassi T-SQL e i risultati ottenuti:

Questo è un metodo in cui CURSOR è utile iterando alcuni dati una riga alla volta e ottiene il risultato necessario. In questo caso particolare, il cursore esegue il lavoro senza avere implicazioni sulle prestazioni ed è facile da usare.

Conclusioni

Ecco fatto. Abbiamo mostrato alcuni esempi con il buono, il brutto e il cattivo quando si usano i cursori. Nella maggior parte dei casi, possiamo usare JOINS, anche WHILE clausole, SSIS pacchetti o altri metodi alternativi per ottenere lo stesso risultato più rapidamente, con un minore impatto sull’output delle prestazioni e persino scrivendo meno righe di sintassi.

Quando noi hanno a che fare con l’ambiente OLTP e grandi set di dati da elaborare, la parola “CURSOR” non dovrebbe essere pronunciata.

In SQL, è una buona pratica pensare a fare operazioni su set di dati, piuttosto che pensare in modo programmatico, utilizzando iterazioni o cicli, perché questo tipo di approccio non è raccomandato né inteso per questo uso. Cercare di utilizzare loop come FOR o FOREACH dai linguaggi di programmazione e associare tale logica alle operazioni SQL, è un ostacolo per ottenere la giusta soluzione alle nostre esigenze. Dobbiamo pensare a operazioni basate su set piuttosto che a una riga alla volta per ottenere i dati di cui abbiamo bisogno.

I cursori potrebbero essere usati in alcune applicazioni per operazioni serializzate come mostrato nell’esempio sopra, ma generalmente dovrebbero essere evitati perché hanno un impatto negativo sulle prestazioni, soprattutto quando si opera su grandi set di dati.

• Autore
• Post recenti

Sono un DBA presso Yazaki Component Technology con 3 anni di esperienza in SQL Server.
Ho una laurea in ingegneria informatica e possiedo anche competenze in .NET, C # e Windows Server. Ho sviluppato esperienza in ottimizzazione e monitoraggio delle prestazioni, sviluppo T-SQL, strategia di backup, amministrazione e configurazione di database.
Sono appassionato di informatica, videogiochi, buoni film e musica elettronica mi aiuta a lavorare quando l’ufficio è rumoroso.
Visualizza tutti i post di Sergiu Onet

Ultimi post di Sergiu Onet (vedi tutti)

• Utilizzo dei cursori di SQL Server – Vantaggi e svantaggi – 23 marzo 2016