Пользователь
0,0
рейтинг
6 августа 2012 в 00:27

Разработка → Параметризованные запросы и производительность django orm

Столкнувшись с существенными потерями производительности на использовании django orm, я стал искать выход из положения, рассматривая разные способы использования orm. Что у меня получилось — смотрите подкатом.


Как пишется обычный кусок кода, использующий django orm?

Как правило, этот кусок входит в некую функцию, ну например view, получает параметры и формирует результат на основании этих параметров.

В качестве примера, рассмотрим следующую элементарную ситуацию: мы хотим получить список наименований групп, в которые входит текущий пользователь. Самый простой и очевидный, приходящий в голову в первую очередь, путь сделать это — получить список групп через отношение и выяснить их имена:

def myview(request):
  u = request.user
  a = [g.name for g in u.groups.all()]
  ...


Проверим, какова будет производительность данного куска, имея в виду, что объект пользователя request.user уже получен на этапе предварительной обработки запроса.

Создадим группу thetest и присоединим к ней самого первого пользователя:
>>> u = User.objects.all()[0]
>>> g = Group(name='thetest')
>>> g.save()
>>> u.groups.add(g)
>>> u.groups.all()
[<Group: thetest>]


Я буду использовать этот кейз во всех дальнейших тестах. Поскольку все делается через shell, я также использую в них переменную u, полученную на данном этапе.

Итак, тест номер 1, выполняем задуманный кусок кода. Проверим, действительно ли он возвращает искомый список:

>>> a = [g.name for g in u.groups.all()]
>>> a
[u'thetest']


Чтобы померить производительность, выполним его 1000 раз.

>>> def test1():
...  import datetime
...  t1 = datetime.datetime.now()
...  for i in xrange(1000):
...   a = [g.name for g in u.groups.all()]
...  t2 = datetime.datetime.now()
...  print "%s" % (t2 - t1)
... 
>>> test1()
0:00:01.437324

Тысяча оборотов нашего цикла выполнялась около полутора секунд, что дает 1.5 миллисекунды на запрос.

Опытные джангописатели наверняка уже собрались ткнуть меня носом в то, что данный кусок далек от оптимальности. Действительно, можно написать на первый взгляд, более оптимальный участок кода, который выполнит те же действия, не конструируя объект группы и получая из базы только те данные, которые действительно нам нужны:

>>> a = [g['name'] for g in u.groups.values('name')]
>>> a
[u'thetest']


Что ж, померяем и этот кусок.

>>> def test2():
...  import datetime
...  t1 = datetime.datetime.now()
...  for i in xrange(1000):
...   a = [g['name'] for g in u.groups.values('name')]
...  t2 = datetime.datetime.now()
...  print "%s" % (t2 - t1)
... 
>>> test2()
0:00:01.752529

Это кажется противоестественным, но вторая версия нашего кода менее оптимальна чем первая?

В действительности, это так оно и есть. Потери на вызове values() и дополнительном анализе запроса оказались выше, чем потенциальная экономия на конструировании объекта Group и получении значений всех его полей.

Но позвольте? А зачем собственно всякий раз заново конструировать и анализировать запрос, если мы в нашем view будем всегда выполнять этот же запрос, а отличаться будет только объект пользователя, на котором этот запрос выполняется?

К сожалению, django изначально не позволяет подготовить запрос заранее, обращаясь к подготовленному запросу по мере необходимости. Нет соответствующих вызовов, да и синтаксис формирования запроса подразумевает использование только конкретных значений в качестве параметров запроса.

Придется немножко полазить по исходникам. Пользуясь случаем, хотел бы выразить признательность разработчикам django_extensions и их замечательной команде shell_plus, которая сильно облегчает интроспекцию объектов.

Оказывается, у объекта QuerySet (это тот самый, который получается например в момент обращения к objects.all()) есть свойство query, объект класса django.db.models.sql.query.Query. У которого в свою очередь, есть метод sql_with_params()

Этот метод возвращает набор параметров, полностью готовый для передачи в cursor.execute() — то есть строку выражения SQL и дополнительные параметры. Самое замечательное заключается в том, что эти самые дополнительные параметры и есть те параметры, которые передаются объекту QuerySet при его формировании:

