I cursori possono apparire come scorciatoie per uno sviluppatore. Quando si dispone di un lavoro complesso da eseguire ed è necessario manipolare le righe in una tabella, il modo più rapido può sembrare scorrere le righe una per una utilizzando un cursore Transact-SQL. Dopotutto, dal momento che devi scorrere le strutture dati nel tuo codice sul lato client, potresti essere tentato di fare lo stesso quando hai a che fare con i dati di SQL Server., Ma l’iterazione dei dati utilizzando i cursori Transact-SQL spesso non si adatta bene e spero di convincerti che non è anche una buona pratica di progettazione o architettura.

A Cursor Experience

Lo porto perché alcuni mesi fa, ho avuto a che fare con lo script Transact-SQL di un fornitore che ha aggiornato il componente del database a una nuova versione dell’applicazione del fornitore. Hanno progettato lo script per ruotare una tabella molto grande e memorizzare i dati rilevanti in una nuova tabella orizzontalmente, come stringhe concatenate., Il fornitore voleva migliorare le prestazioni rendendo la tabella più piccola, quindi ha deciso di memorizzare i dati di dettaglio orizzontalmente, come stringhe delimitate da virgole per ogni ID genitore. L’applicazione client potrebbe interrogare le stringhe delimitate da virgole risultanti più velocemente rispetto a ciascuna di esse come singole righe e, nel contesto, la modifica aveva senso e migliorava le prestazioni dell’applicazione.

Tuttavia, lo script Transact-SQL del fornitore per ruotare i dati durante l’aggiornamento ha richiesto 16 ore per essere eseguito su una macchina di prova e il cliente non poteva permettersi più di alcune ore di inattività per l’aggiornamento., Quando abbiamo esaminato lo script del fornitore, abbiamo visto che lo sviluppatore aveva codificato il processo di pivoting in due passaggi: un cursore per scorrere tutti gli ID della tabella padre per creare una tabella preformattata vuota, e poi un altro script per concatenare le stringhe, di nuovo usando un cursore.

Utilizzando un approccio basato su set, siamo stati in grado di ridurre il tempo di elaborazione da oltre 16 ore a meno di cinque minuti. Abbiamo seguito la strategia originale dello sviluppatore, costruendo la tabella vuota usando le istruzioni SELECT e abbiamo ridotto il tempo per quel passaggio a meno di due minuti., Abbiamo quindi concatenato le stringhe utilizzando un’istruzione UPDATE, eseguita per id genitore. La nostra iterazione attraverso gli ID genitore utilizzava un ciclo WHILE e terminava in meno di tre minuti.

L’inevitabilità dell’iterazione

Molti accessi ai dati del database devono essere iterativi in qualche modo per preparare i dati per ulteriori manipolazioni. Anche il motore SQL Server itera i dati quando esegue la scansione o unisce i dati utilizzando i vari tipi di join disponibili. È possibile visualizzarlo quando si esamina il piano di query SQL Server per una query che restituisce molte righe da un set di dati di grandi dimensioni., Per un join, vedrai più comunemente un ciclo nidificato, ma a volte anche un unione o un join hash. Per query più semplici, è possibile visualizzare una scansione dell’indice cluster o non cluster. È solo nei casi in cui SQL Server può restituire una singola riga o un piccolo insieme di righe e la tabella ha un indice appropriato, che vedrai una ricerca utilizzando un indice.

Pensaci: Microsoft ha ottimizzato e sintonizzato il motore di SQL Server per anni per scorrere i suoi dati disponibili nel modo più efficiente possibile., Immagina, se avessi il tempo e fossi disposto a spendere l’energia, potresti probabilmente scrivere accessi di basso livello ai file di dati del database che sarebbero piuttosto efficienti. Tuttavia, sarebbe efficiente solo per l’attività individuale di fronte a te, e dovresti eseguirne il debug e potrebbe essere necessario riscriverlo completamente se l’ambito dell’accesso ai dati dovesse cambiare. Probabilmente ci vorranno anni per ottenere davvero il codice completamente ottimizzato e generalizzato, e anche allora non saresti vicino all’efficienza del codice all’interno del motore di archiviazione di SQL Server.,

Allora, dov’è il guadagno nel reinventare la ruota? È solo perché il motore di SQL Server è così ben ottimizzato e debug, che è meglio lasciarlo fare l’iterazione per voi e sfruttare l’ampio sviluppo e test che è già incorporato nel database.

Se guardi più da vicino le tue attività di elaborazione dei dati, penso che scoprirai che ci sono davvero pochissime occasioni in cui sono richiesti cursori. Prima di tutto, spesso è possibile raggiungere il proprio obiettivo facendo affidamento sui comandi SQL basati su set in Transact-SQL e ignorando l’ordine delle righe di una tabella., In secondo luogo, i cursori Transact-SQL sono solo un modo per scorrere una tabella riga per riga. Se è possibile identificare in modo univoco ogni riga di una tabella che è necessario iterare, è possibile utilizzare un ciclo WHILE anziché un cursore e potenzialmente ottenere prestazioni migliori. Lasciate che vi accompagni attraverso un esempio per mostrarvi perché.

Confrontando le strategie di iterazione

si supponga di poter identificare in modo univoco ogni riga di una tabella perché la tabella ha una chiave univoca o un gruppo univoco di colonne., In un ciclo WHILE, tutto ciò che devi fare è trovare il valore più basso della condizione univoca e quindi trovare il valore più alto successivo ogni volta che esegui l’iterazione. Ecco un esempio dalla produzione di database di esempio di SQL Server 2005 AdventureWorks.Tabella TransactionHistory. Ha un indice cluster sulla chiave primaria e il ciclo WHILE può cercare nella riga ogni volta.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Ecco lo stesso ciclo che utilizza un cursore FAST FORWARD, che è il tipo più efficiente di cursore Transact-SQL solo per leggere i dati:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

