Optymalizacja ORM w Django

Za każdym razem kiedy czytam lub uczestniczę w dyskusji dotyczącej ORM, spotykam dwa obozy, w których jedni go uwielbiają, a inni nienawidzą. Co jest takiego w ORM, że powoduje on aż tak skrajne emocje? Ostatnio usłyszałem dość śmiałą tezę, że przejście na czysty SQL pozwala przyśpieszyć projekt nawet 20-krotnie. Czy to jest prawda? Dziś spróbujemy wspólnie się do tego odnieść.

Osobiście rozumiem, że w niektórych przypadkach zastosowanie czystego SQL jest niezbędne. Doceniam też podejście oddzielenia warstwy persystencji od warstwy logiki biznesowej. Wiem, że daje to większą elastyczność i rzeczywiście w niektórych przypadkach potrafi optymalizować fragmentu kodu pod konkretne operacji, jak na przykład szybki zapis, lub szybki odczyt. Wymaga to większej wiedzy, bo poza znajomością programowania obiektowego trzeba również rozumieć jakie mechanizmy dzieją się pod spodem. Z drugiej strony taki ORM jest dostępny niemal w każdym języku obiektowym, który korzysta z bazy danych i w Django na przykład jest wbudowany i korzysta się z niego bardzo wygodnie.

Czym jest ten ORM

ORM (ang. Object-Relational Mapping) to mapowanie obiektowo-relacyjne, czyli nic innego jak tłumaczenie architektury systemu na warstwę bazy danych. Myślę, że do dalszych rozważań taka uproszczona definicja wystarczy. Korzystanie z ORM może mieć problemy wydajnościowe, zwłaszcza jeśli robi się to niepoprawnie. Najważniejsze jest jednak, że ORM nie robi nic innego jak tłumaczy zapytania wyższego poziomu na konkretne zapytania SQL

Model danych

Żeby w jakikolwiek sposób mówić o zapytaniach trzeba mieć stworzone modele. Skorzystam z dwóch prostych modeli, których używam w swoim nowym projekcie, czyli produkt i lista cen tego produktu.

class Product(models.Model):
    name = models.CharField(max_length=255)
    image = models.URLField(max_length=255, null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    slug = models.SlugField(max_length=255)
    ceneo_id = models.CharField(max_length=255)


class Price(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    value = models.DecimalField(max_digits=10, decimal_places=2)
    date = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ("date",)

Do testów dodałem nieco ponad milion produktów i w trakcie pisania postu pracuje skrypt, który dodaje kilkanaście milionów cen

Narzędzia do badania SQL

Zobaczmy jakie popularne narzędzia w Django pozwolą nam zweryfikować proces translacji na zapytania SQL.

Atrybut query w Queryset

queryset = Product.objects.all()
print(queryset.query)  # SELECT "products_product"."id", "products_product"."name", "products_product"."image", "products_product"."description", "products_product"."slug", "products_product"."ceneo_id" FROM "products_product"

Niestety to rozwiązanie przedstawia jedynie przetłumaczone zapytanie. W wielu przypadkach to wystarcza, jednak nam zależy nam zbadaniu również czasów odpowiedzi, dlatego poszukajmy dalej.

Connection

from django.db import connection

connection.queries # []

Product.objects.all()
connection.queries # [{'sql': 'SELECT "products_product"."id", "products_product"."name", "products_product"."image", "products_product"."description", "products_product"."slug", "products_product"."ceneo_id" FROM "products_product" LIMIT 21',
  'time': '1.285'}]

Już lepiej, bo poza zapytaniem mamy również  czas wykonania. Moim zdaniem całkiem przyzwoite rozwiązanie, ale sprawdźmy inne alternatywy.

Django-debug-toolbar

Django-debug-toolbar to naprawdę potężne narzędzie i osobiście uważam, że każdy powinien się z nim zapoznać. Mnie osobiście najbardziej podoba się lista wykorzystanych szablonów oraz zakładka z zapytaniami do bazy danych. W zakładce tej mamy informację o wszystkich wykonanych zapytaniach, czasach wykonania, a nawet duplikatach jeśli jakieś się pojawiły. Jeśli jeszcze nie znasz tego narzędzia, to gorąco zachęcam do zapoznania się z nim, bo możesz się w nich zakochać. Wszystko dostępne jest na rozwijanym pasku dynamicznie po załadowaniu strony. Największą wadą tego rozwiązania jest spory narzut czasowy. W związku ze sporą ilością zbieranych informacji czas ładowania strony może się znacznie wydłużyć, dlatego zachęcam do dostosowania go do własnych potrzeb. Nigdy nie powinniśmy też mieć włączonego tego narzędzia na produkcji, bo nie chcemy, żeby tak wrażliwe informacje wyszły w świat.