>>> u.groups.all().values('name').query.sql_with_params()
('SELECT `auth_group`.`name` FROM `auth_group` INNER JOIN `auth_user_groups` ON (`auth_group`.`id` = `auth_user_groups`.`group_id`) WHERE `auth_user_groups`.`user_id` = %s ', (1,))


Теперь, если мы получим подготовленный запрос SQL и будем подставлять в него различные значения параметров, мы сможем выполнять запрос, не тратя ресурсы на подготовку запроса.

Для этого создадим специальный класс, скрывающий внутри все детали хака, который мы собираемся совершить.

from django.db import connection
from django.db.models.query import QuerySet,ValuesQuerySet

import django

from threading import local

class PQuery(local):
    def __init__(self,query,connection=connection,**placeholders):
        self.query = query
        self.connection = connection
        self.placeholders = placeholders
        self.replaces = {}
        sql = None
        try:
            sql = self.query.query.sql_with_params() # 1.4
        except AttributeError:
            sql = self.query.query.get_compiler(connection=self.connection).as_sql() # 1.3, lower?
        self.places = list(sql[1])
        self.sql = sql[0]
        self.is_values = isinstance(query,ValuesQuerySet)
        self.cursor = None
        for i in xrange(len(self.places)):
            x = self.places[i]
            found = False
            for p in self.placeholders:
                v = self.placeholders[p]
                if x == v:
                    found = True
                    if not p in self.replaces:
                        self.replaces[p] = []
                    self.replaces[p].append(i)
            if not found:
                raise AttributeError("The placeholder %(ph)s not found, please add some_name=%(ph)s to the list of constructor parameters" % {
                    'ph':repr(x)
                })

    def execute(self,**kw):
        try:
            for k in kw:
                for i in self.replaces[k]:
                    self.places[i] = kw[k]
        except KeyError,ex:
            raise TypeError("No such placeholder: %s" % k)
        if not self.cursor:
            self.cursor = self.connection.cursor()
        self.cursor.execute(self.sql,self.places)
        if not hasattr(self,'fldnms'):
            self.fldnms = [col[0] for col in self.cursor.description]
        if self.is_values:
            return [dict(zip(self.fldnms,row)) for row in self.cursor.fetchall()]
        return [self.query.model(**dict(zip(self.fldnms,row))) for row in self.cursor.fetchall()]

    def __call__(self,**kw):
        return self.execute(**kw)

ParametrizedQuery = PQuery # compatibility issue

UPD: 2012-08-06 19:20:00 MSK — внес поправки в код по поводу совместимости с мультитредингом, исправления мелких недочетов при выполнении сложных запросов и улучшения юзабельности.
Предыдущая версия кода
from django.db import connection
from django.db.models.query import QuerySet,ValuesQuerySet

class ParametrizedQuery:
    def __init__(self,query,connection=connection,**placeholders):
        self.query = query
        self.connection = connection
        self.placeholders = placeholders
        self.replaces = {}
        sql = self.query.query.sql_with_params()
        self.places = list(sql[1])
        self.sql = sql[0]
        self.is_values = isinstance(query,ValuesQuerySet)
        self.cursor = None
        for p in self.placeholders:
            v = self.placeholders[p]
            self.replaces[p] = self.places.index(v)

    def execute(self,**kw):
        for k in kw:
            self.places[self.replaces[k]] = kw[k]
        if not self.cursor:
            self.cursor = self.connection.cursor()
        self.cursor.execute(self.sql,self.places)
        if not hasattr(self,'fldnms'):
            self.fldnms = [col[0] for col in self.cursor.description]
        if self.is_values:
            return [dict(zip(self.fldnms,row)) for row in self.cursor.fetchall()]
        return [self.query.model(**dict(zip(self.fldnms,row))) for row in self.cursor.fetchall()]


Что делает этот класс? Он получает запрос и выковыривает из него подготовленный SQL и параметры. Мы можем сформировать такой запрос, в котором каждый из параметров, который мы собираемся подставлять, имеет специальное, известное нам заранее значение. Эти значения мы будем использовать для поиска того места, в которое мы хотим подставлять значения, передаваемые во время исполнения.

Несколько дополнительных деталей реализации также помогут нам сэкономить ресурсы.
  • Свойство fldnms содержит массив имен полей, получаемый при первом исполнении запроса. Последующие вызовы будут использовать готовый массив.
  • Свойство replaces содержит отображение имен подстановок в номера параметров.
  • Каждый объект нашего класса будет держать собственный курсор. Потенциальное ускорение от этого шага является следствием во-первых, того что создание курсора — достаточно затратная операция, а во-вторых — следующей фразы из описания pyodbc, который может быть использован в качестве бакенда базы данных: «It is also be more efficient if you execute the same SQL repeatedly with different parameters. The SQL will be prepared only once. (pyodbc only keeps the last statement prepared, so if you switch between statements, each will be prepared multiple times.)»
  • Свойство is_values поможет нам определить, что запрос не должен возвращать объект модели, что позволит сэкономить на создании такого объекта при возвращении результатов.


Немного изменим исходный запрос, чтобы было удобно подсовывать туда подстановки:

>>> q = Group.objects.filter(user__id=12345).values('name')
>>> q.query.sql_with_params()
('SELECT `auth_group`.`name` FROM `auth_group` INNER JOIN `auth_user_groups` ON (`auth_group`.`id` = `auth_user_groups`.`group_id`) WHERE `auth_user_groups`.`user_id` = %s ', (12345,))


Значение 12345 мы используем как подстановку:

>>> p = ParametrizedQuery(q,user_id=12345)
>>> [g['name'] for g in p.execute(user_id=u.id)]
[u'thetest']


При исполнении запроса p.execute() на место подстановки 12345 было подставлено реальное значение идентификатора пользователя.

Попробуем теперь посмотреть, как изменится производительность кода:

>>> def test3():
...  import datetime
...  t1 = datetime.datetime.now()
...  for i in xrange(1000):
...   a = [g['name'] for g in p.execute(user_id=u.id)]
...  t2 = datetime.datetime.now()
...  print "%s" % (t2 - t1)
... 
>>> test3()
0:00:00.217270

Вот это результат! Время исполнения запроса сократилось в 7 раз.

Как это использовать в реальном коде?

Во-первых, необходимо место, в котором подготовленный запрос мог бы храниться. Во-вторых, в какой-то момент нужно заполнить эту переменную. Например, в момент первого исполнения кода функции. Ну и в третьих, разумеется, мы используем обращение к параметризованному запросу вместо прямого исполнения запроса.

def myview(request):
 if not hasattr(myview,'query'):
  myview.query = ParametrizedQuery(Group.objects.filter(user__id=12345).values('name'),user_id=12345)
 a = [g['name'] for g in myview.query.execute(user_id=request.user.id)]
 ...


Весь код исполнялся на:
— django.VERSION=(1, 4, 0, 'final', 0)
— СУБД mysql (django.db.backends.mysql)
— table engine=MYISAM
— соединение через localhost
— Python 2.7.2+ (default, Oct 4 2011, 20:03:08) [GCC 4.6.1] on linux2
— хост Linux seva 3.0.0-22-generic #36-Ubuntu SMP Tue Jun 12 17:13:04 UTC 2012 i686 athlon i386 GNU/Linux

Комментарии специалистов приветствуются.
Всеволод Новиков @nnseva
карма
32,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

Самое читаемое Разработка

