Jak znaleźć brakujące indeksy w bazie danych?

Home

Optymalizacja bazy danych i zapytań to temat rozległy i szeroki jak morze i nie jedną książka napisano na ten temat. Ja dzisiaj napiszę o dosyć prostej technice pozwalającej znaleźć brakujące indeksy w bazie danych MSSQL. Zapewne każdy korzystający z MSSQL Management Studio wie, że można poprosić o wyświetlenie planu wykonania zapytania (opcje Dispaly Actual Execution Plan oraz Include Actual Execution Plan). Dodatkowo po wykonaniu zapytania MSSQL zasugeruje nam jakich indeksów brakuje.

Fajnie, ale co w sytuacji kiedy widzimy, że nasza aplikacja działa wolno. Mamy podejrzenie, że problem dotyczy bazy danych, ale przecież nie będziemy uruchamiali każdego możliwego zapytania w SSMS. W takiej sytuacji możemy de facto użyć tej samej funkcjonalności co w przypadku uruchamiania zapytania z SSMS. Mam tutaj na myśli Missing Indexes Feature, która jest cechą MSSQL, a nie środowiska SSMS. Informacje o brakujących indeksach silnik bazy danych odkłada mianowicie w kilku widokach systemowych z rodziny sys.dm_db_missing_index_*. Wystarczy więc uruchomić aplikację i zobaczyć jakie indeksy sugeruje nam MSSQL. Ja w tym celu używam zapytania, które znalazłem na blogu SQL Authority.

Przykład z życia. Ostatnio musiałem zoptymalizować pewne obliczenia i postąpiłem dokładnie jak napisałem wyżej. Uruchomiłem w aplikację, zmierzyłem czas obliczeń, zapisałem czas ich uruchomienia i zakończenia, a następnie wyświetliłem listę sugerowanych indeksów do utworzenia. Było ich 6. Na początek odrzuciłem te o niskiej wartości w kolumnie Avg_Esitmated_Impact. Z pozostałych indeksów 2 różniły się tym, że jeden miał klauzulę INCLUDE, a drugi nie. Stwierdziłem, że w pierwszym podejściu skupię się na jednym.

W dalszej kolejności wykonałem testy aby zobaczyć jaki uzysk daje założenie każdego z tych 3 indeksów, a także 2 z nich czy wszystkich 3. Okazało się, że zastosowanie jednego z nich skrócił czas obliczeń o ponad 30%, a pozostałe dwa o małe kilka. Dla rzetelności testy powtórzyłem, a wyniki uśredniłem. Na koniec dokładnie przeanalizowałem proponowany indeks i porównałem go do indeksów już utworzonych na tabeli. Okazało się, że istniał już bardzo podobny indeks. Konkretnie, MSSQL zaproponował coś takiego:

CREATE INDEX IX_Test ON dbo.Table(Col_1, Col_2) INCLUDE (Col_4);

A istniejący indeks wyglądał tak:

CREATE INDEX IX_Test ON dbo.Table(Col_1, Col_2, Col_3);

Wystarczyło, wieć go zmodyfikować w następujący sposób:

CREATE INDEX IX_Test ON dbo.Table(Col_1, Col_2, Col_3) INCLUDE (Col_4);

Na koniec sprawdziłem jak taka modyfikacja wpływa na operacje wstawiania/aktualizacji danych do/w docelowej tabeli. W tym celu napisałem zapytania wstawiające setki tysięcy rekordów do tej tabeli, a także takie, które modyfikuje kolumnę Col_4.. Wyniki pokazały niewielkie spadek wydajności. Był on znacznie mniejszy niż zysk przy odczycie danych, a po drugie wiedziałem, że w praktyce omawiana tabela jest częściej czytana niż modyfikowana.

Przy pracy z Missing Indexes Feature warto wiedzieć o kilku dodatkowych rzeczach. MSSQL może nam zasugerować wiele brakujących indeksów i nie koniecznie wszystkie muszą dotyczyć zapytać wykonanych przez nas. Aby wyeliminować ten problem sugeruję wykonywanie takich ćwiczeń na dedykowanej bazie danych. Przydatne będą też kolumny last_user_seek oraz last_user_scan z widoku sys.dm_db_missing_index_group_stats. Zawierają one informacje o tym kiedy dany brakujący indeks był potrzebny. Po pierwsze podany czas możemy porównać z czasem uruchomienia/zakończenia obliczeń i odrzucić te indeksy, które nie mieszczą się w tym zakresie. Po drugie te czasy mogą zgrubnie wskazać, w którym momencie działania aplikacji występuje problem. Napisałem, że przy wyborze indeksów do dalszej analizy bazowałem na kolumnie Avg_Esitmated_Impact. Trzeba na to jednak uważać. Ta wartość to tylko pewne przybliżenie i może nas wyprowadzić na manowce. Z 3 indeksów jakie wybrałem do dalszej analizy największy zysk miał ten o najmniej wartości w tej kolumnie.