Shell_plus z django-extensions

python manage.py shell_plus --print-sql

Nie wszystkie zapytania wykonujemy bezpośrednio na stronie. Czasem zachodzi potrzeba napisania jakiegoś skryptu, albo optymalizacja konkretnych zapytań. Nie wyobrażam sobie wchodzić na stronę z każdą drobną zmianą. Na szczęście jest świetna biblioteka, która ułatwia taką pracę. Shell_plus z django-extensions pozwala na uruchomienie shell z parametrem --print-sql , który wyświetla jakie zapytania zostały wykonane, jednocześnie informując o czasach ich wykonania. Dziś wszystkie badania będę wykonywać właśnie na tym narzędziu.

In [2]: Product.objects.all()
Out[2]: SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 21

Execution time: 0.830947s [Database: default]

Najważniejsze uwagi

Lazy load

Najważniejszy do zapamiętania jest fakt, iż zapytania są lazyload, czyli wykonują się w momencie, kiedy rzeczywiście ich użyjemy, na przykład poprzez wypisanie. Zobaczmy to na przykładzie

In [2]: products = Product.objects.all()                                                                                                                                
In [3]: print(products)                                                                                                                                                 
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 21

Execution time: 0.104087s [Database: default]
<QuerySet [<Product: Product object (1)>, <Product: Product object (2)>, <Product: Product object (3)>, <Product: Product object (4)>, <Product: Product object (5)>, <Product: Product object (6)>, <Product: Product object (7)>, <Product: Product object (8)>, <Product: Product object (9)>, <Product: Product object (10)>, <Product: Product object (11)>, <Product: Product object (12)>, <Product: Product object (13)>, <Product: Product object (14)>, <Product: Product object (15)>, <Product: Product object (16)>, <Product: Product object (17)>, <Product: Product object (18)>, <Product: Product object (19)>, <Product: Product object (20)>, '...(remaining elements truncated)...']>

Widzimy,  że zapytanie nie wykonało się w momencie pobrania produktów a dopiero podczas ich wypisywania. To bardzo ważna kwestia, która rozszerzymy nieco później.

Cache zapytań

Kolejną niezwykle istotną kwestią jest cache przy zapytaniach. Jeśli wykonamy ponownie to samo zapytanie, to czas odpowiedzi będzie znacznie krótszy, bo zostanie ono zapisane w cache. Zobaczmy to na przykładzie

In [1]: Product.objects.all()
Out[1]: SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 21
Execution time: 3.234180s [Database: default]


In [2]: Product.objects.all()                                                                                                                                           
Out[2]: SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 21

Execution time: 0.129226s [Database: default]

Czytelność przede wszystkim

Przesadna komplikacja kodu nie jest dobrym pomysłem, co zresztą jest podkreślone w Zen Python. Staraj się przede wszystkim, żeby twój kod był czytelny, a dopiero później skup się na jego optymalizacji, co w zasadzie wiąże się z kolejnym punktem.

Przedwczesna optymalizacja

Ostatnio w głowie utkwił mi świetny cytat Donalda Knutha "premature optimization is the root of all evil" . Pamiętajmy, że nie wszystko należy dopieszczać w każdym stopniu. Jeśli tworzysz skrypt, który zostanie uruchomiony tylko raz, to nie musisz dbać o idealne pobieranie danych i prawdopodobnie nie stanie się nic złego jeśli zamiast 10 sekund potrwa 2 minuty. Podobnie, jeśli wiesz, że obiektów nie będzie więcej niż kilkaset, to nie musisz poświęcać energii na przedwczesną optymalizację, bo to zwyczajne marnowanie twoich zasobów.

Wykonanie zapytania

Wcześniej wspomniałem, że zapytania są lazy-load, czyli wykonują się dopiero w momencie kiedy rzeczywiście odniesiemy się do danych. Zobaczmy na kilku przykładach kiedy zapytania się wykonają, a kiedy nie

Zapytania, które będą odpytywać bazę

# Pobranie wszystkich obiektów
In [8]: Product.objects.all()  # Execution time: 0.530774s [Database: default]
In [9]: Product.objects.all()  # Execution time: 0.000175s [Database: default]

# Pobranie pierwszego obiektu (za każdym razem wykonuje zapytanie)
In [10]: Product.objects.all()[0]  # Execution time: 0.054815s [Database: default]                                                                                                                     
In [11]: Product.objects.all()[0]  # Execution time: 0.034775s [Database: default]

