UPDATE z SELECT i JOIN w SQL Server

UPDATE tabeli wirtualnej?

Operacja UPDATE jest jedną z podstawowych operacji i jest dość dobrze znana. Problem pojawia się przy aktualizacjach, w których udział bierze więcej tabel. Trzeba sobie od razu powiedzieć - nie da się w jednej instrukcji modyfikować jednocześnie dwóch tabel. Takie operacje należy wykonać w transakcji jako dwie oddzielne instrukcje. Da się natomiast modyfikować tabele wirtualne (podzapytania), w których wszystkie kolumny wyjściowe należą do jednej fizycznej tabeli. Jedną z opcji takiej okrężnej modyfikacji jest utworzenie widoku, który może mieć operacje JOIN. Kluczem do sukcesu, jak wspomniałem, jest umieszczenie w sekcji SELECT kolumn tylko z jednej tabeli. Operacja, skądinąd skuteczna, wymaga jednak utworzenia dodatkowego widoku, który nie zawsze jest potrzebny. Tworzenie widoku tylko pod modyfikacje nie jest chyba dobrym rozwiązaniem. Na szczęście jest kilka innych metod. Warto poznać wszystkie, by rozszerzyć repertuar możliwych zagrań w przyszłości.

Przykładowe tabele

Pokazane techniki można zastosować do większej ilości tabel, ale takie konstrukcje utrudniłyby zrozumienie samej koncepcji. Wszystkie pokazane techniki będą korzystały z dwóch tabel, które definiują hipotetyczną strukturę organizacyjną w pewnej firmie. Przyjrzyjmy się tym tabelom, ich zawartości i łączącej je relacji:

Dane osobowe i stanowiska są zupełnie przypadkowe.

Nasze zadanie polega na zmianie pensji wszystkich programistów. Na czym polega problem? Na tym, że pensje są w jednaj tabeli (Pracownicy), a nazwy stanowisk w drugiej (Stanowiska). Jest niby klucz, wiemy, że programista ma identyfikator 1, ale nie zawsze jest tak różowo. Załóżmy, że znamy tylko nazwę stanowiska. Jest to o tyle istotne, że pozwoli pokazać tytułową technikę UPDATE z SELECT i JOIN. Popatrzmy jeszcze na dane, które znajdją się w tabelach (identyfikatory zostały pominięte):

Zwykły, choć niestandardowy JOIN

Pokazana poniżej składnia nie jest rozwiązaniem standardu SQL. Jest to konstrukcja typowa dla w SQL Server. Co by na jej temat nie mówić, jest dość prosta i czytelna. Przyjrzyjmy się przykładowej instrukcji, która podniesie pensję wszystkim programistom o 200:

Zmodyfikowane zostaną 3 rekordy, bo tylu mamy programistów w bazie. Wykonanie pokazanej wcześniej instrukcji SELECT da nam teraz następujący rezultat:

Rozwiązanie skuteczne, ale przejdźmy dalej.

UPDATE przez Common Table Expression

W SQL Server 2005 pojawiło się coś takiego jak CTE (Common Table Expressions). To pewnego rodzaju definicje wirtualnych tabel, taki jakby dynamiczny widok, który, przy spełnieniu pewnych reguł, może być modyfikowany. Głównym wymogiem jest modyfikowanie tylko jednaj fizycznej tabeli (to samo co dla widoków zwykłych). Oczywiście reguł, które musi spełniać takie CTE, jest więcej, ale są one dość oczywiste. Nie można stosować agregacji (jak SQL Server miałby rozdzielić wartość na kolumny wchodzące w skład agregacji?), nie można stosować kolumn wyliczanych (np. Kolumna+100, SQL Server nie potrafi przekształcać wyrażeń). Większość operacji UPDATE jest jednak na tyle prosta, że wymienione ograniczenia nie są przeszkodą. Przyjrzyjmy się przykładowemu rozwiązaniu CTE:

Metoda jest wyjątkowo czytelna - wybieramy rekordy, które nas insteresują, a następnie przeprowadzamy prostą aktualizację na tej naszej wirtualnej tabeli. Co więcej, łatwo można sprawdzić, które rekordy zostaną zmodyfikowane wykonując tylko wewnętrzny SELECT! Przed masową operacją aktualizacji lepiej się dwa razy zastanowić, bo można sobie, często nieświadomie, nadpisać inne dane. Taki dodatkowy SELECT pozwala zweryfikować nasze zamiary. Szkody wyrządzone przez nieprawidłowy UPDATE mogą być bardzo kosztowne, a konieczność przywracania danych z kopii zapasowej czasochłonna (nie mówiąc o ewentualnych przestojach). Taka ostrożność wskazana jest nie tylko przy aktualizacji - dotyczny w zasadzie każdej operacji, która coś zmienia.

