Snabbare UPDATE med CASE funktionen
Förord
En av nycklarna till att höja SQL Serverns prestanda är genom att hålla transaktionerna så korta som möjligt. I den här artikeln ska vi titta på några tricks om hur du med hjälp av CASE satsen kan utföra flera uppdateringar mot en tabell, i en enda operation. Genom att göra det så kan du korta ner transaktionerna samt höja prestandan.
Snabbare UPDATEs med CASE satsen
av Neil Boyle
Flera UPDATEs mot en singel kolumn
Följande exempel använder PUBS databas för att justera försäljningspriset på böcker olika mycket, beroende på de givna kriterierna. I exemplet nedan tänker jag ge 25 % rabatt på företagsböcker från vilket utgivare som helst, samtidigt som jag ger 10 % rabatt på böcker från en specifik utgivare. För att lyckas med det kan du känna dig frestad att slå ihop två UPDATEs till en transaktion så här:
BEGIN TRAN
UPDATE titles SET ...
UPDATE titles SET ...
COMMIT tran
Det negativa med den tekniken är att SQL-satsen kommer gå igenom tabellen två gånger, en för varje UPDATE. Om vi istället skriver om koden till den nedan, så går den bara igenom tabellen en gång. När man använder stora tabeller så kan man på så sätt spara på disk I/O, speciellt om satsen behöver utföra en tabellsökning.
UPDATE titles
SET price =
CASE
WHEN type = "business"
THEN price * 0.75
WHEN pub_id = "0736"
THEN price * 0.9
END
WHERE pub_id = "0736" OR type = "business"
Notera att en ”rakt nedåtgående” prioritering är inkluderad i CASE satsen. För företagsböcker från utgivare 0736 så kommer företagsrabatten på 25 % att dras av, eftersom det är det första villkoret i listan som uppfylls. Vi kommer däremot inte att dra av de ytterligare 10 % för boken, trots att det uppfyller det andra kravet i WHERE satsen. Därför att CASE satsen undersöker bara kriterierna tills den hittar den första posten som passar in (i det här fallet står ”business” först, vilket innebär att det är företagsrabatten som dras av i första hand).
Uppdatera flera kolumner
Vi kan med hjälp av CASE satsen uppdatera flera kolumner i en tabell samtidigt, och till och med ange olika kriterier för varje kolumn. Följande exempel uppdaterar utgivarens tabell med att sätta staten till ”--” för icke-USA företag, samtidigt som den i en och samma tabell läsning ändrar staden för en specifik utgivare.
UPDATE publishers
SET state =
CASE
WHEN country <> "USA"
THEN "--"
ELSE state
END,
city =
CASE
WHEN pub_id = "9999"
THEN "LYON"
ELSE city
END
WHERE country <> "USA" OR
pub_id = "9999"
Det går även att på samma sätt som ovan utföra uppdateringar i tre eller fler poster samtidigt, även då med olika uppdateringskriterier.
Det kan hända att du sällan kommer att kunna använda exemplet på ett effektivt sätt. SQL-satsen kommer nästan alltid att resultera i en tabellsökning, eftersom den mängden kolumner vi söker efter sällan finns med i samma täckande Index. Om varje kolumn bara uppdateras och Indexeras ett fåtal gånger, så kan det ibland nästan vara bättre att genomföra separata uppdateringar.
Ett bra sätt att börja använda den här tekniken på kan dock vara när du vill rensa upp ett flertal kolumner från en Interface fil i ett annat system.
Eftersom vi använder oss av två separata CASE satser, en för varje testkriteria/uppdatering, så kommer CASE satsen att undersöka varje post, och uppdatera där den ska. Det innebär att om det är flera kolumner i en post som kräver uppdatering, så kommer de alla att bli uppdaterade.
Det är två särskilt viktiga saker du bör komma ihåg i det här exemplet:
Varje CASE sats som används kräver en ELSE [kolumn] klausul, annars kommer du att ”null”a kolumner som inte alls ska ”null”as.
Längst ned i SQL-satsen måste det finnas en WHERE klausul som begränsar uppdateringar till de poster som kräver uppdatering i åtminstone en kolumn. Om det inte finns det så kommer alla kolumner att uppdateras, vilket förlänger exekveringstiden och tynger ned transaktionsloggen.
0 Kommentarer