PageRenderTime 38ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 0ms

/docs/topics/db/optimization.txt

https://code.google.com/p/mango-py/
Plain Text | 270 lines | 192 code | 78 blank | 0 comment | 0 complexity | 2befd0934ba3e1bcf8d047e2aa837eac MD5 | raw file
Possible License(s): BSD-3-Clause
  1. ============================
  2. Database access optimization
  3. ============================
  4. Django's database layer provides various ways to help developers get the most
  5. out of their databases. This document gathers together links to the relevant
  6. documentation, and adds various tips, organized under a number of headings that
  7. outline the steps to take when attempting to optimize your database usage.
  8. Profile first
  9. =============
  10. As general programming practice, this goes without saying. Find out :ref:`what
  11. queries you are doing and what they are costing you
  12. <faq-see-raw-sql-queries>`. You may also want to use an external project like
  13. django-debug-toolbar_, or a tool that monitors your database directly.
  14. Remember that you may be optimizing for speed or memory or both, depending on
  15. your requirements. Sometimes optimizing for one will be detrimental to the
  16. other, but sometimes they will help each other. Also, work that is done by the
  17. database process might not have the same cost (to you) as the same amount of
  18. work done in your Python process. It is up to you to decide what your
  19. priorities are, where the balance must lie, and profile all of these as required
  20. since this will depend on your application and server.
  21. With everything that follows, remember to profile after every change to ensure
  22. that the change is a benefit, and a big enough benefit given the decrease in
  23. readability of your code. **All** of the suggestions below come with the caveat
  24. that in your circumstances the general principle might not apply, or might even
  25. be reversed.
  26. .. _django-debug-toolbar: http://robhudson.github.com/django-debug-toolbar/
  27. Use standard DB optimization techniques
  28. =======================================
  29. ...including:
  30. * Indexes. This is a number one priority, *after* you have determined from
  31. profiling what indexes should be added. Use
  32. :attr:`django.db.models.Field.db_index` to add these from Django.
  33. * Appropriate use of field types.
  34. We will assume you have done the obvious things above. The rest of this document
  35. focuses on how to use Django in such a way that you are not doing unnecessary
  36. work. This document also does not address other optimization techniques that
  37. apply to all expensive operations, such as :doc:`general purpose caching
  38. </topics/cache>`.
  39. Understand QuerySets
  40. ====================
  41. Understanding :doc:`QuerySets </ref/models/querysets>` is vital to getting good
  42. performance with simple code. In particular:
  43. Understand QuerySet evaluation
  44. ------------------------------
  45. To avoid performance problems, it is important to understand:
  46. * that :ref:`QuerySets are lazy <querysets-are-lazy>`.
  47. * when :ref:`they are evaluated <when-querysets-are-evaluated>`.
  48. * how :ref:`the data is held in memory <caching-and-querysets>`.
  49. Understand cached attributes
  50. ----------------------------
  51. As well as caching of the whole ``QuerySet``, there is caching of the result of
  52. attributes on ORM objects. In general, attributes that are not callable will be
  53. cached. For example, assuming the :ref:`example Weblog models
  54. <queryset-model-example>`::
  55. >>> entry = Entry.objects.get(id=1)
  56. >>> entry.blog # Blog object is retrieved at this point
  57. >>> entry.blog # cached version, no DB access
  58. But in general, callable attributes cause DB lookups every time::
  59. >>> entry = Entry.objects.get(id=1)
  60. >>> entry.authors.all() # query performed
  61. >>> entry.authors.all() # query performed again
  62. Be careful when reading template code - the template system does not allow use
  63. of parentheses, but will call callables automatically, hiding the above
  64. distinction.
  65. Be careful with your own custom properties - it is up to you to implement
  66. caching.
  67. Use the ``with`` template tag
  68. -----------------------------
  69. To make use of the caching behavior of ``QuerySet``, you may need to use the
  70. :ttag:`with` template tag.
  71. Use ``iterator()``
  72. ------------------
  73. When you have a lot of objects, the caching behavior of the ``QuerySet`` can
  74. cause a large amount of memory to be used. In this case,
  75. :meth:`~django.db.models.QuerySet.iterator()` may help.
  76. Do database work in the database rather than in Python
  77. ======================================================
  78. For instance:
  79. * At the most basic level, use :ref:`filter and exclude <queryset-api>` to do
  80. filtering in the database.
  81. * Use :ref:`F() object query expressions <query-expressions>` to do filtering
  82. against other fields within the same model.
  83. * Use :doc:`annotate to do aggregation in the database </topics/db/aggregation>`.
  84. If these aren't enough to generate the SQL you need:
  85. Use ``QuerySet.extra()``
  86. ------------------------
  87. A less portable but more powerful method is
  88. :meth:`~django.db.models.QuerySet.extra()`, which allows some SQL to be
  89. explicitly added to the query. If that still isn't powerful enough:
  90. Use raw SQL
  91. -----------
  92. Write your own :doc:`custom SQL to retrieve data or populate models
  93. </topics/db/sql>`. Use ``django.db.connection.queries`` to find out what Django
  94. is writing for you and start from there.
  95. Retrieve everything at once if you know you will need it
  96. ========================================================
  97. Hitting the database multiple times for different parts of a single 'set' of
  98. data that you will need all parts of is, in general, less efficient than
  99. retrieving it all in one query. This is particularly important if you have a
  100. query that is executed in a loop, and could therefore end up doing many database
  101. queries, when only one was needed. So:
  102. Use ``QuerySet.select_related()``
  103. ---------------------------------
  104. Understand :ref:`QuerySet.select_related() <select-related>` thoroughly, and use it:
  105. * in view code,
  106. * and in :doc:`managers and default managers </topics/db/managers>` where
  107. appropriate. Be aware when your manager is and is not used; sometimes this is
  108. tricky so don't make assumptions.
  109. Don't retrieve things you don't need
  110. ====================================
  111. Use ``QuerySet.values()`` and ``values_list()``
  112. -----------------------------------------------
  113. When you just want a ``dict`` or ``list`` of values, and don't need ORM model
  114. objects, make appropriate usage of :meth:`~django.db.models.QuerySet.values()`.
  115. These can be useful for replacing model objects in template code - as long as
  116. the dicts you supply have the same attributes as those used in the template,
  117. you are fine.
  118. Use ``QuerySet.defer()`` and ``only()``
  119. ---------------------------------------
  120. Use :meth:`~django.db.models.QuerySet.defer()` and
  121. :meth:`~django.db.models.QuerySet.only()` if there are database columns you
  122. know that you won't need (or won't need in most cases) to avoid loading
  123. them. Note that if you *do* use them, the ORM will have to go and get them in
  124. a separate query, making this a pessimization if you use it inappropriately.
  125. Also, be aware that there is some (small extra) overhead incurred inside
  126. Django when constructing a model with deferred fields. Don't be too aggressive
  127. in deferring fields without profiling as the database has to read most of the
  128. non-text, non-VARCHAR data from the disk for a single row in the results, even
  129. if it ends up only using a few columns. The ``defer()`` and ``only()`` methods
  130. are most useful when you can avoid loading a lot of text data or for fields
  131. that might take a lot of processing to convert back to Python. As always,
  132. profile first, then optimize.
  133. Use QuerySet.count()
  134. --------------------
  135. ...if you only want the count, rather than doing ``len(queryset)``.
  136. Use QuerySet.exists()
  137. ---------------------
  138. ...if you only want to find out if at least one result exists, rather than ``if
  139. queryset``.
  140. But:
  141. Don't overuse ``count()`` and ``exists()``
  142. ------------------------------------------
  143. If you are going to need other data from the QuerySet, just evaluate it.
  144. For example, assuming an Email model that has a ``body`` attribute and a
  145. many-to-many relation to User, the following template code is optimal:
  146. .. code-block:: html+django
  147. {% if display_inbox %}
  148. {% with emails=user.emails.all %}
  149. {% if emails %}
  150. <p>You have {{ emails|length }} email(s)</p>
  151. {% for email in emails %}
  152. <p>{{ email.body }}</p>
  153. {% endfor %}
  154. {% else %}
  155. <p>No messages today.</p>
  156. {% endif %}
  157. {% endwith %}
  158. {% endif %}
  159. It is optimal because:
  160. 1. Since QuerySets are lazy, this does no database queries if 'display_inbox'
  161. is False.
  162. #. Use of ``with`` means that we store ``user.emails.all`` in a variable for
  163. later use, allowing its cache to be re-used.
  164. #. The line ``{% if emails %}`` causes ``QuerySet.__nonzero__()`` to be called,
  165. which causes the ``user.emails.all()`` query to be run on the database, and
  166. at the least the first line to be turned into an ORM object. If there aren't
  167. any results, it will return False, otherwise True.
  168. #. The use of ``{{ emails|length }}`` calls ``QuerySet.__len__()``, filling
  169. out the rest of the cache without doing another query.
  170. #. The ``for`` loop iterates over the already filled cache.
  171. In total, this code does either one or zero database queries. The only
  172. deliberate optimization performed is the use of the ``with`` tag. Using
  173. ``QuerySet.exists()`` or ``QuerySet.count()`` at any point would cause
  174. additional queries.
  175. Use ``QuerySet.update()`` and ``delete()``
  176. ------------------------------------------
  177. Rather than retrieve a load of objects, set some values, and save them
  178. individual, use a bulk SQL UPDATE statement, via :ref:`QuerySet.update()
  179. <topics-db-queries-update>`. Similarly, do :ref:`bulk deletes
  180. <topics-db-queries-delete>` where possible.
  181. Note, however, that these bulk update methods cannot call the ``save()`` or
  182. ``delete()`` methods of individual instances, which means that any custom
  183. behavior you have added for these methods will not be executed, including
  184. anything driven from the normal database object :doc:`signals </ref/signals>`.
  185. Use foreign key values directly
  186. -------------------------------
  187. If you only need a foreign key value, use the foreign key value that is already on
  188. the object you've got, rather than getting the whole related object and taking
  189. its primary key. i.e. do::
  190. entry.blog_id
  191. instead of::
  192. entry.blog.id