SQL Server optimera Uppdateringsfrågor för stora datavolymer

SQL Server optimera Uppdateringsfrågor för stora datavolymer

Uppdatera mycket stora tabeller kan vara en tid att ta uppgift och ibland kan det ta timmar att avsluta. Utöver detta kan det också orsaka blockeringsproblem.

här är några tips för att SQL Server optimera uppdateringarna på stora datavolymer.

1. ta bort index på kolumnen som ska uppdateras.
2. utför uppdateringen i mindre satser.
3. inaktivera Delete triggers.
4. ersätter uppdateringsuttalande med en Bulkinsats.

med det sagt, låt oss tillämpa ovanstående punkter för att optimera en uppdateringsfråga.

koden nedan skapar en dummy-tabell med 200 000 rader och obligatoriska index.

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)

Tänk på följande uppdateringsfråga som ska optimeras. Det är en väldigt rak fråga att uppdatera en enda kolumn.

UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'

frågan tar 2:19 minuter att köra.

Låt oss titta på exekveringsplanen för frågan som visas nedan. Förutom den grupperade indexuppdateringen uppdateras även indexet ix_col1. Indexuppdateringen och Sorteringsoperationen tar tillsammans 64% av exekveringskostnaden.

1. Ta bort index på kolumnen som ska uppdateras

samma fråga tar 14-18 sekunder när det inte finns något index på col1. Således körs en uppdateringsfråga snabbare om kolumnen som ska uppdateras inte är en indextangentkolumn. Indexet kan alltid skapas när uppdateringen är klar.

2. Utför uppdateringen i mindre satser

frågan kan optimeras ytterligare genom att köra den i mindre satser. Detta är i allmänhet snabbare. Koden nedan uppdaterar posterna i partier 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

ovanstående fråga tar 6-8 sekunder att utföra. Vid uppdatering i satser, även om uppdateringen misslyckas eller den behöver stoppas, rullas bara rader från den aktuella batchen tillbaka.

3. Inaktivera Delete triggers

Triggers med markörer kan extremt bromsa prestandan hos en delete fråga. Inaktivera efter raderingsutlösare kommer att öka frågans prestanda avsevärt.

4. Ersätta Update statement med en Bulk-Insert operation

en update statement är en helt loggad operation och därmed kommer det säkert att ta avsevärd tid om miljontals rader ska uppdateras.Det snabbaste sättet att påskynda uppdateringsfrågan är att ersätta den med en bulkinsats. Det är en minimalt loggad operation i enkel och Bulkloggad återställningsmodell. Detta kan göras enkelt genom att göra en bulkinsats i en ny tabell och sedan byta namn på tabellen till original. De obligatoriska index och begränsningar kan skapas på en ny tabell som krävs.

koden nedan visar hur uppdateringen kan konverteras till en bulkinsats. Det tar 4 sekunder att utföra.

SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable

bulkinsatsen kan sedan optimeras ytterligare för att få ytterligare prestandaökning.

hoppas detta hjälper!!!

gilla oss på FaceBook / Följ oss på Twitter / gå med i den snabbast växande SQL Server-gruppen på FaceBook