SQL Server optimizarea interogări de actualizare pentru volume mari de date
actualizarea tabele foarte mari poate fi un timp luând sarcină și, uneori, ar putea dura ore pentru a termina. În plus, ar putea provoca și probleme de blocare.
iată câteva sfaturi pentru SQL Server optimizarea actualizări pe volume mari de date.
- eliminarea index pe coloana care urmează să fie actualizate.
- executarea actualizării în loturi mai mici.
- dezactivarea declanșatoarelor de ștergere.
- înlocuirea Declarației de actualizare cu o operație de inserare în bloc.
acestea fiind spuse, să aplicăm punctele de mai sus pentru a optimiza o interogare de actualizare.
codul de mai jos creează un tabel fals cu 200.000 de rânduri și indici necesari.
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)
luați în considerare următoarea interogare de actualizare care trebuie optimizată. Este o interogare foarte drept înainte de a actualiza o singură coloană.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
interogarea durează 2:19 minute pentru a fi executată.
Să analizăm planul de execuție al interogării prezentat mai jos. În plus față de actualizarea indexului grupat, indicele ix_col1 este, de asemenea, actualizat. Actualizarea indexului și operațiunea de sortare împreună iau 64% din costul de execuție.
1. Eliminarea index pe coloana care urmează să fie actualizate
aceeași interogare durează 14-18 secunde atunci când nu există nici un index pe col1. Astfel, o interogare de actualizare rulează mai repede dacă coloana de Actualizat nu este o coloană cheie index. Indexul poate fi creat întotdeauna după finalizarea actualizării.
2. Executarea actualizării în loturi mai mici
interogarea poate fi optimizată în continuare executând-o în loturi mai mici. Acest lucru este, în general, mai rapid. Codul de mai jos actualizează înregistrările în loturi de 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
interogarea de mai sus durează 6-8 secunde pentru a executa. Când actualizați în loturi, chiar dacă actualizarea eșuează sau trebuie oprită, numai rândurile din lotul curent sunt derulate înapoi.
3. Dezactivarea declanșatoarelor de ștergere
declanșatoarele cu cursoare pot încetini extrem de mult performanța unei interogări de ștergere. Dezactivarea după ștergerea declanșatoarelor va crește considerabil performanța interogării.
4. Înlocuirea Declarației de actualizare cu o operație de inserare în bloc
o declarație de actualizare este o operație complet înregistrată și, prin urmare, va dura cu siguranță o cantitate considerabilă de timp dacă milioane de rânduri vor fi actualizate.Cel mai rapid mod de a accelera interogarea de actualizare este să o înlocuiți cu o operație de inserare în bloc. Este o operație minim logat în model de recuperare simplu și în vrac-logat. Acest lucru se poate face cu ușurință făcând o inserție în bloc într-un tabel nou și apoi redenumiți tabelul la unul original. Indicii și constrângerile necesare pot fi create pe un tabel nou, după cum este necesar.
codul de mai jos arată modul în care actualizarea poate fi convertită într-o operație de inserare în bloc. Este nevoie de 4 secunde pentru a executa.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
inserția în vrac poate fi apoi optimizată în continuare pentru a obține un impuls suplimentar de performanță.
Sper că acest lucru vă ajută!!!
ca-ne pe FaceBook / Urmați-ne pe Twitter / Alăturați – vă grupului SQL Server cu cea mai rapidă creștere pe FaceBook