[Django] #24702: Cache Query objects when possible
Django
----------------------------------------------+--------------------
Reporter: thedrow | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Currently Django constructs the Query objects every time you query the
database.
This is often unnecessary and can be avoided.
I started memoizing some of the Query objects that the manager creates but
I encountered some test failures related to prefetch_related querysets.
This is the memoized manager implementation
https://github.com/thedrow/django/blob/feature/memoized-
manager/django/db/models/manager.py#L244
I'm not sure it should be in a separate class but I created another one
because of some of the required API changes.
Can anyone explain the test failures?
--
Ticket URL: <https://code.djangoproject.com/ticket/24702>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: thedrow | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by thedrow):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
These are the test failures:
{{{
======================================================================
ERROR: gis_tests.geoadmin.tests (unittest.loader.ModuleImportFailure)
----------------------------------------------------------------------
ImportError: Failed to import test module: gis_tests.geoadmin.tests
Traceback (most recent call last):
File
"/home/omer/.pyenv/versions/2.7.9/lib/python2.7/unittest/loader.py", line
254, in _find_tests
module = self._get_module_from_name(name)
File
"/home/omer/.pyenv/versions/2.7.9/lib/python2.7/unittest/loader.py", line
232, in _get_module_from_name
__import__(name)
File
"/home/omer/Documents/Projects/django/tests/gis_tests/geoadmin/tests.py",
line 10, in <module>
from .admin import UnmodifiableAdmin
File
"/home/omer/Documents/Projects/django/tests/gis_tests/geoadmin/admin.py",
line 4, in <module>
class UnmodifiableAdmin(admin.OSMGeoAdmin):
AttributeError: 'module' object has no attribute 'OSMGeoAdmin'
======================================================================
FAIL: test_traverse_single_item_property
(prefetch_related.tests.CustomPrefetchTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File
"/home/omer/Documents/Projects/django/tests/prefetch_related/tests.py",
line 488, in test_traverse_single_item_property
[['primary_house', 'occupants', 'houses']]
File "/home/omer/Documents/Projects/django/django/test/testcases.py",
line 91, in __exit__
query['sql'] for query in self.captured_queries
AssertionError: 15 queries executed, 5 expected
Captured queries were:
QUERY = u'SELECT "prefetch_related_person"."id",
"prefetch_related_person"."name" FROM "prefetch_related_person" ORDER BY
"prefetch_related_person"."id" ASC' - PARAMS = ()
QUERY = u'SELECT ("prefetch_related_person_houses"."person_id") AS
"_prefetch_related_val_person_id", "prefetch_related_house"."id",
"prefetch_related_house"."name", "prefetch_related_house"."address",
"prefetch_related_house"."owner_id",
"prefetch_related_house"."main_room_id" FROM "prefetch_related_house"
INNER JOIN "prefetch_related_person_houses" ON (
"prefetch_related_house"."id" =
"prefetch_related_person_houses"."house_id" ) WHERE
"prefetch_related_person_houses"."person_id" IN (%s, %s) ORDER BY
"prefetch_related_house"."id" ASC' - PARAMS = (1, 2)
QUERY = u'SELECT "prefetch_related_room"."id",
"prefetch_related_room"."name", "prefetch_related_room"."house_id" FROM
"prefetch_related_room" WHERE "prefetch_related_room"."house_id" IN (%s,
%s, %s, %s) ORDER BY "prefetch_related_room"."id" ASC' - PARAMS = (1, 2,
3, 4)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 3)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 4)
QUERY = u'SELECT ("prefetch_related_person_houses"."house_id") AS
"_prefetch_related_val_house_id", "prefetch_related_person"."id",
"prefetch_related_person"."name" FROM "prefetch_related_person" INNER JOIN
"prefetch_related_person_houses" ON ( "prefetch_related_person"."id" =
"prefetch_related_person_houses"."person_id" ) WHERE
"prefetch_related_person_houses"."house_id" IN (%s, %s) ORDER BY
"prefetch_related_person"."id" ASC' - PARAMS = (1, 3)
QUERY = u'SELECT ("prefetch_related_person_houses"."person_id") AS
"_prefetch_related_val_person_id", "prefetch_related_house"."id",
"prefetch_related_house"."name", "prefetch_related_house"."address",
"prefetch_related_house"."owner_id",
"prefetch_related_house"."main_room_id" FROM "prefetch_related_house"
INNER JOIN "prefetch_related_person_houses" ON (
"prefetch_related_house"."id" =
"prefetch_related_person_houses"."house_id" ) WHERE
"prefetch_related_person_houses"."person_id" IN (%s, %s) ORDER BY
"prefetch_related_house"."id" ASC' - PARAMS = (1, 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 3)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 4)
======================================================================
FAIL: test_order (prefetch_related.tests.LookupOrderingTest)
----------------------------------------------------------------------
Traceback (most recent call last):
File
"/home/omer/Documents/Projects/django/tests/prefetch_related/tests.py",
line 922, in test_order
[list(p.primary_house.occupants.all()) for p in qs]
File "/home/omer/Documents/Projects/django/django/test/testcases.py",
line 91, in __exit__
query['sql'] for query in self.captured_queries
AssertionError: 14 queries executed, 4 expected
Captured queries were:
QUERY = u'SELECT "prefetch_related_person"."id",
"prefetch_related_person"."name" FROM "prefetch_related_person" ORDER BY
"prefetch_related_person"."id" ASC' - PARAMS = ()
QUERY = u'SELECT ("prefetch_related_person_houses"."person_id") AS
"_prefetch_related_val_person_id", "prefetch_related_house"."id",
"prefetch_related_house"."name", "prefetch_related_house"."address",
"prefetch_related_house"."owner_id",
"prefetch_related_house"."main_room_id" FROM "prefetch_related_house"
INNER JOIN "prefetch_related_person_houses" ON (
"prefetch_related_house"."id" =
"prefetch_related_person_houses"."house_id" ) WHERE
"prefetch_related_person_houses"."person_id" IN (%s, %s) ORDER BY
"prefetch_related_house"."id" ASC' - PARAMS = (1, 2)
QUERY = u'SELECT "prefetch_related_room"."id",
"prefetch_related_room"."name", "prefetch_related_room"."house_id" FROM
"prefetch_related_room" WHERE "prefetch_related_room"."house_id" IN (%s,
%s, %s, %s) ORDER BY "prefetch_related_room"."id" ASC' - PARAMS = (1, 2,
3, 4)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 3)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 4)
QUERY = u'SELECT ("prefetch_related_person_houses"."house_id") AS
"_prefetch_related_val_house_id", "prefetch_related_person"."id",
"prefetch_related_person"."name" FROM "prefetch_related_person" INNER JOIN
"prefetch_related_person_houses" ON ( "prefetch_related_person"."id" =
"prefetch_related_person_houses"."person_id" ) WHERE
"prefetch_related_person_houses"."house_id" IN (%s, %s) ORDER BY
"prefetch_related_person"."id" ASC' - PARAMS = (1, 3)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 3)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_room" WHERE
"prefetch_related_room"."house_id" = %s' - PARAMS = (u'*', 4)
======================================================================
FAIL: test_count (prefetch_related.tests.PrefetchRelatedTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File
"/home/omer/Documents/Projects/django/tests/prefetch_related/tests.py",
line 103, in test_count
[b.first_time_authors.count() for b in qs]
File "/home/omer/Documents/Projects/django/django/test/testcases.py",
line 91, in __exit__
query['sql'] for query in self.captured_queries
AssertionError: 6 queries executed, 2 expected
Captured queries were:
QUERY = u'SELECT "prefetch_related_book"."id",
"prefetch_related_book"."title" FROM "prefetch_related_book" ORDER BY
"prefetch_related_book"."id" ASC' - PARAMS = ()
QUERY = u'SELECT "prefetch_related_author"."id",
"prefetch_related_author"."name",
"prefetch_related_author"."first_book_id" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" IN (%s, %s, %s, %s) ORDER
BY "prefetch_related_author"."id" ASC' - PARAMS = (1, 2, 3, 4)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s' - PARAMS = (u'*', 1)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s' - PARAMS = (u'*', 2)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s' - PARAMS = (u'*', 3)
QUERY = u'SELECT COUNT(%s) AS "__count" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s' - PARAMS = (u'*', 4)
======================================================================
FAIL: test_exists (prefetch_related.tests.PrefetchRelatedTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File
"/home/omer/Documents/Projects/django/tests/prefetch_related/tests.py",
line 108, in test_exists
[b.first_time_authors.exists() for b in qs]
File "/home/omer/Documents/Projects/django/django/test/testcases.py",
line 91, in __exit__
query['sql'] for query in self.captured_queries
AssertionError: 6 queries executed, 2 expected
Captured queries were:
QUERY = u'SELECT "prefetch_related_book"."id",
"prefetch_related_book"."title" FROM "prefetch_related_book" ORDER BY
"prefetch_related_book"."id" ASC' - PARAMS = ()
QUERY = u'SELECT "prefetch_related_author"."id",
"prefetch_related_author"."name",
"prefetch_related_author"."first_book_id" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" IN (%s, %s, %s, %s) ORDER
BY "prefetch_related_author"."id" ASC' - PARAMS = (1, 2, 3, 4)
QUERY = u'SELECT (1) AS "a", "prefetch_related_author"."id",
"prefetch_related_author"."name",
"prefetch_related_author"."first_book_id" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s ORDER BY
"prefetch_related_author"."id" ASC' - PARAMS = (1,)
QUERY = u'SELECT (1) AS "a", "prefetch_related_author"."id",
"prefetch_related_author"."name",
"prefetch_related_author"."first_book_id" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s ORDER BY
"prefetch_related_author"."id" ASC' - PARAMS = (2,)
QUERY = u'SELECT (1) AS "a", "prefetch_related_author"."id",
"prefetch_related_author"."name",
"prefetch_related_author"."first_book_id" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s ORDER BY
"prefetch_related_author"."id" ASC' - PARAMS = (3,)
QUERY = u'SELECT (1) AS "a", "prefetch_related_author"."id",
"prefetch_related_author"."name",
"prefetch_related_author"."first_book_id" FROM "prefetch_related_author"
WHERE "prefetch_related_author"."first_book_id" = %s ORDER BY
"prefetch_related_author"."id" ASC' - PARAMS = (4,)
----------------------------------------------------------------------
Ran 9185 tests in 235.862s
FAILED (failures=4, errors=1, skipped=483, expected failures=8)
Destroying test database for alias 'default'...
Destroying test database for alias 'other'...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/24702#comment:1>
Django
Reporter: thedrow | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by shaib):
Haven't looked in the tests code, so I can only guess that the tests fail
because you're getting the memoized "all" query instead of one that should
be limited by a FK (for the prefetched query).
This hints at a flaw in your design: You can't memoize based only on the
call from the manager. You need to take account of how the manager was
constructed. Also, on first reading your code, I thought it odd that you'd
choose to put memoization on the manager class rather than the queryset
class.
--
Ticket URL: <https://code.djangoproject.com/ticket/24702#comment:2>
Django
Reporter: thedrow | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
* version: 1.8 => master
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/24702#comment:3>
Django
Reporter: thedrow | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by timgraham):
* status: new => closed
* resolution: => needsinfo
Comment:
I guess it's not so clear how to proceed here. @thedrow, feel free to
reopen if you return to this and get something working.
--
Ticket URL: <https://code.djangoproject.com/ticket/24702#comment:4>
Django
Reporter: thedrow | Owner: nobody
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by thedrow):
My approach doesn't work and I don't have an alternative.
I think this should go back to the drawing board.
Any thoughts on how this should be implemented?
--
Ticket URL: <https://code.djangoproject.com/ticket/24702#comment:5>