# list comprehension (za każdym razem wykonuje zapytanie)
In [12]: [product for product in Product.objects.all()]  # Execution time: 0.827393s [Database: default]
In [13]: [product for product in Product.objects.all()]  # Execution time: 0.807047s [Database: default]

Zapytania, które nie będą za każdym razem odpytywać bazy

# Ponowne użycie tego samego queryset do list comprehension
In [23]: products = Product.objects.all()
In [24]: product_list = [product for product in products]  # Execution time: 0.019153s [Database: default]
In [25]: product_list = [product for product in products]  # zapamiętanie zapytanie

# Pierwszy element wykonanego już zapytania
In [26]: products = Product.objects.all()
In [27]: list(products)  # Execution time: 0.233925s [Database: default]
In [28]: products[0]  # zapamiętany element (bez zapytania)

Czasy oczywiście nie będą tu wymierne, bo w czasie jednej sesji uruchomionego interpertera wykonałem wiele zapytań, więc Django sprytnie to zomptymalizowało. Zachęcam do zbadania tego na własną rękę, bo wyniki mogą nie raz zaskoczyć.

Values i values_list

Często podczas przeglądu kodu widzę, że pobierane są całe obiekty podczas gdy rzeczywiście wykorzystywane są tylko niektóre parametry. W zależności od tego czy wynik może być listą, czy musi być słownikiem, możemy skorzystać z values lub values_list. Zobaczmy w praktyce, jak wyglądają zbudowane zapytania i czasy odpowiedzi. Żeby uniknąć cache, za każdym razem będę od nowa uruchamiać interpreter.

Standardowe zapytanie

In [1]: Product.objects.all()                                                                                                                                           
Out[1]: SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 21

Execution time: 2.350809s [Database: default]
<QuerySet [<Product: Product object (1)>, <Product: Product object (2)>, <Product: Product object (3)>, <Product: Product object (4)>, <Product: Product object (5)>, <Product: Product object (6)>, <Product: Product object (7)>, <Product: Product object (8)>, <Product: Product object (9)>, <Product: Product object (10)>, <Product: Product object (11)>, <Product: Product object (12)>, <Product: Product object (13)>, <Product: Product object (14)>, <Product: Product object (15)>, <Product: Product object (16)>, <Product: Product object (17)>, <Product: Product object (18)>, <Product: Product object (19)>, <Product: Product object (20)>, '...(remaining elements truncated)...']>

Jeśli potrzebujemy tylko nazwy danego produktu, to pozostałe zapytania będą tylko wydłuzać czas zapytania. Spróbujmy pierwszego kroku optymalizcji.

Values, czyli wynik w formie słownika

In [1]: Product.objects.values("name")                                                                                                                                  
Out[1]: SELECT "products_product"."name"
  FROM "products_product"
 LIMIT 21

Execution time: 0.931418s [Database: default]
<QuerySet [{'name': ' Metro 2033: Przełom'}, {'name': " Marvel's Spider-Man (Gra PS4)"}, {'name': ' Samsung Galaxy S20 Plus SM-G985 128GB Czarny'}, {'name': 'name 0'}, {'name': 'name 1'}, {'name': 'name 2'}, {'name': 'name 3'}, {'name': 'name 4'}, {'name': 'name 5'}, {'name': 'name 6'}, {'name': 'name 7'}, {'name': 'name 8'}, {'name': 'name 9'}, {'name': 'name 10'}, {'name': 'name 11'}, {'name': 'name 12'}, {'name': 'name 13'}, {'name': 'name 14'}, {'name': 'name 15'}, {'name': 'name 16'}, '...(remaining elements truncated)...']>

Wynik poprawił się prawie o 250%, a im więcej nadmiarowych atrybtów, tym różnica będzie większa.

Values_list, czyli wynik w formie listy

Zdecydowanie częściej zdarzało mi ię wyciągać dane w postaci list niż słowników.

In [1]: Product.objects.values_list("name")                                                                                                                             
Out[1]: SELECT "products_product"."name"
  FROM "products_product"
 LIMIT 21

Execution time: 0.831459s [Database: default]
<QuerySet [(' Metro 2033: Przełom',), (" Marvel's Spider-Man (Gra PS4)",), (' Samsung Galaxy S20 Plus SM-G985 128GB Czarny',), ('name 0',), ('name 1',), ('name 2',), ('name 3',), ('name 4',), ('name 5',), ('name 6',), ('name 7',), ('name 8',), ('name 9',), ('name 10',), ('name 11',), ('name 12',), ('name 13',), ('name 14',), ('name 15',), ('name 16',), '...(remaining elements truncated)...']>

