Advanced Django ORM techniques

1.
Advanced Django ORM techniques Daniel Roseman http://blog.roseman.org.uk

2.
About Me • Pythonuser for five years • Discovered Django four years ago • Worked full-time with Python/Django since 2008. • Top Django answerer on StackOverflow! • Occasionally blog on Django, concentrating on efficient use of the ORM.

3.
Contents • Behind thescenes: models and ﬁelds • How model relationships work • More efﬁcient relationships • Other optimising techniques

4.
Django ORM efﬁciency: astory

6.
414 queries!

7.
How can youstop this happening to you? http://www.ﬂickr.com/photos/m0n0/4479450696

8.
Behind the scenes: modelsand ﬁelds http://www.ﬂickr.com/photos/spacesuitcatalyst/847530840

9.
Defining a model •Model structure initialised via metaclass • Called when model is first defined • Resulting model class stored in cache to use when instantiated

10.
Fields • Fields havecontribute_to_class • Adds methods, eg get_FOO_display() • Enables use of descriptors for ﬁeld access

11.
Model metadata • Model._meta • .fields • .get_field(fieldname) • .get_all_related_objects()

12.
Model instantiation • Instanceis populated from database initially • Has no subsequent relationship with db until save • No identity between models

13.
Querysets • Model=manager returnsa queryset: foos Foo.objects.all() • Queryset is an ordered list of instances of a single model • No database access yet • Slice: foos[0] • Iterate: {% for foo in foos %}

14.
Where do allthose queries come from? • Repeated queries • Lack of caching • Relational lookup • Templates as well as views

15.
Repeated queries def get_absolute_url(self): return "%s/%s" % ( self.category.slug, self.slug ) Same category, but query is repeated for each article

16.
Repeated queries • Samelink on every page • Dynamic, so can't go in urlconf • Could be cached or memoized

17.
Relationships http://www.ﬂickr.com/photos/katietegtmeyer/124315322

18.
Relational lookups • Forwards: foo.bar.ﬁeld • Backwards: bar.foo_set.all()

19.
Example models class Foo(models.Model): name = models.CharField(max_length=10) class Bar(models.Model): name = models.CharField(max_length=10) foo = models.ForeignKey(Foo)

20.
Forwards relationship >>> bar= Bar.objects.all()[0] >>> bar.__dict__ {'id': 1, 'foo_id': 1, 'name': u'item1'}

21.
Forwards relationship >>> bar.foo.name u'item1' >>>bar.__dict__ {'_foo_cache': <Foo: Foo object>, 'id': 1, 'foo_id': 1, 'name': u'item1'}

22.
Fowards relationships • Relationalaccess implemented via a descriptor: django.db.models.ﬁelds.related. SingleRelatedObjectDescriptor • __get__ tries to access _foo_cache • If doesn't exist, does lookup and creates cache

23.
select_related • Automatically followsforeign keys in SQL query • Prepopulates _foo_cache • Doesn't follow null=True relationships by default • Makes query more expensive, so be sure you need it

24.
Backwards relationships {% forfoo in my_foos %} {% for bar in foo.bar_set.all %} {{ bar.name }} {% endfor %} {% endfor %}

25.
Backwards relationships • Onequery per foo • If you iterate over foo_set again, you generate a new set of db hits • No _foo_cache • select_related does not work here

26.
Optimising backwards relationships • Get all related objects at once • Sort by ID of parent object • Then cache in hidden attribute as with select_related

27.
qs = Foo.objects.ﬁlter(criteria=whatever) obj_dict= dict([(obj.id, obj) for obj in qs]) objects = Bar.objects.ﬁlter(foo__in=qs) relation_dict = {} for obj in objects: relation_dict.setdefault( obj.foo_id, []).append(obj) for id, related in relation_dict.items(): obj_dict[id]._related = related

28.

29.

30.

31.

32.

33.
Optimising backwards [{'time': '0.000','sql': u'SELECT "foobar_foo"."id", "foobar_foo"."name" FROM "foobar_foo"'}, {'time': '0.000', 'sql': u'SELECT "foobar_bar"."id", "foobar_bar"."name", "foobar_bar"."foo_id" FROM "foobar_bar" WHERE "foobar_bar"."foo_id" IN (SELECT U0."id" FROM "foobar_foo" U0)'}]

34.
Optimising backwards • Stillquite expensive, as can mean large dependent subquery – MySQL in particular very bad at these • But now just two queries instead of n • Not automatic – need to remember to use _related_items attribute

35.
Generic relations • Foreignkey to ContentType, object_id • Descriptor to enable direct access • iterating through creates n+m queries(n=number of source objects, m=number of different content types) • ContentType objects automatically cached • Forwards relationship creates _foo_cache • but select_related doesn't work

36.
generics = {} foritem in queryset: generics.setdefault(item.content_type_id, set()).add(item.object_id) content_types = ContentType.objects.in_bulk( generics.keys()) relations = {} for ct, fk_list in generics.items(): ct_model = content_types[ct].model_class() relations[ct] = ct_model.objects. in_bulk(list(fk_list)) for item in queryset: setattr(item, '_content_object_cache', relations[content_type_id][item.object_id] )

37.

38.

39.

40.

41.

42.

43.

44.

45.
Other optimising techniques

46.
Memoizing • Cache propertyon ﬁrst access • Can cache within instance, if multiple accesses within same request def get_expensive_items(self): if not hasattr(self, '_cache'): self._cache = self.expensive_op() return self._cache

47.
DB Indexes • Payattention to slow query log and debug toolbar output • Add extra indexes where necessary - especially for multiple-column lookup • Use EXPLAIN

48.
Outsourcing • Does allthe logic need to go in the web app? • Services - via eg Piston • Message queues • Distributed tasks, eg Celery

49.
Summary • Understand wherequeries are coming from • Optimise where necessary, within Django or in the database • and...

50.
PROFILE

51.
Daniel Roseman http://blog.roseman.org.uk

Advanced Django ORM techniques