Комментарии (19)

  • –6
    По мне так, код не особо «красивым» получается. Мне больше нравится такая система:
    q = query('table', 'alias').select('a', 'b', 'c').where(a = 'b', b = 2).orderBy('a', true);
    Реализованы классы у меня под python и php для mysql и postgresql. В скором времени, наверное, выложу на гитхаб и накатаю статейку.
    • +11
      вы просто создали еще один способ записать неудобно sql.
  • +1
    1.5 мс на выполнение запроса к БД — это не много. Не самое критичное место в производительности веб приложений. Тот же рендеринг шаблона у вас займет явно больше.
    • +3
      ^ this. Особенно, если шаблоны рендерятся обычными джанговыми темплейтами.
    • +3
      Сильно подозреваю (но еще не проверял), что одним из основных мест торможения при рендеринге шаблонов может стать как раз доступ к объектам через ORM. Если вы используете в шаблоне например тот же пресловутый список групп, получая его допустим через выражение наподобие
      <ul>
      {% for u in user.groups.all %}
        <li>{{ u.name }}</li>
      {% endfor %}
      </ul>
      

      то при рендеринге такого шаблона как раз и случится описанный в топике тормоз.
  • –1
    А вот так не пробовали?
    q = Group.objects.values('name')
    ..
    a = q.filter(user=u)
    

    Без всяких хаков дало прирост в 10 раз.
    Бало как и у вас 1.6 сек, стало 0.15 сек.
    • +1
      a = q.filter(user=u) возвращает подготовленный QuerySet, а вовсе не список значений. Попробуйте a=list(q.filter(user=u)) и вы увидите разницу
      • 0
        Вы правы, об этом я не подумал.
  • +2
    Мне кажется, подход правильный, понравилось.

    1) Вроде лучше было в бенчмарках использовать

    .values_list('name', flat=True)

    вместо

    [g['name'] for g in u.groups.values('name')].

    2) Не очень приятная штука — в Group.objects.filter(user__id=12345).values('name') используется user__id, а в ParametrizedQuery — user_id. Было б удобно, если бы все было одинаково, или хотя бы было очевидно, как имена получать. Ведь если бы запрос был с user__username, нужно было бы писать не user_username, а просто username, так? Или еще что-то другое? Это все неочевидно довольно.

    3) Пример из реального кода не понял — зачем там 12345. Ну и код с багами на потокобезопасность — у вас общий query на все потоки (т.к. он атрибут myview), execute меняет некоторые атрибуты query (self.places, например) — в момент вызова self.cursor.execute(self.sql,self.places) уже нельзя гарантировать, что в self.places были на основе **kw из этого потока построены.

    Тут, мне кажется, 2 вещи: (а) .execute не должен менять состояние и (б) подготовленный запрос создать бы вне вьюхи (лучше всего — в models.py или managers.py; самое правильное — вообще в менеджере или в кастомном QuerySet для модели) — там же ничего зависящего от request нет, и несколько экземпляров не нужны, если execute состояние менять не будет.
    • 0
      А по реализации — через RawQuerySet этот хак не получается сделать? Туда и query можно передать готовый (не raw_query), и params + логика по построению моделей более хитрая уже реализована — например, части ответа могут быть доступны как атрибуты модели, но не будут передаваться в конструктор (какое-нибудь вычисляемое в sql-запросе поле, например). Сам не пробовал, но код похоже выглядит.
    • 0
      1) values_list погоды не делает, тормозит практически так же
      2,3) user__id — это обращение к полю id объекта user, в то время как user_id — это вообще говоря произвольное имя параметра параметризованного запроса, там мог бы стоять например vasq_pupkin:

      >>> p = ParametrizedQuery(q,vasq_pupkin=12345)
      >>> [g['name'] for g in p.execute(vasq_pupkin=u.id)]
      


      Идея заключается в том, что значение 12345 в исходном запросе QuerySet является отметкой того места, куда должно попасть значение соответствующего параметра ParametrizedQuery при его фактическом использовании. То есть последовательность следующая:
      • В исходном запросе мы отмечаем фейковыми значениями (12345, «QQ», datetime.datetime(2345,11,11) и т.д.) те места, которые будут заполняться фактическими значениями параметров при выполнении запроса.
      • Формируя параметризованный запрос, мы даем имена параметрам запроса и связываем эти имена с теми местами, в которые должны попасть фактически переданные значения параметров. Связывание происходит через те фейковые значения, которые переданы в исходный запрос.
      • При использовании параметризованного запроса, мы передаем фактические значения через те параметры, которые были поименованы при формировании параметризованного запроса. Эти значения заменяют собой фейковые значения, переданные при формировании исходного запроса

      Это в некотором смысле извращение, которое продиктовано тем, что django не имеет синтаксиса, подходящего для передачи в запрос формальных параметров.

      3) Про многопоточность — да, я заметил эту проблему и собираюсь как раз сейчас сделать апдейт поста с поправками по поводу многопоточности и еще несколько мелких недочетов.
  • 0
    In [10]: %time t=[u.groups.all() for i in range(1000)]
    CPU times: user 0.63 s, sys: 0.01 s, total: 0.64 s
    Wall time: 0.64 s

    In [11]: %time t=[u.groups.values('name') for i in range(1000)]
    CPU times: user 1.03 s, sys: 0.02 s, total: 1.06 s
    Wall time: 1.04 s

    На самом деле, если посмотреть в connection.queries из django.db.models, то первый и второй запрос к базе практически не отличаются. Хотя потеря почти в ~0.40с.

    Действительно дело состоит в скорости работы самого django orm. Если честно то ситуация с таким подходом «разгона» orm как минимум занятная. Так как от мы теряем по сути большую часть общего функционала, но получаем быстрый кусок точных данных. Если честно, то мне скорее непонятно где конкретно это может пригодится.
    • 0
      u.groups.all() не возвращает данные. Он возвращает QuerySet. Правильно мерять надо было так:

      %time t=[list(u.groups.all()) for i in range(1000)]
      %time t=[list(u.groups.values('name')) for i in range(1000)]
      

      • +1
        Кстати, это же ipython? Тем через %timeit можно замеры делать — он и количество итераций сам выберет, и несколько раз бенчмарк прогонит, и сборщик мусора отключит, как-то так:

        %timeit list(u.groups.all())
      • 0
        Данные в данном ключе не принципиальны, так как вопрос стоит в самой работе orm, а не оптимизации запроса к базе. А вот расхождение между .values('names') и all() действительно значительны, при учете того, что работает только ORM:

        Без получения данных:
        In [14]: %time t=[u.groups.all() for i in range(1000)]
        CPU times: user 0.69 s, sys: 0.01 s, total: 0.70 s
        Wall time: 0.69 s

        In [15]: %time t=[u.groups.values('name') for i in range(1000)]
        CPU times: user 1.04 s, sys: 0.01 s, total: 1.05 s
        Wall time: 1.04 s

        С получением:
        In [17]: %time t=[list(u.groups.all()) for i in range(1000)]
        CPU times: user 1.03 s, sys: 0.02 s, total: 1.05 s
        Wall time: 1.05 s

        In [18]: %time t=[list(u.groups.values('name')) for i in range(1000)]
        CPU times: user 1.36 s, sys: 0.02 s, total: 1.38 s
        Wall time: 1.39 s

        Это примерно показывает, то что и должно: запросы и в первом случае и во втором практически одинаковы, но тем не менее .values('names') работает значительно медленнее.

        Спасибо кстати за наводку, если бы не прочитал статью не думал бы, что values медленнее настолько :)
        • 0
          Потери на 50% на values это плохо конечно. Но меня лично прикалывает объем потерь именно на коде, который мог бы быть вынесен за пределы рабочего цикла вообще — компиляция запроса sql.
  • 0
    It is also be more efficient if you execute the same SQL repeatedly with different parameters. The SQL will be prepared only once.

    хочется отметить, что настоящие prepared statements связаны с выделением долгоживущих ресурсов. т.е. одно дело служебные скрипты, но если клиентов много (как в web), такую схему нужно обязательно тестировать на нагрузки. к тому же они поддерживаются далеко не всеми питонячими драйверами и имеют характерные ограничения.
    • 0
      В классической конфигурации django-wsgi — клиентов (БД) будет ровно столько, сколько WSGI исполнителей. Количество препарированных выражений будет пропорционально количеству строк кода, таким образом нагрузка легко измерима и контролируема. Впрочем конечно же, тестировать надо всегда.

      Из питонячьих дриверов упоминание о prepared statements мне удалось найти только в pyodbc. Структура интерфейса «стандартного клиента СУБД» к сожалению, провоцирует разработчиков игнорировать prepared statements — поскольку не поддерживает явного обращения к ним.
      • 0
        для mysql prepared statements поддерживаются драйвером oursql.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.