optimering af Opdateringsforespørgsler til store datamængder
opdatering af meget store tabeller kan være en tidskrævende opgave, og nogle gange kan det tage timer at afslutte. Ud over dette kan det også forårsage blokeringsproblemer.
her er nogle tips til at optimere opdateringerne på store datamængder.
- fjernelse af indeks på kolonnen, der skal opdateres.
- udførelse af opdateringen i mindre batches.
- deaktivering af Sletteudløsere.
- udskiftning af Opdateringserklæring med en Bulk-Insert-operation.
når det er sagt, lad os anvende ovenstående punkter for at optimere en opdateringsforespørgsel.
koden nedenfor opretter en dummy-tabel med 200.000 rækker og krævede 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)
overvej følgende opdateringsforespørgsel, som skal optimeres. Det er en meget ligetil forespørgsel at opdatere en enkelt kolonne.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
forespørgslen tager 2:19 minutter at udføre.
lad os se på udførelsesplanen for forespørgslen vist nedenfor. Ud over den grupperede indeksopdatering opdateres indekset iks_col1 også. Indeksopdatering og sortering tager sammen 64% af eksekveringsomkostningerne.
1. Fjernelse indeks på kolonnen, der skal opdateres
den samme forespørgsel tager 14-18 sekunder, når der ikke er noget indeks på col1. En opdateringsforespørgsel kører således hurtigere, hvis kolonnen, der skal opdateres, ikke er en indeksnøglekolonne. Indekset kan altid oprettes, når opdateringen er afsluttet.
2. Udførelse af opdateringen i mindre batches
forespørgslen kan optimeres yderligere ved at udføre den i mindre batches. Dette er generelt hurtigere. Koden nedenfor opdaterer posterne i batches af 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
ovenstående forespørgsel tager 6-8 sekunder at udføre. Når du opdaterer i batches, selvom opdateringen mislykkes, eller den skal stoppes, rulles kun rækker fra den aktuelle batch tilbage.
3. Deaktivering af Sletningsudløsere
udløsere med markører kan ekstremt bremse udførelsen af en sletningsforespørgsel. Deaktivering efter sletningsudløsere øger forespørgselsydelsen betydeligt.
4. Udskiftning af Update statement med en Bulk-Insert operation
en update statement er en fuldt logget handling, og det vil derfor helt sikkert tage lang tid, hvis millioner af rækker skal opdateres.Den hurtigste måde at fremskynde opdateringsforespørgslen på er at erstatte den med en bulk-insert-operation. Det er en minimalt logget operation i enkel og Bulk-logget opsving model. Dette kan gøres nemt ved at gøre en bulk-indsats i en ny tabel og derefter omdøbe tabellen til oprindelige. De krævede indekser og begrænsning kan oprettes på en ny tabel efter behov.
koden nedenfor viser, hvordan opdateringen kan konverteres til en bulk-insert-operation. Det tager 4 sekunder at udføre.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
bulkindsatsen kan derefter optimeres yderligere for at få yderligere ydelsesforøgelse.
håber dette hjælper!!!
Like us på FaceBook | Følg os på kvidre | Deltag i den hurtigst voksende SERVERGRUPPE på FaceBook