TEXT 28
Optimization.txt Guest on 29th April 2021 06:44:11 AM
  1. ============================
  2. Database access optimization
  3. ============================
  4.  
  5. Django's database layer provides various ways to help developers get the most
  6. out of their databases. This document gathers together links to the relevant
  7. documentation, and adds various tips, organized under a number of headings that
  8. outline the steps to take when attempting to optimize your database usage.
  9.  
  10. Profile first
  11. =============
  12.  
  13. As general programming practice, this goes without saying. Find out :ref:`what
  14. queries you are doing and what they are costing you
  15. <faq-see-raw-sql-queries>`. You may also want to use an external project like
  16. django-debug-toolbar_, or a tool that monitors your database directly.
  17.  
  18. Remember that you may be optimizing for speed or memory or both, depending on
  19. your requirements. Sometimes optimizing for one will be detrimental to the
  20. other, but sometimes they will help each other. Also, work that is done by the
  21. database process might not have the same cost (to you) as the same amount of
  22. work done in your Python process. It is up to you to decide what your
  23. priorities are, where the balance must lie, and profile all of these as required
  24. since this will depend on your application and server.
  25.  
  26. With everything that follows, remember to profile after every change to ensure
  27. that the change is a benefit, and a big enough benefit given the decrease in
  28. readability of your code. **All** of the suggestions below come with the caveat
  29. that in your circumstances the general principle might not apply, or might even
  30. be reversed.
  31.  
  32. .. _django-debug-toolbar: https://github.com/django-debug-toolbar/django-debug-toolbar/
  33.  
  34. Use standard DB optimization techniques
  35. =======================================
  36.  
  37. ...including:
  38.  
  39. * Indexes_. This is a number one priority, *after* you have determined from
  40.   profiling what indexes should be added. Use
  41.   :attr:`Field.db_index <django.db.models.Field.db_index>` or
  42.   :attr:`Meta.index_together <django.db.models.Options.index_together>` to add
  43.   these from Django. Consider adding indexes to fields that you frequently
  44.   query using :meth:`~django.db.models.query.QuerySet.filter()`,
  45.   :meth:`~django.db.models.query.QuerySet.exclude()`,
  46.   :meth:`~django.db.models.query.QuerySet.order_by()`, etc. as indexes may help
  47.   to speed up lookups. Note that determining the best indexes is a complex
  48.   database-dependent topic that will depend on your particular application.
  49.   The overhead of maintaining an index may outweigh any gains in query speed.
  50.  
  51. .. _Indexes: http://en.wikipedia.org/wiki/Database_index
  52.  
  53. * Appropriate use of field types.
  54.  
  55. We will assume you have done the obvious things above. The rest of this document
  56. focuses on how to use Django in such a way that you are not doing unnecessary
  57. work. This document also does not address other optimization techniques that
  58. apply to all expensive operations, such as :doc:`general purpose caching
  59. </topics/cache>`.
  60.  
  61. Understand QuerySets
  62. ====================
  63.  
  64. Understanding :doc:`QuerySets </ref/models/querysets>` is vital to getting good
  65. performance with simple code. In particular:
  66.  
  67. Understand QuerySet evaluation
  68. ------------------------------
  69.  
  70. To avoid performance problems, it is important to understand:
  71.  
  72. * that :ref:`QuerySets are lazy <querysets-are-lazy>`.
  73.  
  74. * when :ref:`they are evaluated <when-querysets-are-evaluated>`.
  75.  
  76. * how :ref:`the data is held in memory <caching-and-querysets>`.
  77.  
  78. Understand cached attributes
  79. ----------------------------
  80.  
  81. As well as caching of the whole ``QuerySet``, there is caching of the result of
  82. attributes on ORM objects. In general, attributes that are not callable will be
  83. cached. For example, assuming the :ref:`example Weblog models
  84. <queryset-model-example>`::
  85.  
  86.   >>> entry = Entry.objects.get(id=1)
  87.   >>> entry.blog   # Blog object is retrieved at this point
  88.   >>> entry.blog   # cached version, no DB access
  89.  
  90. But in general, callable attributes cause DB lookups every time::
  91.  
  92.   >>> entry = Entry.objects.get(id=1)
  93.   >>> entry.authors.all()   # query performed
  94.   >>> entry.authors.all()   # query performed again
  95.  
  96. Be careful when reading template code - the template system does not allow use
  97. of parentheses, but will call callables automatically, hiding the above
  98. distinction.
  99.  
  100. Be careful with your own custom properties - it is up to you to implement
  101. caching when required, for example using the
  102. :class:`~django.utils.functional.cached_property` decorator.
  103.  
  104. Use the ``with`` template tag
  105. -----------------------------
  106.  
  107. To make use of the caching behavior of ``QuerySet``, you may need to use the
  108. :ttag:`with` template tag.
  109.  
  110. Use ``iterator()``
  111. ------------------
  112.  
  113. When you have a lot of objects, the caching behavior of the ``QuerySet`` can
  114. cause a large amount of memory to be used. In this case,
  115. :meth:`~django.db.models.query.QuerySet.iterator()` may help.
  116.  
  117. Do database work in the database rather than in Python
  118. ======================================================
  119.  
  120. For instance:
  121.  
  122. * At the most basic level, use :ref:`filter and exclude <queryset-api>` to do
  123.   filtering in the database.
  124.  
  125. * Use :class:`F expressions <django.db.models.F>` to filter
  126.   based on other fields within the same model.
  127.  
  128. * Use :doc:`annotate to do aggregation in the database
  129.   </topics/db/aggregation>`.
  130.  
  131. If these aren't enough to generate the SQL you need:
  132.  
  133. Use ``QuerySet.extra()``
  134. ------------------------
  135.  
  136. A less portable but more powerful method is
  137. :meth:`~django.db.models.query.QuerySet.extra()`, which allows some SQL to be
  138. explicitly added to the query. If that still isn't powerful enough:
  139.  
  140. Use raw SQL
  141. -----------
  142.  
  143. Write your own :doc:`custom SQL to retrieve data or populate models
  144. </topics/db/sql>`. Use ``django.db.connection.queries`` to find out what Django
  145. is writing for you and start from there.
  146.  
  147. Retrieve individual objects using a unique, indexed column
  148. ==========================================================
  149.  
  150. There are two reasons to use a column with
  151. :attr:`~django.db.models.Field.unique` or
  152. :attr:`~django.db.models.Field.db_index` when using
  153. :meth:`~django.db.models.query.QuerySet.get` to retrieve individual objects.
  154. First, the query will be quicker because of the underlying database index.
  155. Also, the query could run much slower if multiple objects match the lookup;
  156. having a unique constraint on the column guarantees this will never happen.
  157.  
  158. So using the :ref:`example Weblog models <queryset-model-example>`::
  159.  
  160.   >>> entry = Entry.objects.get(id=10)
  161.  
  162. will be quicker than:
  163.  
  164.   >>> entry = Entry.object.get(headline="News Item Title")
  165.  
  166. because ``id`` is indexed by the database and is guaranteed to be unique.
  167.  
  168. Doing the following is potentially quite slow:
  169.  
  170.   >>> entry = Entry.objects.get(headline__startswith="News")
  171.  
  172. First of all, ``headline`` is not indexed, which will make the underlying
  173. database fetch slower.
  174.  
  175. Second, the lookup doesn't guarantee that only one object will be returned.
  176. If the query matches more than one object, it will retrieve and transfer all of
  177. them from the database. This penalty could be substantial if hundreds or
  178. thousands of records are returned. The penalty will be compounded if the
  179. database lives on a separate server, where network overhead and latency also
  180. play a factor.
  181.  
  182. Retrieve everything at once if you know you will need it
  183. ========================================================
  184.  
  185. Hitting the database multiple times for different parts of a single 'set' of
  186. data that you will need all parts of is, in general, less efficient than
  187. retrieving it all in one query. This is particularly important if you have a
  188. query that is executed in a loop, and could therefore end up doing many database
  189. queries, when only one was needed. So:
  190.  
  191. Use ``QuerySet.select_related()`` and ``prefetch_related()``
  192. ------------------------------------------------------------
  193.  
  194. Understand :meth:`~django.db.models.query.QuerySet.select_related` and
  195. :meth:`~django.db.models.query.QuerySet.prefetch_related` thoroughly, and use
  196. them:
  197.  
  198. * in view code,
  199.  
  200. * and in :doc:`managers and default managers </topics/db/managers>` where
  201.   appropriate. Be aware when your manager is and is not used; sometimes this is
  202.   tricky so don't make assumptions.
  203.  
  204. Don't retrieve things you don't need
  205. ====================================
  206.  
  207. Use ``QuerySet.values()`` and ``values_list()``
  208. -----------------------------------------------
  209.  
  210. When you just want a ``dict`` or ``list`` of values, and don't need ORM model
  211. objects, make appropriate usage of
  212. :meth:`~django.db.models.query.QuerySet.values()`.
  213. These can be useful for replacing model objects in template code - as long as
  214. the dicts you supply have the same attributes as those used in the template,
  215. you are fine.
  216.  
  217. Use ``QuerySet.defer()`` and ``only()``
  218. ---------------------------------------
  219.  
  220. Use :meth:`~django.db.models.query.QuerySet.defer()` and
  221. :meth:`~django.db.models.query.QuerySet.only()` if there are database columns
  222. you know that you won't need (or won't need in most cases) to avoid loading
  223. them. Note that if you *do* use them, the ORM will have to go and get them in
  224. a separate query, making this a pessimization if you use it inappropriately.
  225.  
  226. Also, be aware that there is some (small extra) overhead incurred inside
  227. Django when constructing a model with deferred fields. Don't be too aggressive
  228. in deferring fields without profiling as the database has to read most of the
  229. non-text, non-VARCHAR data from the disk for a single row in the results, even
  230. if it ends up only using a few columns. The ``defer()`` and ``only()`` methods
  231. are most useful when you can avoid loading a lot of text data or for fields
  232. that might take a lot of processing to convert back to Python. As always,
  233. profile first, then optimize.
  234.  
  235. Use QuerySet.count()
  236. --------------------
  237.  
  238. ...if you only want the count, rather than doing ``len(queryset)``.
  239.  
  240. Use QuerySet.exists()
  241. ---------------------
  242.  
  243. ...if you only want to find out if at least one result exists, rather than ``if
  244. queryset``.
  245.  
  246. But:
  247.  
  248. .. _overuse_of_count_and_exists:
  249.  
  250. Don't overuse ``count()`` and ``exists()``
  251. ------------------------------------------
  252.  
  253. If you are going to need other data from the QuerySet, just evaluate it.
  254.  
  255. For example, assuming an Email model that has a ``body`` attribute and a
  256. many-to-many relation to User, the following template code is optimal:
  257.  
  258. .. code-block:: html+django
  259.  
  260.    {% if display_inbox %}
  261.      {% with emails=user.emails.all %}
  262.        {% if emails %}
  263.          <p>You have {{ emails|length }} email(s)</p>
  264.          {% for email in emails %}
  265.            <p>{{ email.body }}</p>
  266.          {% endfor %}
  267.        {% else %}
  268.          <p>No messages today.</p>
  269.        {% endif %}
  270.      {% endwith %}
  271.    {% endif %}
  272.  
  273.  
  274. It is optimal because:
  275.  
  276. 1. Since QuerySets are lazy, this does no database queries if 'display_inbox'
  277.    is False.
  278.  
  279. #. Use of :ttag:`with` means that we store ``user.emails.all`` in a variable
  280.    for later use, allowing its cache to be re-used.
  281.  
  282. #. The line ``{% if emails %}`` causes ``QuerySet.__bool__()`` to be called,
  283.    which causes the ``user.emails.all()`` query to be run on the database, and
  284.    at the least the first line to be turned into an ORM object. If there aren't
  285.    any results, it will return False, otherwise True.
  286.  
  287. #. The use of ``{{ emails|length }}`` calls ``QuerySet.__len__()``, filling
  288.    out the rest of the cache without doing another query.
  289.  
  290. #. The :ttag:`for` loop iterates over the already filled cache.
  291.  
  292. In total, this code does either one or zero database queries. The only
  293. deliberate optimization performed is the use of the :ttag:`with` tag. Using
  294. ``QuerySet.exists()`` or ``QuerySet.count()`` at any point would cause
  295. additional queries.
  296.  
  297. Use ``QuerySet.update()`` and ``delete()``
  298. ------------------------------------------
  299.  
  300. Rather than retrieve a load of objects, set some values, and save them
  301. individual, use a bulk SQL UPDATE statement, via :ref:`QuerySet.update()
  302. <topics-db-queries-update>`. Similarly, do :ref:`bulk deletes
  303. <topics-db-queries-delete>` where possible.
  304.  
  305. Note, however, that these bulk update methods cannot call the ``save()`` or
  306. ``delete()`` methods of individual instances, which means that any custom
  307. behavior you have added for these methods will not be executed, including
  308. anything driven from the normal database object :doc:`signals </ref/signals>`.
  309.  
  310. Use foreign key values directly
  311. -------------------------------
  312.  
  313. If you only need a foreign key value, use the foreign key value that is already on
  314. the object you've got, rather than getting the whole related object and taking
  315. its primary key. i.e. do::
  316.  
  317.    entry.blog_id
  318.  
  319. instead of::
  320.  
  321.    entry.blog.id
  322.  
  323. Don't order results if you don't care
  324. -------------------------------------
  325.  
  326. Ordering is not free; each field to order by is an operation the database must
  327. perform. If a model has a default ordering (:attr:`Meta.ordering
  328. <django.db.models.Options.ordering>`) and you don't need it, remove
  329. it on a ``QuerySet`` by calling
  330. :meth:`~django.db.models.query.QuerySet.order_by()` with no parameters.
  331.  
  332. Adding an index to your database may help to improve ordering performance.
  333.  
  334. Insert in bulk
  335. ==============
  336.  
  337. When creating objects, where possible, use the
  338. :meth:`~django.db.models.query.QuerySet.bulk_create()` method to reduce the
  339. number of SQL queries. For example::
  340.  
  341.     Entry.objects.bulk_create([
  342.         Entry(headline="Python 3.0 Released"),
  343.         Entry(headline="Python 3.1 Planned")
  344.     ])
  345.  
  346. ...is preferable to::
  347.  
  348.     Entry.objects.create(headline="Python 3.0 Released")
  349.     Entry.objects.create(headline="Python 3.1 Planned")
  350.  
  351. Note that there are a number of :meth:`caveats to this method
  352. <django.db.models.query.QuerySet.bulk_create>`, so make sure it's appropriate
  353. for your use case.
  354.  
  355. This also applies to :class:`ManyToManyFields
  356. <django.db.models.ManyToManyField>`, so doing::
  357.  
  358.     my_band.members.add(me, my_friend)
  359.  
  360. ...is preferable to::
  361.  
  362.     my_band.members.add(me)
  363.     my_band.members.add(my_friend)
  364.  
  365. ...where ``Bands`` and ``Artists`` have a many-to-many relationship.

Paste-bin is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.