Number of sql queries very high when using menu templatetags #493

Closed
izimobil opened this Issue Jun 14, 2010 · 5 comments

Comments

Projects
None yet
3 participants
Contributor

izimobil commented Jun 14, 2010

I've recently ported a project from django 1.1/django-cms 2.0 to django 1.2/django-cms current.

It's a project with 95 cms pages and after the migration, the number of sql queries passed from 29 to 208 queries !.

Issue 1 (with possible fix)

I've investigated a bit and this commit alone is responsible for 94 queries: http://github.com/digi604/django-cms-2.0/commit/2fd49227d9a19b35c34aa13300f24087268e644c

In my case it generates 94 identical queries of the type:

SELECT `django_site`.`id`, `django_site`.`domain`, `django_site`.`name` FROM `django_site` WHERE `django_site`.`id` = 1 ORDER BY `django_site`.`domain` ASC

An easy fix for this is to add a select_related() to the PageQuerySet.on_site() method:
return self.filter(site=site).select_related()

Issue 2

But there's still 114 queries vs 29 before, I think this is due to the new menus template tags, th extra sql queries are of the type:

SELECT `cms_page`.`id`, `cms_page`.`publisher_is_draft`, `cms_page`.`publisher_public_id`,
`cms_page`.`publisher_state`, `cms_page`.`created_by`, `cms_page`.`changed_by`,
`cms_page`.`parent_id`, `cms_page`.`creation_date`, `cms_page`.`publication_date`,
`cms_page`.`publication_end_date`, `cms_page`.`in_navigation`, `cms_page`.`soft_root`,
`cms_page`.`reverse_id`, `cms_page`.`navigation_extenders`, `cms_page`.`published`,
`cms_page`.`template`, `cms_page`.`site_id`, `cms_page`.`moderator_state`, `cms_page`.`level`,
`cms_page`.`lft`, `cms_page`.`rght`, `cms_page`.`tree_id`, `cms_page`.`login_required`,
`cms_page`.`limit_visibility_in_menu` FROM `cms_page` WHERE (`cms_page`.`rght` > 22 AND
`cms_page`.`lft` < 21 AND `cms_page`.`tree_id` = 6 ) ORDER BY `cms_page`.`lft` DESC

The stacktrace for the query is:

34     _dec     /.../django-cms/cms/utils/__init__.py
123     show_menu_below_id     /.../django-cms/menus/templatetags/menu_tags.py
89     show_menu     /.../django-cms/menus/templatetags/menu_tags.py
122     get_nodes     /.../django-cms/menus/menu_pool.py
72     _build_nodes     /.../django-cms/menus/menu_pool.py
92     get_nodes     /.../django-cms/cms/menu.py
46     page_to_node     /.../django-cms/cms/menu.py
332     get_absolute_url     /.../django-cms/cms/models/pagemodel.py
344     get_cached_ancestors     /.../django-cms/cms/models/pagemodel.py

for each page...
Not sure how this can be optimized but it should be possible, since in 2.0 this was ok.

How to reproduce

Write a template that has a show_menu_below_id tag in it, activate the debug toolbar and look at the generated requests.

Collaborator

ojii commented May 20, 2011

Issue 1 was resolved

Collaborator

ojii commented May 20, 2011

Issue 2 might be a bit harder to fix

Contributor

stephrdev commented Jun 11, 2011

I dived into this problem a bit more and isolated the issue to this lines of code: https://github.com/divio/django-cms/blob/develop/cms/models/pagemodel.py#L554-558

The code looks for the ancestors and checks if the root node is the home page.

My idea is to denormalize the "root node is home" bit just like the title path (see https://github.com/divio/django-cms/blob/develop/cms/signals.py#L37-43). We could update the "root_page_is_home" on every page move.

What do you think about this approach?

Collaborator

ojii commented Jun 13, 2011

I had a similar idea (and was damn sure I wrote that down somewhere, but apparently it's not here....) so if you can come up with a solution that works (my naive approaches failed), go for it!

Contributor

stephrdev commented Jun 14, 2011

publisher got in my way. Looking for other approaches but my last idea did not work. Reason: "home" entry can change when a page gets published and not only when a page gets saved.

ojii closed this in ba38d2e Aug 2, 2011

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment