Hi guys,
I'm spending some time at the moment trying to optimize parts of my code because I noticed it was becoming slow. This mostly means reducing the amount of queries, such as by using select_related().
When I was looking through my queries with Django Debug Toolbar on a development setup I made, I noticed FeinCMS does some big UNION queries for rendering Page regions. These queries take up to 3ms locally, which is certainly a bit on the heavy side. I noticed, however, that some parts which are being UNION'ed aren't really needed, because those content types can't exist on those pages.
Let me explain by showing my pages, content types and the resulting queries:
MagazinePage.register_templates(
{
'key': 'cover',
'title': _('Omslagpagina'),
'path': 'magazines/pages/cover.html',
'regions': (
('cover_slides', _('Dia\'s')),
('cover_blocks', _('Blokken')),
),
},
{
'key': 'contents',
'title': _('Inhoudspagina'),
'path': 'magazines/pages/contents.html',
'regions': (
('contents', _('Inhoud')),
),
},
{
'key': 'genre',
'title': _('Genrepagina'),
'path': 'magazines/pages/genre.html',
'regions': (
('genre', _('Inhoud')),
),
},
{
'key': 'performance',
'title': _('Voorstellingspagina'),
'path': 'magazines/pages/performance.html',
'regions': (
('performance_cover', _('Omslag')),
('performance_content', _('Inhoud')),
),
}
)
MagazinePage.create_content_type(CoverHeaderSlide, regions=('cover_slides',))
MagazinePage.create_content_type(
PerformanceBlock, regions=('cover_blocks', 'genre',))
MagazinePage.create_content_type(
GenreHeaderBlock, regions=('genre',))
MagazinePage.create_content_type(
ContentsChapterBlock, regions=('contents',))
MagazinePage.create_content_type(
PerformanceCoverTextBlock, regions=('performance_cover',))
MagazinePage.create_content_type(
PerformanceCoverTitleBlock, regions=('performance_cover',))
MagazinePage.create_content_type(
PerformanceCoverButtonBlock, regions=('performance_cover',))
MagazinePage.create_content_type(
PerformanceCoverReviewBlock, regions=('performance_cover',))
MagazinePage.create_content_type(
PerformanceContent, regions=('performance_content',))As you can see, there are four different types of pages, which combined have six different types of regions. Further more, there are nine different content types, most of which are only allowed in one region.
For my development setup, I have created four different kind of pages, with id's 1 through 4 (in the same order as they are registered, which is a nice coincidence). I render all those pages and their regions at once, resulting in the following four queries:
SELECT * FROM ( SELECT 0 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_coverheaderslide WHERE parent_id=1 GROUP BY region UNION SELECT 1 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performanceblock WHERE parent_id=1 GROUP BY region UNION SELECT 2 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_genreheaderblock WHERE parent_id=1 GROUP BY region UNION SELECT 3 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_contentschapterblock WHERE parent_id=1 GROUP BY region UNION SELECT 4 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertextblock WHERE parent_id=1 GROUP BY region UNION SELECT 5 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertitleblock WHERE parent_id=1 GROUP BY region UNION SELECT 6 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverbuttonblock WHERE parent_id=1 GROUP BY region UNION SELECT 7 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverreviewblock WHERE parent_id=1 GROUP BY region UNION SELECT 8 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecontent WHERE parent_id=1 GROUP BY region ) AS ct ORDER BY ct_idx
SELECT * FROM ( SELECT 0 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_coverheaderslide WHERE parent_id=2 GROUP BY region UNION SELECT 1 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performanceblock WHERE parent_id=2 GROUP BY region UNION SELECT 2 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_genreheaderblock WHERE parent_id=2 GROUP BY region UNION SELECT 3 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_contentschapterblock WHERE parent_id=2 GROUP BY region UNION SELECT 4 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertextblock WHERE parent_id=2 GROUP BY region UNION SELECT 5 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertitleblock WHERE parent_id=2 GROUP BY region UNION SELECT 6 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverbuttonblock WHERE parent_id=2 GROUP BY region UNION SELECT 7 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverreviewblock WHERE parent_id=2 GROUP BY region UNION SELECT 8 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecontent WHERE parent_id=2 GROUP BY region ) AS ct ORDER BY ct_idx
SELECT * FROM ( SELECT 0 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_coverheaderslide WHERE parent_id=3 GROUP BY region UNION SELECT 1 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performanceblock WHERE parent_id=3 GROUP BY region UNION SELECT 2 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_genreheaderblock WHERE parent_id=3 GROUP BY region UNION SELECT 3 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_contentschapterblock WHERE parent_id=3 GROUP BY region UNION SELECT 4 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertextblock WHERE parent_id=3 GROUP BY region UNION SELECT 5 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertitleblock WHERE parent_id=3 GROUP BY region UNION SELECT 6 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverbuttonblock WHERE parent_id=3 GROUP BY region UNION SELECT 7 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverreviewblock WHERE parent_id=3 GROUP BY region UNION SELECT 8 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecontent WHERE parent_id=3 GROUP BY region ) AS ct ORDER BY ct_idx
SELECT * FROM ( SELECT 0 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_coverheaderslide WHERE parent_id=4 GROUP BY region UNION SELECT 1 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performanceblock WHERE parent_id=4 GROUP BY region UNION SELECT 2 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_genreheaderblock WHERE parent_id=4 GROUP BY region UNION SELECT 3 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_contentschapterblock WHERE parent_id=4 GROUP BY region UNION SELECT 4 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertextblock WHERE parent_id=4 GROUP BY region UNION SELECT 5 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecovertitleblock WHERE parent_id=4 GROUP BY region UNION SELECT 6 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverbuttonblock WHERE parent_id=4 GROUP BY region UNION SELECT 7 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecoverreviewblock WHERE parent_id=4 GROUP BY region UNION SELECT 8 AS ct_idx, region, COUNT(id) FROM magazines_magazinepage_performancecontent WHERE parent_id=4 GROUP BY region ) AS ct ORDER BY ct_idx
As you can see, it does a UNION SELECT for every table created by registered all nine content types. In my opinion, this does not make sense. Take the page with id 1 for example (first query). It is a cover page, which has the regions cover_slides and cover_blocks registered. There are a total of two content types registered on both those regions combined: CoverHeaderSlide and PerformanceBlock. In that light, it doesn't make sense to do a SELECT from all nine tables; it should only look up the content types which are allowed on the regions this page has. I think that the speed of those queries will benefit from having to do less UNIONs. I understand it's probably rare case because I have more content types than regions and they are all only registered to one or two regions, but still I think it's silly to look up all content type tables for every page.
Hi guys,
I'm spending some time at the moment trying to optimize parts of my code because I noticed it was becoming slow. This mostly means reducing the amount of queries, such as by using
select_related().When I was looking through my queries with Django Debug Toolbar on a development setup I made, I noticed FeinCMS does some big UNION queries for rendering Page regions. These queries take up to 3ms locally, which is certainly a bit on the heavy side. I noticed, however, that some parts which are being UNION'ed aren't really needed, because those content types can't exist on those pages.
Let me explain by showing my pages, content types and the resulting queries:
As you can see, there are four different types of pages, which combined have six different types of regions. Further more, there are nine different content types, most of which are only allowed in one region.
For my development setup, I have created four different kind of pages, with id's 1 through 4 (in the same order as they are registered, which is a nice coincidence). I render all those pages and their regions at once, resulting in the following four queries:
As you can see, it does a UNION SELECT for every table created by registered all nine content types. In my opinion, this does not make sense. Take the page with id 1 for example (first query). It is a
coverpage, which has the regionscover_slidesandcover_blocksregistered. There are a total of two content types registered on both those regions combined:CoverHeaderSlideandPerformanceBlock. In that light, it doesn't make sense to do a SELECT from all nine tables; it should only look up the content types which are allowed on the regions this page has. I think that the speed of those queries will benefit from having to do less UNIONs. I understand it's probably rare case because I have more content types than regions and they are all only registered to one or two regions, but still I think it's silly to look up all content type tables for every page.