CTE pojawiło się w SQL Server 2005. W SQL Server 2008 jest coś lepszego:

UPDATE z JOIN poprzez MERGE

W SQL Server 2008 pojawiła się, według mnie, jedna z najbardziej niedocenianych instrukcji - MERGE. Służy ona, przynajmniej w teorii, do łączenia dwóch źródeł danych. Można ją jednak stosować także do wykonywania innych, nie mniej istotnych i często pojawiających się operacji. Jedną z nich jest transakcyjny UPSERT, czyli wstaw, jeżeli nie ma, zmodyfikuj, jeśli jest. Zostało to opisane we wpisie UPSERT, czyli UPDATE+INSERT w SQL Server. Tym razem MERGE zostanie użyte do operacji UPDATE z SELECT i JOIN. Przyjrzyjmy się przykładowemu rozwiązaniu tego problemu:

Dla tych, którym instrukcja MERGE jest obca, zapis może się wydać dość dziwny. Instrukcja MERGE wykonuje niejawne złączenie tabeli Pracownicy, z dowolnym źródłem (tabelą wirtualną) wkazaną w sekcji USING. Jeżeli złączenie się udało (w tabeli Pracownicy jest identyfikator stanowiska programisty), wykonywana jest sekcja WHEN MATCHED. Tam dane są aktualizowane. Zapis jest bardziej skomplikowany. Dlaczego więc mielibyśmy go stosować? Zaraz się wszystko wyjaśni.

Analiza planów wykonania i wydajności

Wszystkie pokazane metody są dobre, bo otrzymujemy właściwy rezultat - programiści zarabiają więcej. Warto jednak wiedzieć, co się dzieje pod spodem. A co się dzieje? Pierwsze dwie operacje mają identyczny plan wykonania. Oznacza to, że są równoważne. Plan z instrukcją MERGE różni się tym, że posiada blok MERGE zamiast bloku UPDATE. O ile sam plan wykonania pokazuje, że czasowo operacje są takie same, o tyle rzeczywiste testy nie potwierdzają tego. Okazuje się, że instrukcja MERGE jest nieco wolniejsza. Niewiele, bo około 10%, ale zawsze to 10%. Dlaczego? To już są szczegóły implementacyjne, do których dostęp jest mocno ograniczony, ale wyjaśnienie wydaje się proste. Instrukcja UPDATE jest łatwiejsza, bo służy tylko do modyfikacji. Instrukcja MERGE to taki mariaż wszystkich instrukcji (jest tam i UPDATE, i DELETE, i INSERT). Nie od dziś wiadomo, że wąska specjalizacja pozwala wykonywać powierzone zadania sprawniej i szybciej. SQL Server nie jest tu wyjątkiem. Dlaczego zatem pokazałem operację MERGE? Przede wszystkim dlatego, że jest opisana w standardzie SQL. Pozostałe metody, choć krótsze i jak się okazuje szybsze, są rozwiązaniami typowymi dla SQL Server. Instrukcja MERGE, przynajmniej w teorii, powinna być wspierana przez wszystkie bazy danych, które ten standard obsługują. Oznacza to, że instrukcję MERGE można bez problemu przenieść na Oracle, bez wykonywania jakiejkolwiek modyfikacji! Zwykłe kopiuj-wklej. Jeżeli jest taka możliwość to nie warto zbytnio przywiązywać się do jednej bazy. Nigdy nie wiadomo, czy jakiś klient, być może wielki fan Oracle, nie zechce naszej aplikacji. O tyle mniej będzie przepisywania.

Wnioski

Wykonywanie operacji UPDATE z JOIN nie jest szczególnie trudne. Tym razem nie ma jednego rozwiązania, które byłoby ze wszech miar najlepsze. Są rozwiązania szybkie i krótkie, ale niestandardowe, jest też rozwiązanie standardowe, ale dłuższe, w sensie treści i w sensie czasu wykonania. To od nas (znowu?) zależy metoda, której użyjemy.

Z logicznego punktu widzenia metody są równoważne. Jeżeli wykonaliśmy każdą z metod raz, to pensja każdego programisty podniosła się trzy razy. Trzy razy o taką samą wartość, w ciągu zaledwie kilkunastu minut, czego sobie i innym życzę.

Kategoria:Common Table ExpressionSQL ServerMerge