SQL Server Optimalisere Oppdateringsspørringer For Store Datamengder
Oppdatere svært store tabeller Kan være en tid tar oppgave, og noen ganger kan det ta timer å fullføre. I tillegg til dette kan det også føre til blokkeringsproblemer.
her er noen tips TIL SQL Server Optimalisere oppdateringene på store datamengder.
- Fjerner indeksen på kolonnen som skal oppdateres.
- Utfører oppdateringen i mindre grupper.
- Deaktiverer Slett utløsere.
- Erstatte Update-setningen med En Masseinnsats-operasjon.
Med det sagt, la oss bruke punktene ovenfor for å optimalisere en oppdateringsspørring.
koden nedenfor lager et dummy-bord med 200 000 rader og nødvendige indekser.
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)
Vurder følgende oppdateringsspørring som skal optimaliseres. Det er en veldig rett frem spørring for å oppdatere en enkelt kolonne.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
spørringen tar 2:19 minutter å kjøre.
La oss se på utførelsesplanen for spørringen som vises nedenfor. I tillegg til den grupperte indeksoppdateringen oppdateres indeksen ix_col1 også. Indeksoppdateringen og Sorteringsoperasjonen tar sammen 64% av utførelseskostnaden.
1. Fjerne indeksen på kolonnen som skal oppdateres
den samme spørringen tar 14-18 sekunder når det ikke er noen indeks på col1. Dermed kjører en oppdateringsspørring raskere hvis kolonnen som skal oppdateres, ikke er en indeksnøkkelkolonne. Indeksen kan alltid opprettes når oppdateringen er fullført.
2. Utfør oppdateringen i mindre grupper
spørringen kan optimaliseres ytterligere ved å utføre den i mindre grupper. Dette er generelt raskere. Koden nedenfor oppdaterer postene i grupper av 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
ovennevnte spørring tar 6-8 sekunder å utføre. Når du oppdaterer i grupper, selv om oppdateringen mislykkes eller den må stoppes, rulles bare rader fra gjeldende batch tilbake.
3. Deaktivering Av Slett utløsere
Utløsere med pekere kan ekstremt redusere ytelsen til en slettespørring. Deaktivering Etter slette utløsere vil betydelig øke spørringen ytelse.
4. Erstatte Update statement med En Bulk-Insert operasjon
en update statement er en fullt logget operasjon, og dermed vil det sikkert ta betydelig tid hvis millioner av rader skal oppdateres.Den raskeste måten å øke hastigheten på oppdateringsspørringen er å erstatte den med en masseinnsats-operasjon. Det er en minimalt logget operasjon i enkel Og Bulk-logget utvinning modell. Dette kan gjøres enkelt ved å gjøre en bulk-sett inn i en ny tabell og deretter endre navn på tabellen til den opprinnelige. De nødvendige indekser og begrensning kan opprettes på en ny tabell etter behov.
koden nedenfor viser hvordan oppdateringen kan konverteres til en masseinnsats-operasjon. Det tar 4 sekunder å utføre.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
masseinnsatsen kan da optimaliseres ytterligere for å få ytterligere ytelsesforbedring.
Håper dette hjelper!!!
Lik oss På FaceBook / Følg Oss På Twitter / Bli med I DEN raskest voksende SQL Server-gruppen på FaceBook