Values_list zwraca listę i to jest super, w przypadku kilku parametrów. Jeśli natomiast chcemy tylko jeden, to można skorzystać z dodatkowego opcjonalnego parametru, czyli flat.

In [1]: Product.objects.values_list("name", flat=True)                                                                                                                  
Out[1]: SELECT "products_product"."name"
  FROM "products_product"
 LIMIT 21

Execution time: 0.079867s [Database: default]
<QuerySet [' Metro 2033: Przełom', " Marvel's Spider-Man (Gra PS4)", ' Samsung Galaxy S20 Plus SM-G985 128GB Czarny', 'name 0', 'name 1', 'name 2', 'name 3', 'name 4', 'name 5', 'name 6', 'name 7', 'name 8', 'name 9', 'name 10', 'name 11', 'name 12', 'name 13', 'name 14', 'name 15', 'name 16', '...(remaining elements truncated)...']>

Inne wywołanie

Niżej wywołanie z więcej niż jednym atrybutem

In [1]: Product.objects.values("id", "name")                                                                                                                            
Out[1]: SELECT "products_product"."id",
       "products_product"."name"
  FROM "products_product"
 LIMIT 21

Execution time: 1.431266s [Database: default]

Only i defer

Jeśli musisz operować na konkretnych obiektach, a nie na słownikach lub listach to podobny efekt w SQL jak wcześniej uzyskasz przez only, lub defer.

Only

Określa które pola mają być pobrane

In [1]: queryset = Product.objects.only("name")                                                                                                                         

In [2]: products = [product.name for product in queryset]                                                                                                               
SELECT "products_product"."id",
       "products_product"."name"
  FROM "products_product"

Execution time: 1.460329s [Database: default]

Defer

Określa, które pola powinny zostać wykluczone

In [1]: queryset = Product.objects.defer("image", "description")                                                                                                        
In [2]: products = [product.name for product in queryset]                                                                                                               
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"

Execution time: 1.209743s [Database: default]

Uwaga

Osobiście nie bardzo lubię only i defer, bo jeśli odwołamy się atrybutu, którego nie mamy zadeklarowanego, to za każdym razem będzie wykonywane nowe zapytanie do bazy. A o taką pomyłkę bardzo łatwo, na przykład przy dodawaniu nowego pola w modelu. Zobaczmy to na przykładzie.

In [1]: queryset = Product.objects.only("name")[:3]                                                                                                                     

In [2]: products = [(product.slug, product.name) for product in queryset]                                                                                               
SELECT "products_product"."id",
       "products_product"."name"
  FROM "products_product"
 LIMIT 3

Execution time: 0.000606s [Database: default]
SELECT "products_product"."id",
       "products_product"."slug"
  FROM "products_product"
 WHERE "products_product"."id" = 1
 LIMIT 21

Execution time: 0.000110s [Database: default]
SELECT "products_product"."id",
       "products_product"."slug"
  FROM "products_product"
 WHERE "products_product"."id" = 2
 LIMIT 21

Execution time: 0.000073s [Database: default]
SELECT "products_product"."id",
       "products_product"."slug"
  FROM "products_product"
 WHERE "products_product"."id" = 3
 LIMIT 21

Execution time: 0.000071s [Database: default]

Tak więc zamiast poprawić aplikację tylko ją pogorszyliśmy.

Korzystaj z iterator przy dużych zapytaniach

Jeśli potrzebujesz przejść po liście obiektów tylko raz i jest to bardzo duży queryset, to warto zbudować z zapytania iterator, który zamiast ładować cały queryset, będzie odwoływać się do kolejnych elementów przy iteracji. O generatorach już po krótce pisałem. Teraz zobaczmy tylko szybkie porównanie

Pobieranie bez iteratora

In [1]: import time 
   ...: start = time.perf_counter() 
   ...: for product in Product.objects.all(): 
   ...:     tmp = product.name 
   ...: end = time.perf_counter() 
   ...: print(end - start)                                                                                                                                              
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"

Execution time: 0.000412s [Database: default]
10.454678344998683

Pobieranie poprzez iterator

In [1]: import time 
   ...: start = time.perf_counter() 
   ...: for product in Product.objects.all().iterator(): 
   ...:     tmp = product.name 
   ...: end = time.perf_counter() 
   ...: print(end - start)                                                                                                                                              
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"

