SQL Server Ottimizzazione delle query di aggiornamento per grandi volumi di dati
L’aggiornamento di tabelle molto grandi può essere un’attività che richiede tempo e talvolta potrebbe richiedere ore per finire. Oltre a questo, potrebbe anche causare problemi di blocco.
Ecco alcuni suggerimenti per SQL Server Ottimizzare gli aggiornamenti su grandi volumi di dati.
- Rimozione dell’indice sulla colonna da aggiornare.
- Esecuzione dell’aggiornamento in batch più piccoli.
- Disabilitazione dei trigger di eliminazione.
- Sostituzione dell’istruzione Update con un’operazione Bulk-Insert.
Detto questo, applichiamo i punti precedenti per ottimizzare una query di aggiornamento.
Il codice seguente crea una tabella fittizia con 200.000 righe e indici richiesti.
CREATE TABLE tblverylargetable ( sno INT IDENTITY, col1 CHAR(800), col2 CHAR(800), col3 CHAR(800) ) GO DECLARE @i INT=0 WHILE( @i < 200000 ) BEGIN INSERT INTO tblverylargetable VALUES ('Dummy', Replicate('Dummy', 160), Replicate('Dummy', 160)) SET @i=@i + 1 ENDGOCREATE INDEX ix_col1 ON tblverylargetable(col1) GO CREATE INDEX ix_col2_col3 ON tblverylargetable(col2) INCLUDE(col3)
Considera la seguente query di aggiornamento che deve essere ottimizzata. È una query molto semplice per aggiornare una singola colonna.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
L’esecuzione della query richiede 2:19 minuti.
Diamo un’occhiata al piano di esecuzione della query mostrato di seguito. Oltre all’aggiornamento dell’indice cluster, viene aggiornato anche l’indice ix_col1. L’operazione di aggiornamento e ordinamento dell’indice insieme prende il 64% del costo di esecuzione.
1. Rimozione dell’indice sulla colonna da aggiornare
La stessa query richiede 14-18 secondi quando non c’è alcun indice su col1. Pertanto, una query di aggiornamento viene eseguita più velocemente se la colonna da aggiornare non è una colonna della chiave di indice. L’indice può sempre essere creato una volta completato l’aggiornamento.
2. Esecuzione dell’aggiornamento in batch più piccoli
La query può essere ulteriormente ottimizzata eseguendola in batch più piccoli. Questo è generalmente più veloce. Il codice seguente aggiorna i record in lotti di 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
La query di cui sopra richiede 6-8 secondi per essere eseguita. Durante l’aggiornamento in batch, anche se l’aggiornamento non riesce o deve essere interrotto, vengono ripristinate solo le righe del batch corrente.
3. Disabilitare i trigger di eliminazione
I trigger con cursori possono rallentare notevolmente le prestazioni di una query di eliminazione. La disabilitazione dopo i trigger di eliminazione aumenterà notevolmente le prestazioni della query.
4. Sostituzione dell’istruzione Update con un’operazione Bulk-Insert
Un’istruzione update è un’operazione completamente registrata e quindi richiederà sicuramente una notevole quantità di tempo se milioni di righe devono essere aggiornate.Il modo più veloce per velocizzare la query di aggiornamento è sostituirlo con un’operazione di inserimento in blocco. Si tratta di un’operazione minimamente registrato in modello di recupero semplice e Bulk-logged. Questo può essere fatto facilmente facendo un bulk-insert in una nuova tabella e quindi rinominare la tabella in quella originale. Gli indici e il vincolo richiesti possono essere creati su una nuova tabella come richiesto.
Il codice seguente mostra come l’aggiornamento può essere convertito in un’operazione di inserimento di massa. Ci vogliono 4 secondi per l’esecuzione.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
L’inserto bulk può quindi essere ulteriormente ottimizzato per ottenere un ulteriore aumento delle prestazioni.
Spero che questo aiuti!!!
Metti Mi piace su FaceBook / Seguici su Twitter / Unisciti al gruppo SQL Server in più rapida crescita su FaceBook