514792 - Django creating too many queries

Status: RESOLVED WONTFIX

(Webtools Graveyard :: Cesium, defect)

Description

[Ryan Doherty (:rdoherty)]

http://grab.by/3dg

For the Site.score property, Django is creating tons of queries that should be merged into one gigantic join.

Something like this (haven't tested at all, probably completely wrong):
SELECT AVG(test.score), MAX(test.date_tested)
FROM users INNER JOIN users_sites ON users.id = users_sites.user_id 
INNER JOIN sites on users_sites.site_id = sites.id 
INNER JOIN users_pages ON users_pages.user_id 
INNER JOIN pages on users_pages.page_id = pages.id 
INNER JOIN tests ON test.page_id = pages.id 
WHERE users.id = N
GROUP BY tests.page_id

Comment 1

[Jeff Balogh (:jbalogh)]

Take your blind rage and place it elsewhere, sir:

In [1]: from django.db import connection

In [2]: from autoyslow.models import *

In [3]: Site.objects.get(id=1).score
Out[3]: 66

In [4]: connection.queries
Out[4]: 
[{'sql': u'SELECT `autoyslow_site`.`id`, `autoyslow_site`.`base_url`, `autoyslow_site`.`last_testrun` FROM `autoyslow_site` WHERE `autoyslow_site`.`id` = 1 ',
  'time': '0.080'},
 {'sql': u'SELECT AVG(`autoyslow_test`.`score`) AS `score__avg` FROM `autoyslow_test` WHERE `autoyslow_test`.`page_id` IN (SELECT U0.`id` FROM `autoyslow_page` U0 WHERE (U0.`site_id` = 1  AND U0.`last_testrun` >= 2009-08-30 22:43:59 ))',
  'time': '0.040'}]

In [1]: from autoyslow.models import *

In [2]: from django.db import connection

In [3]: Site.objects.for_user(2).get(id=1).score
Out[3]: 66

In [4]: connection.queries
Out[4]: 
[{'sql': u'SELECT `autoyslow_site`.`id`, `autoyslow_site`.`base_url`, `autoyslow_site`.`last_testrun` FROM `autoyslow_site` INNER JOIN `autoyslow_site_users` ON (`autoyslow_site`.`id` = `autoyslow_site_users`.`site_id`) WHERE (`autoyslow_site_users`.`user_id` = 2  AND `autoyslow_site`.`id` = 1 )',
  'time': '0.001'},
 {'sql': u'SELECT AVG(`autoyslow_test`.`score`) AS `score__avg` FROM `autoyslow_test` WHERE `autoyslow_test`.`page_id` IN (SELECT U0.`id` FROM `autoyslow_page` U0 INNER JOIN `autoyslow_page_users` U1 ON (U0.`id` = U1.`page_id`) WHERE (U1.`user_id` = 2  AND U0.`site_id` = 1  AND U0.`last_testrun` >= 2009-08-30 22:43:59 ))',
  'time': '0.004'}]

Comment 2

[Jeff Balogh (:jbalogh)]

(I agree it's a problem, we're just not looking in the right place.)

Comment 3

[Ryan Doherty (:rdoherty)]

Ok, I commented out format_detail_dict(site, request.user), looks like most queries are coming from there (down to 11 from 108 queries). I still see some weird queries that look related to averages though:

http://grab.by/3dx

Comment 4

[Jeff Balogh (:jbalogh)]

Cesium is dead.  RESOLVED => EXPIRED