Execution time: 0.000437s [Database: default]
7.2180140760028735

To niewiele ponad milion obiektów i prawie 3 sekundy różnicy, także całkiem nieźle.

Nie dajcie się zmanipulować

Zdarzyło się, że widziałem wpisy, w których generatory były chwalone i wyniki były kilkaset albo nawet kilka tysięcy razy lepsze. Zobaczcie taki przykład niżej

In [1]: import time 
   ...: start = time.perf_counter() 
   ...: (product for product in Product.objects.all().iterator()) 
   ...: end = time.perf_counter() 
   ...: print(end - start)                                                                                                                                              
0.00028210099844727665

Dlaczego taka operacja wykonała się tak szybko? A no z dwóch powodów. Po pierwsze zauważ, że z queryset zbudowaliśmy iterator. Po drugie zwróć uwagę na to jakie nawiasy zostały użyte. Gdyby były to nawiasy kwadratowe, to zbudowalibyśmy list comprehension, a tak mamy generator. I w ten oto sposób nie wykonaliśmy żadnego zapytania do bazy i nie mamy niczego w pamięci. I właśnie dlatego to zapytanie nie jest kilkadziesiąt, czy nawet kilkaset razy szybsze. Po prostu się nie wykonało. Dla porównania niżej dam przykład z list comprehension

In [1]: import time 
   ...: start = time.perf_counter() 
   ...: [product for product in Product.objects.all().iterator()] 
   ...: end = time.perf_counter() 
   ...: print(end - start)                                                                                                                                              
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"

Execution time: 0.000521s [Database: default]
10.23106057899713

Zauważ, że teraz zostało wykonane zapytanie i dane zostały załadowane do pamięci. Jakbyśmy mieli porównać czas wykonania obu fragmentów, to pierwszy wykonał się ponad 36 tysięcy razy szybciej. Następnym razem jak ktoś będzie zachwalać dane rozwiązanie i podawać wyniki po podobnym rzędzie wielkości, to postaraj się to zweryfikować.

Select_related oraz prefetch_related

Select_related

Rzadko pisze się programy, które operują na jednej tabeli. Znacznie częściej zachodzi potrzeba dociągania powiązanych danych. W naszym przykładzie może to być pobranie nazwy produktu aktualnej ceny. Zobacz jak wygląda przetłumaczone zapytanie przy takim prostym przykładzie

In [5]: price = Price.objects.first() 
   ...: price.product.name                                                                                                                                              
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 ORDER BY "products_price"."date" ASC
 LIMIT 1

Execution time: 0.395687s [Database: default]
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."id" = 1
 LIMIT 21

Execution time: 0.730168s [Database: default]
Out[5]: ' Metro 2033: Przełom'

W związku z tym że w pierwszej linijce pobraliśmy tylko dane o cenie, to za każdym razem przy odwołaniu się do produktu będzie wykonywane zapytanie. Dlatego poniższy kod wykona aż 5 zapytań do bazy. Jedno po ceny i w każdym przejściu pętli o produkty.

In [1]: prices = Price.objects.all()[:4]

In [2]: for price in prices:
   ...:     price.product.name
   ...: 
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 ORDER BY "products_price"."date" ASC
 LIMIT 4

Execution time: 1.019770s [Database: default]
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."id" = 1
 LIMIT 21

Execution time: 0.179471s [Database: default]
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."id" = 1
 LIMIT 21

Execution time: 0.729816s [Database: default]
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."id" = 2
 LIMIT 21

Execution time: 0.053747s [Database: default]
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."id" = 3
 LIMIT 21

Execution time: 0.129070s [Database: default]

W czystym SQL łatwo to ograniczyć przez łączenie tabel. Ale zaraz, ORM to nic innego jak tłumaczenie zapytań na SQL, zatem musi istnieć na to jakiś sposób. Wystarczy skorzystać z metody select_related, w której jako atrybut podaje się dowiązaną klasę. Zweryfikujmy to

In [1]: price = Price.objects.select_related("product").first() 
   ...: price.product.name                                                                                                                                              
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date",
       "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_price"
 INNER JOIN "products_product"
    ON ("products_price"."product_id" = "products_product"."id")
 ORDER BY "products_price"."date" ASC
 LIMIT 1

Execution time: 1.555109s [Database: default]
Out[1]: ' Metro 2033: Przełom'

Teraz widzimy, INNER JOIN pomiędzy tabelami products_price oraz products_product .  Dzięki temu jeśli poprawimy wcześniejszy kod, to zamiast 5 wykona się tylko jedno zapytanie. Proste i bardzo pomocne.

In [1]: prices = Price.objects.select_related("product")[:4]                                                                                                            

In [2]: for price in prices: 
   ...:     price.product.name 
   ...:                                                                                                                                                                 
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date",
       "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_price"
 INNER JOIN "products_product"
    ON ("products_price"."product_id" = "products_product"."id")
 ORDER BY "products_price"."date" ASC
 LIMIT 4

Execution time: 2.477847s [Database: default]

prefetch_related

Jak się pewnie domyślasz podobny problem może być przy pobieraniu cen kilku kolejnych produktów. Zobaczmy zatem ile wykonamy zapytań przy pobraniu cen 4 pierwszych produktów.

In [1]: prices = []

In [2]: products = Product.objects.all()[:4]

In [3]: for product in products:
   ...:     prices.append(product.price_set.all())
   ...: prices
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 4

Execution time: 0.213991s [Database: default]
Out[3]: SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1
 ORDER BY "products_price"."date" ASC
 LIMIT 21

Execution time: 0.830368s [Database: default]
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 2
 ORDER BY "products_price"."date" ASC
 LIMIT 21

Execution time: 0.229827s [Database: default]
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 3
 ORDER BY "products_price"."date" ASC
 LIMIT 21

Execution time: 0.330111s [Database: default]
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 4
 ORDER BY "products_price"."date" ASC
 LIMIT 21

Execution time: 0.729580s [Database: default]

Aż 5 zapytań, z czego jedno na produkt i przy każdym przejściu pętli kolejne zapytania o ceny dla tego produktu. Przy ograniczeniu do 4 elementów to nie stanowi problemu, ale przy kilkuset albo kilku tysiącach to już może zaboleć. Zobaczmy ile zapytań wykona się przy dodaniu prefetch_related

In [1]: prices = []

In [2]: products = Product.objects.prefetch_related("price_set")[:4]

In [3]: for product in products:
   ...:     prices.append(product.price_set.all())
   ...: prices
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 4

Execution time: 0.123551s [Database: default]

SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" IN (1, 2, 3, 4)
 ORDER BY "products_price"."date" ASC

Execution time: 1.332318s [Database: default]

Tym prostym rozwiązaniem udało nam się zejść z 5 do 2 zapytań.

Uwaga

Zauważmy jakie zapytanie jest budowane przy użyciu prefetch_related . Jest to zapytanie z WHERE . Jeśli chcielibyśmy dodać prefetch_related podczas pobierania wszystkich produktów, to ten SQL nie miałby prawa sie wykonać. Z drugiej strony jeśli do obiektów odwołujemy się jednorazowo, to również nie musimy tego specjalnie optymalizować. Dlatego zanim skorzystasz z jednego z wyżej wymienionych zastanów się jaki rząd wielkości danych przewidujesz, żeby nie natknąć się w przyszłości na problemy, które przecież przed chwila optymalizowałeś.

Pobieranie obiektów powiązanych

Przed chwilą pobierałem obiekty przez dziwną konstrukcję, której nie wyjaśniłem. Jeśli w modelu w Django nie podamy nazwy do relacji, to zostanie utworzona domyślna i będzie mieć strukturę < nazwa klasy>_set . Spójrzmy zatem na dwie konstrukcje, które zwrócą dokładnie takie samo zapytanie

In [2]: Price.objects.filter(product=product)                                                                                                                       
Out[2]: SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1
 ORDER BY "products_price"."date" ASC
 LIMIT 21

In [3]: product.price_set.all()                                                                                                                                         
Out[3]: SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1
 ORDER BY "products_price"."date" ASC
 LIMIT 21

Jaka jest zatem różnica i którego powinno się stosować? To wszystko zależy.
Jeśli mamy obiekt produktu, to czytelniejsze jest

product.price_set.all()

Jeśli natomiast nie mamy produktu a jedynie jego ID, to musimy już skorzystać z pierwszej opcji.

product_id = product.pk
Price.objects.filter(product=product_id)  # pobieranie po ID

Price.objects.filter(product=product)  # pobieranie po obiekcie

Tu od razu masz wskazówkę, bo jak widzisz to powyższego rozwiązania nie potrzebujesz obiektu. A w niektórych sytuacjach to bardzo cenna informacja.

Aktualizacja danych

Praktycznie cały wpis poświęcłem odczytowi danych. Ale to nie tak, że zapisów nie można zoptymalizować. Przyjmijmy, że w naszym systemie musimy podnieść wszystkie ceny, które kosztują 1 zł do 2 zł. Standardowo można zrobić pętlę i dla każdego wiersza robić oddzielny zapis. Ale można to zrobić znacznie prościej i przede wszystkim szybciej.