Sul mio portatile, dopo averlo eseguito alcune volte per assicurarsi che i dati siano tutti nella cache, il ciclo WHILE impiega nove secondi e il cursore impiega 17 secondi. Le tue durate possono variare. Si noti che anche se l’esempio non fa davvero nulla con i dati, il ciclo WHILE è più rapido. Il cursore aggiunge evidentemente più overhead.,

Il cursore richiede anche comandi aggiuntivi, che rendono il codice disordinato. Senza entrare nei dettagli di come funzionano i cursori, che Microsoft spiega completamente nei libri online di Microsoft SQL Server 2005, si noti che quando si utilizza un ciclo WHILE, non è necessario dichiarare, aprire, chiudere e deallocare nulla. La logica è più semplice e puoi persino aggiornare liberamente le righe lungo la strada. Per aggiornare le righe usando il cursore, dovrai cambiare il tipo di cursore.

Anche un ciclo WHILE aggiunge il sovraccarico dell’iterazione., Potresti essere in grado di sostituirlo con un comando SELECT basato su set o sostituire gli aggiornamenti che volevi fare nel tuo ciclo con il comando UPDATE basato su set e lasciare l’iterazione al motore di SQL Server. Una semplice istruzione SELECT per ottenere gli stessi dati del nostro cursore e WHILE loop sopra richiede meno di 3 secondi e restituisce le righe al client, che è più lavoro rispetto ai due loop precedenti.

SELECT *FROM Production.TransactionHistory

Questa SELEZIONE si basa su SQL Server per iterare i dati ed è di gran lunga il più veloce dei tre metodi di accesso ai dati che abbiamo esaminato.,

Dai sacchetti ai set

A volte i cursori potrebbero sembrare necessari. Quando devi semplicemente scorrere i dati del database, riga per riga, nel loro ordine fisico, a volte funzionerà solo un cursore. Questo accade più comunemente quando si hanno righe duplicate e non c’è modo di identificare in modo univoco una determinata riga nella tabella. Queste tabelle sono borse, non set, di dati, poiché una “borsa” non elimina i valori duplicati, come fa un set.

Tali sacchi di dati di solito si verificano quando si importano dati da un’origine esterna e non è possibile fidarsi completamente dei dati., Ad esempio, se la tabella della cronologia delle transazioni di AdventureWorks non contiene un gruppo di colonne che è possibile chiamare univoche e/o righe duplicate, è possibile che sia necessario utilizzare un cursore.

Tuttavia, è sempre possibile trasformare un sacchetto di righe in una tabella normalizzata. Anche se si dispone di righe duplicate in una tabella o di nessun set di colonne su cui è possibile fare affidamento per l’unicità, è possibile aggiungere una colonna di identità alla tabella e seminare l’identità per iniziare la numerazione con 1. Questo aggiunge una chiave univoca alla tabella, che consente di utilizzare un ciclo WHILE invece di un cursore., Una volta che si dispone di una chiave univoca, è possibile rimuovere i duplicati utilizzando il comando di aggiornamento basato su set Transact-SQL.

L’API logica per i dati del database

Utilizzando le operazioni set-base è meglio che iterare i dati da soli in almeno due modi.

Innanzitutto, i comandi SQL basati su set sono più efficienti perché si utilizza il motore altamente ottimizzato di SQL Server per eseguire l’iterazione. Se si esegue l’iterazione dei dati da soli, non si utilizza il motore di archiviazione di SQL Server in modo ottimale. Invece, lo stai riempiendo di comandi per recuperare solo una singola riga alla volta., Ogni volta che si richiede una singola riga, il comando deve passare attraverso SQL Server optimizer prima che possa arrivare al motore di archiviazione e si finisce per non utilizzare il codice ottimizzato di SQL Server Storage engine. Se hai iterato te stesso, ti affidi anche a informazioni fisiche estranee sulla tabella, ovvero l’ordine delle righe, durante l’elaborazione dei dati. I comandi set-base Transact-SQL SELECT, UPDATE ed DELETE offrono un modo per ignorare l’ordine delle righe e influenzarle in base alle caratteristiche dei dati, e sono più veloci.,

In secondo luogo, i comandi basati su set sono più logici perché pensare ai dati nei set ti astrae da dettagli estranei che sono più interessati a come i dati sono effettivamente ordinati. Infatti, i comandi basati su set come SELECT, UPDATE ed DELETE, se applicati alle tabelle direttamente e non in un cursore o in un ciclo WHILE, ti avvicinano logicamente ai tuoi dati, proprio perché puoi ignorare l’ordine dei dati.,

Ecco un altro modo di pensare a questo secondo punto-Proprio come le stored procedure sono l’API più naturale per le applicazioni di interfacciarsi con SQL Server a livello di codice, così i comandi SQL basati su set sono l’API appropriata per l’accesso ai dati relazionali. Le stored procedure disaccoppiano l’applicazione dagli interni del database e sono più efficienti delle query ad hoc. Allo stesso modo, i comandi SQL set-base all’interno di Transact-SQL offrono un’interfaccia logica ai dati relazionali e sono più efficienti perché si fa affidamento sul motore di archiviazione di SQL Server per l’iterazione dei dati.,

La linea di fondo non è che l’iterazione attraverso i dati sia cattiva. In realtà, spesso è inevitabile. Piuttosto, il punto è, lasciare che il motore di archiviazione lo faccia per te e fare affidamento invece sull’interfaccia logica dei comandi Transact-SQL basati su set. Penso che troverai poche situazioni in cui devi effettivamente utilizzare un cursore Transact-SQL.