So quite a while ago I wrote about avoiding the n+1 query problem with SQLObject. I’ve since been using Django a lot more. In particular at my day job I’ve been improving a Django site we’ve inherited. This particular site suffered from a few too many SQL queries and needed speeding up. Some of the issues related to the classic n+1 problem. i.e. one initial query triggering a further n queries (where n is the number of results in the first query).
A liberal dose of select_related helped. However that was only useful in the cases where a ForeignKey needed to be pre-fetched.
In the case however there was a page that was selecting a set of objects that had tags. The tags for each object were being displayed along side a link to the main object. Given that the initial query returned over three hundred objects, this meant the page was performing another three hundred (plus) queries to fetch the individual tags for each object! Now we could cache the page and that’s was indeed what was being done. The trouble however was when the cache expired. It also made things painful when developing – as I’d typically want to disable caching whilst I’m making changes to pages frequently.
I came up with a specific solution for this project – to perform the initial query, then a second query to fetch *all* of the other tags in one go. The results of the second query could then be stitched into the original results, to make them available in a handy manor within the page’s template.
>>> entries = Entry.objects.batch_select('tags').all() >>> entry = entries >>> print entry.tags_all [<Tag: Tag object>]
It’s a very early release – after the result of only a few hours coding, so use with care. It does have 100% test code coverage at the moment and I’m reasonable confident about it working. Please try it out and let me know whether it works for you.