Końcowa uwaga jest taka, że Missing Indexes Feature to pomocna rzecz, ale nie jest to magiczna formuła, która rozwiąże wszystkie problemy za nas. Ma też swoje ograniczenia, o których należy wiedzieć.

Podsumowując:

• MSSQL sugeruje brakujące indeksy.
• Brakujące indeksy można odczytać z bazy danych.
• Testy wydajności należy powtórzyć kilka razy.
• Testy wydajności dobrze wykonywać w dedykowanym do tych celu środowisku.
• Missing Indexes Feature to nie magiczna formuła i ma swoje ograniczenia.
• Proponowane brakujące indeksy należy zawsze poddać analizie i porównać do istniejących indeksów.
• Należy pamiętać, że indeksy spowalniają operacje aktualizacji i wstawiania danych.
• Wartość w kolumnie Avg_Esitmated_Impact należy traktować ostrożnie.

DateTime.Parse vs DateTime.ParseExact

Home

Ostatnio spotkałem się z taka sytuacją. Ktoś zdefiniował sobie dość specyficzny format daty na swoje potrzebny. Wszystko było ok kiedy był on używany do zamiany typu DateTime na ciąg znaków. Problemy zaczęły się przy próbie wykonania operacji odwrotnej. Okazało się, że rodzina metod DateTime.(Try)Parse sobie z tym nie radzi. Rozwiązaniem okazało się użycie metod z rodziny DateTime.(Try)ParseExact, która od tych wcześniejszych różni się tym, że jawnie wymaga podania formatu, który ma zostać użyty przy parsowaniu.

Postawione pytanie brzmiało czemu DateTime.(Try)Parse nie działa w tym przypadku, a w innych tak? Moja odpowiedź jest taka. Nie wiem czemu podjęto taką decyzję, ale DateTime.(Try)Parse nie obsługuje wszystkich możliwych formatów i to nawet jeśli kultura używana przez aplikację zawiera wszystkie potrzebne informacje. Oto fragment z dokumetnacji:

If you parse a date and time string generated for a custom culture, use the ParseExact method instead of the Parse method to improve the probability that the parse operation will succeed. A custom culture date and time string can be complicated, and therefore difficult to parse. The Parse method attempts to parse a string with several implicit parse patterns, all of which might fail.

A to jeszcze jeden:

The Parse and TryParse methods do not fully iterate all strings in patterns when parsing the string representation of a date and time. If you require a date and time string to have particular formats in a parsing operation, you should pass the array of valid formats to the DateTime.ParseExact...

W skrócie DateTime.(Try)Parse jest z założenia "upośledzone" i nie umie wszystkiego. Dlaczego? Może dlatego, że obsłużenie wszystkich możliwych przypadków jest bardzo trudne? A może dlatego, że gdyby napisano kombajn obsługujący wszystko to działałby wolno? To tylko zgadywanie, ale trzeba pamiętać, że:

Jeśli używamy własnych formatów daty i czasu to zaleca się użycie DateTime.(Try)ParseExact.

Kodować jak w NASA

Home

Kolega podesłał mi link do ciekawego artykułu na temat 10 zasad stosowanych w NASA, aby pisać naprawdę bezpieczny, czytelny i dobry kod. Zasady te w oryginale dotyczą języka C i zacząłem się zastanawiać czy da się je zastosować do .NET'a. Na zielono zaznaczyłem te zasady, które moim zdaniem da się użyć w .NET'cie, na pomarańczowo te dyskusyjne, a na czerwono takie, których się nie da.

Stosuj tylko bardzo proste konstrukcje sterujące przepływem sterowania w programie. Nie używaj goto i rekursji.

Mi ta zasada przypomina inną Keep it simple stupid, czyli nie starajmy się na siłę pokazać jakimi super programistami jesteśmy i piszmy możliwie prosto. To, że bez goto można się obejść to oczywiste. Bardzo dyskusyjny jest natomiast zakaz użycia rekursji. Autor zasady argumentuje to tym, że brak rekursji ułatwia pracę analizatorom kodu źródłowego, a także dowodzenie poprawności kodu. Ciężko mi z tym dyskutować, bo nie wiem jakiego analizatora używa NASA i nie spotkałem się też z dowodzeniem poprawności kodu w .NET. Osobiście uważam, że rekursja jest przydatna i w wielu przypadkach algorytmy zapisane rekurencyjnie są po prostu łatwiejszych do zrozumienia. Trzeba jednak uważać o czym już pisałem w tym albo w tym artykule.

