Pilnuj typów danych

Dzisiaj chciałbym zwrócić Waszą uwagę na pilnowanie typów danych w MS SQLu (i w innych silnikach bazodanowych pewnie też). Czasami przez nieuwagę możemy nieźle namieszać na bazie i nawet początkowo o tym nie wiedzieć.

Załóżmy, że w bazie mamy takie 3 tabele:

Products (z indeksem klastrowym na ID):

Stores (z indeksem klastrowym na ID):

StoresProducts (z indeksem klastrowym na ProductID i StoreID):

Struktura bardzo prosta. Kolekcja produktów, sklepów i tabelka łącząca sklepy z produktami.

Niektórzy z Was pewnie już zwrócili uwagę na kolumnę ID w tabeli Products oraz ProductID w StoresProducts. Mamy tutaj inne typy danych, choć jak można by się domyśleć, wartości te są ze sobą powiązane. Skąd pomysł na varchar(7) dla id produktu to trochę inny temat (np. autor założył, że id będzie posiadało zera wiodące), ale taki twór może się zdarzyć, więc warto się tym zainteresować.

Skoro mamy już bazę, to pora na zapytanie :).

Powiedzmy, że potrzebujemy pobrać listę wszystkich sklepów, w których sprzedawany jest dany produkt. Jeżeli dany produkt nie jest sprzedawany w jakimś sklepie, to i tak musimy go mieć na liście, ale z NULLami.

Zapytanie mogłoby wyglądać tak (* w zapytaniu umieszczam celowo, aby nie komplikować zapisu na blogu):

Zapytanie wygląda dobrze i nawet działa:
sql_typy_00

Ale jak zerkniemy na plan wykonania:
sql_typy_01

to zobaczymy, że mamy konwersję typu kolumny ProductID.

Co można z tym zrobić? Np. rzecz, która z początku wydaje się bezsensowna – stworzyć tabelę mapującą id typu varchar(7) na int:

Z taką tabelą zapytanie wyglądać będzie tak:

O wiele bardziej skomplikowane, ale zobaczmy statystyki:

sql_typy_02

Jak widać jest szybciej i mamy mniej odczytów.

Dla porównania jeszcze taka wersja:

sql_typy_03

Opisany przypadek może wydawać się hardcore’owy, ale taki jest łatwiej zapamiętać :).

Trzeba pamiętać, że taki problem możemy mieć również przy prostszych zapytaniach:

Zapytanie bardzo proste – pobieramy produkt na podstawie podanego barcodu, ale z jakiegoś powodu w jednym przypadku zmienna przechowująca barcode posiada typ INT.

Jak wyglądać będzie wykonanie takich zapytań?

sql_typy_04

Różnicy nie widać, ale jak założymy na kolumnie Barcode indeks nieklastrowy, to otrzymamy takie wyniki:

sql_typy_05

Jak widać, jest o co walczyć.

A dlaczego tak się dzieje, widać poniżej:

sql_typy_06

Jak widać, w sytuacji złego typu, optymalizator wykonał pełny skan indeksu. I na tym straciliśmy.

Dlatego pamiętajcie, żeby sprawdzać plany wykonania nawet najprostszych zapytań i walczyć nawet o milisekundy.