In [1]: Price.objects.filter(value=1).count()                                                                                                                           
SELECT COUNT(*) AS "__count"
  FROM "products_price"
 WHERE "products_price"."value" = '1'

Execution time: 0.303559s [Database: default]
Out[1]: 38189

In [2]: Price.objects.filter(value=1).update(value=2)                                                                                                                   
UPDATE "products_price"
   SET "value" = '2.00'
 WHERE "products_price"."value" = '1'

Execution time: 1.685790s [Database: default]
Out[2]: 38189

In [3]: Price.objects.filter(value=1).count()                                                                                                                           
SELECT COUNT(*) AS "__count"
  FROM "products_price"
 WHERE "products_price"."value" = '1'

Execution time: 0.298308s [Database: default]
Out[3]: 0

Najczęsztsze błędy

Teraz chciałbym omówić kilka błędów, które widzę najczęściej oraz te, które sam popełniałem na początku przygody z Django.

Filtruj na bazie a nie w Python

Odnosząc się do naszego przykładu, spróbujmy pobrać wszystkie ceny, które są mniejsze, lub równe 1.

Naiwny i zły sposób

import time

start = time.perf_counter()
prices = [price for price in Price.objects.all() if price.value <= 1]
end = time.perf_counter()
print("Pobrano", len(prices), "cen w czasie", end - start, "sekund")
# Pobrano 38875 cen w czasie 105.79278904300008 sekund

Zapytanie trwało tu zaledwie 1.390340s , a cały kod wykonał się prawie 2 minuty. Dlaczego tak długo? Po erwsze wyciągneliśmy z bazy wszystkie ceny, a po drugie po tych wszystkich cenach musieliśmy przejść i sprawdzić warunek.

Lepszy sposób - filter

import time

start = time.perf_counter()
prices = Price.objects.filter(value__lte=1)
end = time.perf_counter()
print("Pobrano", prices.count(), "cen w czasie", end - start, "sekund")
# Pobrano 38839 cen w czasie 0.07374403900757898 sekund

Kod jest czytelniejszy, zgrabniejszy i wykonał sie znacznie szybciej. Tu cały fragment kodu wykonał się szybciej niż w poprzednim wypadku zapytanie. Tak powinno sie optymalizować, a może nie tyle optymalizować co od razu pisać.

Pobieranie ID powiązanego obiektu

Wcześniej opisywałem jak korzystać z select_related, przy obiektach powiązanych. Czasem zachodzi potrzeba pobrania samego identyfikatora powiązanego obiektu a nie innych atrybutów. Zobaczmy to na przykładzie

Zły sposób

In [1]: price = Price.objects.first()                                                                                                                                   
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 ORDER BY "products_price"."date" ASC
 LIMIT 1

Execution time: 0.509204s [Database: default]

In [2]: price.product.pk                                                                                                                                                
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."id" = 1
 LIMIT 21

Execution time: 0.054184s [Database: default]
Out[2]: 1

Dlaczego rozwiązanie jest złe? Bo jak sam zapewne widzisz poszło nadmiarowe zapytanie do bazy. Niżej przykład jak to zrobić szybciej.

Dobry sposób

In [1]: price = Price.objects.first()                                                                                                                                   
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 ORDER BY "products_price"."date" ASC
 LIMIT 1

Execution time: 0.507325s [Database: default]

In [2]: price.product_id                                                                                                                                                
Out[2]: 1

Po co robić dwa zapytania skoro wystarczy jedno? Ponownie dałem przykład tylko dla jednego elementu. Przy kilku tysiącach obiektów różnica jest już odczuwalna

Sprwadzenie czy obiekt istnieje

Zdarza się, że nalezy podjąc jakąś akcję jeśli obiekty istnieją, bądź nie. W naszym przypadku może to być dodanie produktu do koszyka pod warunkiem, że istnieje dla niego przynajmniej jedna cena.

Zły sposób

In [2]: if product.price_set.all(): 
   ...:    # add_to_cart()
   ...:                                                                                                                                                                 
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1
 ORDER BY "products_price"."date" ASC

Execution time: 0.830923s [Database: default]

Dobry sposób

In [2]: if product.price_set.exists(): 
   ...:    # add_to_cart() 
   ...:                                                                                                                                                                 
SELECT (1) AS "a"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1
 LIMIT 1

Execution time: 0.078933s [Database: default]   