Wszystkie pętle powinny mieć sztywno określoną górną granicę liczby iteracji.

Znowu dyskusyjna sprawa i znowu ma to służyć temu, aby można było udowodnić, że pętla się kiedyś zakończy. Dać się pewnie da tak pisać, ale wygodne to to pewnie nie jest. Ponieważ w .NET'ie nie piszemy oprogramowania dla statków kosmicznych tą zasadę bym pominął.

Nie stosuj dynamicznej alokacji pamięci (w szczególności garbage collector'a) już po zainicjowaniu aplikacji.

No cóż w .NET bez garbage collector'a się nie da. Można próbować minimalizować tworzenie nowych obiektów w czasie działania aplikacji, ale tak na co dzień to właściwie po co? To, co powinno się rozważyć, to załadowanie do pamięci danych niezmiennych (referencyjnych, słownikowych czy jak to zwał) i korzystanie z nich przez cały czas życia aplikacji.

Metody powinny być możliwie krótkie tj. nie więcej niż 60 linii kodu.

Czy 60 to dobre ograniczenie? Można dyskutować, ale z pewnością metody powinny być możliwie krótkie bo to podnosi ich czytelność. Po drugie jeśli metoda jest krótka to znaczy, że robi jedną konkretną rzecz, a nie kilka lub kilkanaście.

Średnia liczba asercji na metodę powinna wynosić minimum 2. Asercje powinny zabezpieczać przez sytuacjami, które w ogóle nie powinny wystąpić i nie mieć efektów ubocznych.

Co do tego jak pisać asercje to się zgadzam, ciężko mi natomiast powiedzieć, czy 2 to dobra średnia asercji na metodę. W komentarzu do tej zasady autor pisze, że przeciętnie testy jednostkowe wykrywają 1 błąd na 10-100 linii kodu, a asercje jeszcze zwiększają szansę na wykrycie błędów. Ja to rozumiem tak, że sugeruje się używanie tego i tego. Ok, ale ja bym jednak explicite wspomniał potrzebę testów jednostkowych czy ogólnie testów automatycznych w tej zasadzie.

Zmienne powinny mieć możliwe mały zasięg.

Czyli nie stosujemy globalnego stanu, nie re-używamy tej samej zmiennej do różnych celów itd.

Wyniki zwracane przez metody powinny być zawsze sprawdzane przez metodę wołającą. Każda wołana metoda powinna zawsze sprawdzać parametry wejściowe.

Innymi słowy nie ufamy nikomu, nie stosujemy konwencji (na przykład takiej, że jeśli metoda zwraca kolekcję to nigdy nie zwróci null'a, ale pustą kolekcję) itp. Ja jednak lubię konwencję, a parametry wejściowe staram się weryfikować głównie dla metod publicznych i chronionych. Wyniki zwracane przez metody weryfikuję natomiast przede wszystkim przy użyciu zewnętrznych bibliotek.

Należy ograniczyć użycie pre-procesora, makr i kompilacji warunkowej.

Preprocesora i makr nie mamy w .NET, ale możliwość kompilacji warunkowej już tak. Czemu ich nie używać? Ponieważ utrudniają przewidzenie wyniku kompilacji. Autor zasady podaje taki przykład. Załóżmy, że w programie mamy 10 dyrektyw kompilacji warunkowej. Załóżmy, że używają innych warunków. Daje 2^10 = 1024 różnych możliwych wyników kompilacji tego samego kodu, które mogą działać inaczej!

Należy ograniczyć użycie wskaźników. W szczególności stosujemy co najwyżej jeden poziom dereferencji. Wskaźniki na funkcje nie są dozwolone.

Autor ponownie argumentuje, że brak wskaźników ułatwia weryfikację kodu. Tą zasadę ciężko jednak przełożyć na .NET. Niby z typowych wskaźników też można korzystać, ale nie jest to często używane. Jeśli natomiast pod słowo ''wskaźnik'' w tej regule podstawimy ''referencja'', a pod ''wskaźnik na funkcję'' terminy ''delegat'' lub ''wyrażenia lambda'' to okaże się, że w .NET nie możemy zrobić właściwie nic. Podsumowując ta zasada nie ma zastosowania do .NET'a.

Kod musi się kompilować i to bez ostrzeżeń. Przynajmniej raz dziennie kod źródłowy musi przejść weryfikację przy pomocy narzędzi do statycznej analizy kodu z zerową liczbą ostrzeżeń.

To, że kod musi się kompilować to oczywiste. Jeśli chodzi o ostrzeżenia to moim zdaniem w przypadku projektów prowadzonych od zera liczba ostrzeżeń rzeczywiście powinna być równa 0. Jeśli uważamy, że jakieś ostrzeżenia są "głupie" to trzeba odpowiedzieć sobie na pytanie czy używamy dobrych narzędzi? W przypadku tzw. kodu zastanego sprawa jest trudniejsza, ale te 10 zasad służy m.in. właśnie temu, aby nie tworzyć takiego kodu.

Jak widać nie wszystkie z tych zasad da się zastosować w .NET czy przy tworzeniu aplikacji typowo biznesowych. Z drugiej strony są firmy, które w .NET piszą oprogramowanie medyczne. Niektóre z tych zasad wydają się bardzo drakońskie, ale jak już pisałem NASA w swoich projektach osiąga wynik 0 błędów na tysiąc linii produkcyjnego kodu.

Polecam też zapoznanie się z oryginalnym dokumentem NASA’s 10 rules for developing safety-critical code.

Czy sposób pisania pętli for ma wpływ na wydajność?

Home

Przy okazji codziennej prasówki natknąłem się na ten artykuł na temat wydajności pętli for w JavaScript'cie dla różnych przeglądarek. W skrócie chodzi o to czy powinniśmy pisać pętlą for tak:

for (int i = 0; i < array.Length; i++)
   ...

A może raczej tak, czyli zapamiętać długości tablicy w zmiennej pomocniczej i nie odczytywać jej przy każdym przebiegu pętli:

for (int i = 0, len = array.Length; i < len; i++)
   ...

Z ciekawości sprawdziłem czy taka mikro optymalizacja ma jakiekolwiek znaczenie w przypadku programowania na platformę .NET. Do zmierzenia czasu użyłem takiej metody pomocniczej:

public static void MeasureIt<T>(Action<T> action, T arg, int noOfIterations)
{
   var sw = new Stopwatch();
   sw.Start();

   for (var i = 0; i < noOfIterations; ++i)
      action(arg);

   sw.Stop();
   Console.WriteLine("Total time: " + sw.ElapsedMilliseconds);
}

Następnie wykonałem następujący test:

var noOfIterations = 100000;
var noOfElements = 10000;
var array = Enumerable.Repeat(1, noOfElements).ToArray();
//Przypadek 1
MeasureIt(arg =>
{
   var total = 0;
   for (var i = 0; i < arg.Length; i++)
      total += a[i];  
}, array, noOfIterations);
//Przypadek 2
MeasureIt(arg =>
{
   var total = 0;
   for (int i = 0, len = arg.Length; i < len; i++)
      total += a[i];
}, array, noOfIterations);

Dla rzetelności testy uruchomiłem wielokrotnie. Czas wykonywania obliczeń w obu przypadkach wynosił około 320ms z dokładnością do kilku milisekund. Czasami pierwsze podejście było szybsze, a czasami drugie. Z jednej strony czegoś takiego się spodziewałem. Z drugiej strony sądziłem, że jednak zaobserwuję pewien zysk w drugim przypadku. Dlaczego?

Otóż jeśli spojrzymy na kod pośredni wygenerowany przez kompilator to te dwie implementacje bynajmniej nie są identyczne. W pierwszym przypadku długość tablicy odczytywana jest wielokrotnie na koniec pętli, a w drugim tylko raz przed właściwą pętlą. Najwidoczniej jest to jednak tak szybka operacja, że się nie liczy (w IL do odczytania długości jednowymiarowej tablicy istnieje dedykowana instrukcja ldlen).

Dodatkowo przeprowadziłem podobny test dla użycia listy generycznej List<T>. Tym razem różnice były już zauważalne i wynosiły około 27% na rzecz zapamiętania liczby elementów listy w dodatkowej zmiennej. Średnio pierwsza wersja wykonywała się 957ms, a druga 752ms. Wynika to z tego, że aby odczytać liczbę elementów w liście należy odczytać właściwość Count czyli metodę get_Count. Na poziomie IL jest to robione przy pomocy instrukcji callvirt (w telegraficznym skrócie służącej do wołania metod na rzecz obiektów), a nie dedykowanej (i pewnie zoptymalizowanej) instrukcji ldlen jak ma to miejsce w przypadku tablic. Pomimo tych różnic uważam jednak, że w codziennej praktyce programistycznej nie należy się tym przejmować gdyż różnice w czasach obliczeń, w porównaniu do dużej liczby iteracji (100000), są zbyt małe.

Życzenia świąteczne

Home

Kolejne Święta Bożego Narodzenia już tuż tuż. W tym roku czekam na nie jeszcze bardziej niż zwykle, bo to pierwsze wydarzenie tego rodzaju dla naszej córeczki. Wszystkim czytającym mojego bloga i nie tylko życzę dużo radości w gronie bliskich Im osób, a w Nowym Roku ciekawych wyzwań zawodowych oraz zapału do nauki i doskonalenia się.

Serdecznie Pozdrawiam,
Michał Komorowski