17 Responses

  1. Pingback: dotnetomaniak.pl
  2. Paweł 26 lutego 2014 / 11:49

    Te statystyki czasu to z jakiego toola?

    Paweł

  3. Sławek 26 lutego 2014 / 14:34

    “Dlatego pamiętajcie, żeby sprawdzać plany wykonania nawet najprostszych zapytań i walczyć nawet o milisekundy.”

    To mi się podoba :)

    Tak, również polecam tego toola.

  4. Bartosz Adamczewski 26 lutego 2014 / 14:37

    Generalnie klucze klastrowane jako varchar to masakra, ponieważ bardzo ciężko będzie nam zachować rosnącą monotoniczność klucza, właściwie należy tego pilnować albo zaimplementować własny mechanizm. Są przypadki kiedy klucz monotoniczny nie jest tym czego chcemy (dużo stron aby zmniejszyć page lock contention) ale w 99.99% przypadków chcemy mieć monotoniczne rosnący klucz.

    • Łukasz Gąsior 27 lutego 2014 / 10:38

      zgadzam się – tutaj chciałem bardziej opisać problem różnych typów danych. a że ludzka fantazja nie zna granic to varchar w indeksie się zdarzyć może :)

  5. marek 27 lutego 2014 / 08:48

    “Dlatego pamiętajcie, żeby sprawdzać plany wykonania nawet najprostszych zapytań i walczyć nawet o milisekundy.” – kompletnie sie z tym nie zgadzam – “bawiac” sie w ten sposob czas produkcji aplikacji wydluzylibysmy tak bardzo, iz bardziej by sie oplacalo nawet dla klienta zewnetrznego dorzucic extra drugi serwer/ram/procesor czy co tam moglo by mu pomoc – wyjdzie to taniej :)

    • Łukasz Gąsior 27 lutego 2014 / 10:42

      każdy orze tak jak może :) ja uważam, że warto się zastanowić czy to co zrobiliśmy jest dobre. tak samo uważam, że warto pisać testy. oczywiście wszystko z umiarem – nie chodzi o to żeby nad każdym selectem siedzieć 2 dni. drugi serwer/ram/procesor nie zawsze jest możliwy a i nie zawsze pomoże.

      wiem, że można coś dostarczyć albo szybko albo dobrze – ja wybieram coś pośrodku

    • Paweł 27 lutego 2014 / 11:16

      Ja też się kompletnie nie zgadzam z Twoją opinią. Zapominając o indeksach czy nie używając istniejących ( na przykład poprzez konwersję typów, stosowanie funkcji w where) można bardo łatwo zarżnąć każdą bazę danych.

  6. Paweł Kmieć 27 lutego 2014 / 12:36

    No cóż. Najprostszy błąd projektowy jaki można popełnić i bardzo bolesny w skutkach. Ale takie kwiatki łatwo wyłapać, nie da się przecież nawet FK założyć w przypadku niespójnych typów danych.

    Zamiast budować protezy typu ProductIdTranslations ja bym refaktoryzował bazę, co od jakiegoś czasu staje się wykonalne nawet w większych projektach z narzędziami typu SSDT..

    • Łukasz Gąsior 27 lutego 2014 / 16:19

      jak masz 20-letni system to ciężko jest przeprowadzić dużą refaktoryzację. nie przez brak narzędzi a ryzyko wprowadzenia zbyt wielu zmian.

      zmiany można wprowadzać małymi krokami i wtedy taka tabelka pomaga.

  7. marek 27 lutego 2014 / 12:58

    Fajny artykul no i ciekawy Tool :)

    Jakim Frameworkiem poslugujecie sie, zeby mapowac te dane w kodzie?
    Jak ten programista “sprytnie” ominal problem roznych typow string/int w kodzie w mapowaniu? :)

    Optymalizacja ogolnie w porzadku, ale w kodzie zrodlowym bedzie troche dodatkowego kodzenia…
    Wydaje mi sie, ze latwiej rozszerzyc tabele Products o pole ID typu int,
    niz tworzyc nowa tabele “translations”.
    Zapytanie bedzie latwiejsze i struktura prostrza, no i prosciej bedzie rozszerzyc mapowanie w kodzie.
    – zmienic nazwe pola Product ID na pole Product IDChar i ustawic jako Unique .
    – dodac nowe pole Product ID int.
    – klucze obce aktualizowac na nowe pole Product ID int.

    Ogolnie ile tam masz danych w tych tableach?
    Bo czas(Duration) bardzo maly masz. Widac nie duzo jest tego.

    Baza danych MSSQL na licencji Enterprise dla duzej ilosci danych, ladnie potrafi optymalizowac zapytania z uzyciem rownoleglego przeszukiwania indeksow dla danego zapytania, jesli zrobic Partitioning dla indexu, np. po dacie, lub GUID itd..
    Ale dla sklepu to licencja Express juz wystarczajaca pewnie :) Bo po co obciazac klienta dodatkowymi kosztami, wiec rozwiazanie jest OK!

    • Łukasz Gąsior 27 lutego 2014 / 16:24

      to co widać na screenach przygotowałem na potrzeby tego posta

      baza w systemie nad którym teraz pracuję ma ok 1TB, największa tabela ma 1 500 000 000 (tak, półtora miliarda) wierszy a większość ważnych tabel ma po 200-300 milionów.

      dodatkowa kolumna pomogłaby jakby była tylko jedna tabela z innym typem :)

    • marek 27 lutego 2014 / 19:50

      - piszesz: “ale taki twór może się zdarzyć, więc warto się tym zainteresować”
      Chodzilo tobie o zaprezentowanie Toola, czy wymyslonego problemu “a gdyby”?
      To w takim razie po co analizowales ten przyklad, skoro u ciebie nie wystepuje i pewnie u nikogo, kto uzywa normalnych ORM-ow tez nie wystapi?

      – Wiec rozumiem, ze ta duza baza to nie sklepy i produkty?

    • Łukasz Gąsior 27 lutego 2014 / 19:59

      - nie chodziło o zaprezentowanie toola (wspomniałem o nim dopiero w komentarzu) a realnego problemu, który w bazie może wystąpić i z którym się spotkałem

      – nigdzie nie wspominam o ORMie

      – nie wiem jaki związek mają sklepy i produkty z wielkością bazy ale pokazany tutaj przykład stworzyłem na potrzeby posta.

  8. marek 27 lutego 2014 / 21:13

    OK, w takim razie nie rozumiem tego posta.
    90% czasu zajelo mi zrozumienie, jak ktos potrafil tak namieszac w bazie.
    No ale skoro robi sie cos niezgodnie z zasadami, no to niestety poprawianie tego zajmuje duzo oczasu i trzeba oblozyc sie roznymi pomocnymi Toolami.

    Co do wielkosci baz, to juz nie musiales pisac, ze baza w systemie nad którym pracujesz ma tyle dancyh, bo ze pracujesz nie znaczy ze ja tworzysz i optymalizujesz i zarzadzasz. Wiec troche tutaj probujesz sie przechwalac.
    Pewnie kilka osob czytajacych tego posta, pracuje nad podobnie duzymi lub wiekszymi systemami.
    Ogolnie tez pracuje, tworze i zarzadzam systemem i jego baza, ktora ma 1mld danch per jedne maly kraj, a Panstw jest obecnie 20.
    I nikt by sobie nie pozwolil na taki blad. Uwazam, ze przyklad jest nieudany, bo zamiast pomagac, robi zamet.

    piszesz:
    “Dzisiaj chciałbym zwrócić Waszą uwagę na pilnowanie typów danych w MS SQLu (i w innych silnikach bazodanowych pewnie też).
    Czasami przez nieuwagę możemy nieźle namieszać na bazie i nawet początkowo o tym nie wiedzieć.”
    No niewiedziec to moga o tym studenci na przyklad…ale od tego jest w pracy zawsze osoba, ktora ja pilnuje i do takich bledow nie dopuszcza.
    Co do “i w innych silnikach bazodanowych pewnie też” – jak nie jestes pewny czy tez tak jest, to nie pisz, ze pewnie tez… W relacyjnych bazach danych, to pewnie tez..w NoSQL pewnie nie, itd.. mozna wymieniac.
    Ogolnie to fajnie, gdybys dopisal jaka to baza, ktora testowales.
    To na tyle.

    Ale znalazles fajny sposob, zeby zachecic do czytania twojego bloga.

    • Łukasz Gąsior 27 lutego 2014 / 21:33

      dzięki, za komentarze – w końcu jakieś ożywienie na blogu :)

      odnośnie tego co napisałeś:

      niczym się nie przechwalam. jakbym tą bazę projektował to bym napisał, że ją projektowałem. nie wiem jak inaczej napisać, że w projekcie jest taka baza. zapytałeś ile jest danych w bazie to odpowiedziałem.

      “90% czasu zajelo mi zrozumienie, jak ktos potrafil tak namieszac w bazie.”
      my (jako zespół, który w projekcie pracuje – żeby nie było, że znowu się przechwalam ;) ) do dzisiaj tego nie rozumiemy

      “No niewiedziec to moga o tym studenci na przyklad…”
      też tak kiedyś myślałem ale życie inaczej to weryfikuje. “kwiatki w kodzie” sadzą nie tylko studenci.

      “Ale znalazles fajny sposob, zeby zachecic do czytania twojego bloga.”
      bardzo mnie to cieszy :)

Comments are closed.