Efekt jest ten sam, ale czas wykonania już zdecydowanie krótszy. Bardzo ważne jest żeby używac exists wszędzie tam, gdzie interesuje nas jedynie czy obiekt istnieje a wartości jakie są zapisane są drugorzędną informacją.

Sprawdzenie długości queryset

Nawet niedawno natknąłem się na swój kod sprzed ponad 5 lat w którym sam to zrobiłem.

Zły sposób

In [2]: len(product.price_set.all())                                                                                                                                    
SELECT "products_price"."id",
       "products_price"."product_id",
       "products_price"."value",
       "products_price"."date"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1
 ORDER BY "products_price"."date" ASC

Execution time: 0.129717s [Database: default]
Out[2]: 273

Dobry sposób

In [2]: product.price_set.count()                                                                                                                                       
SELECT COUNT(*) AS "__count"
  FROM "products_price"
 WHERE "products_price"."product_id" = 1

Execution time: 0.008748s [Database: default]
Out[2]: 273

Po pierwsze oszczędzamy naszą bazę, bo zapytania po count są szybsze, po drugie przyśpieszamy naszą aplikację, co sami widzimy szybszą odpowiedzią, a po trzecie kod jest dużo czytelniejszy. Zatem same plusy.

Pobieranie pierwszego elementu

Jak najprościej pobrać pierwszy element na liście obiektów?

Zły sposób

In [1]: Product.objects.all()[0]                                                                                                                                        
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 LIMIT 1

Execution time: 0.438381s [Database: default]
Out[1]: <Product: Product object (1)>

Dobry sposób

In [1]: Product.objects.first()                                                                                                                                         
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 ORDER BY "products_product"."id" ASC
 LIMIT 1

Execution time: 0.409307s [Database: default]
Out[1]: <Product: Product object (1)>

A dlaczego te pierwsze rozwiązanie jest złe, skoro zapytanie jest podobne, a sam wynik jest takim sam? No to zobaczmy co by się stało gdyby lista wyników była pusta.

Pusta lista

In [1]: Product.objects.filter(name="ddeby kurs programowania w Python").first()                                                                                        
SELECT "products_product"."id",
       "products_product"."name",
       "products_product"."image",
       "products_product"."description",
       "products_product"."slug",
       "products_product"."ceneo_id"
  FROM "products_product"
 WHERE "products_product"."name" = 'ddeby kurs programowania w Python'
 ORDER BY "products_product"."id" ASC
 LIMIT 1

Execution time: 1.215422s [Database: default]

Jak widać nie napisałem jeszcze żadnego kursu programowania. Co się stanie przy pierwszym podejściu? IndexError .

Kiedy powinniśmy korzystać z ORM?

Wyżej przedstawiłem kilka sposobów na całkiem niezłą optymalizację zapytań przy wykorzystaniu ORM. Skupiłem się głównie na odczycie a z zapisu podałem tylko update, który sam stosuję najczęściej. Kiedy moim zdaniem powinno się stosować ORM

• Preferencje zespołu - jeśli zespół swobodnie czuje się w ORM i potrafi go pisać dobrze, a w SQL nie czuje się najlepiej to warto zostać przy ORM, zwłaszcza jeśli zapytania nie są krytyczną częścią systemu.
• Małe projekty - przy małych projektach, gdzie tabel nie będzie dużo praca z ORM będzie dużo wygodniejsza
• Czas - bardzo ważne kryterium. Jesli mamy dostarczyć aplikację szybko, to dopieszczanie każdego zapytania mija się z celem. Aplikacja, która ma obsłużyć black friday będzie lepsza nawet jak będzie działać nieco wolniej niż taka, co będzie odpowiadać poniżej 100ms, ale zostanie dostarczona dzień po promocjach.

Podsumowanie

Jeśli juz na początku tworzenia projektu mamy doświadczone osoby i wiemy, że ORM będzie jedynie utrudnieniem to warto od razu napisac własny poziom komunikacji z bazą danych. Z drugiej strony pamiętajmy, że krytyczne fragmentu kodu można napisać w czystym SQL, nawet posiadając ORM. Najgorsze co możesz zrobić to zabarykadować się po jednej i twierdzić, że SQL śmierdzi, albo że ORM jest tylko dla osób, które nie potrafią zbudować zapytania.

Jakie inne ciekawe tematy chcesz jeszcze przeczytać?

Podziel się opinią w komentarzu, a jeśli wpis okazał sie wartościowy podziel się z nim z innymi. W końcu wszystkim zależy na pisaniu szybszych aplikachi :)