Django Batch Select

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.

I took the original idea and made it re-usable and am now releasing that code as Django Batch Select. There are examples of how to use it over at github, but it works a bit like this:


>>> entries = Entry.objects.batch_select('tags').all()
>>> entry = entries[0]
>>> 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.

2 thoughts on “Django Batch Select

  1. Useful thing. Great thanks. It will be first code I’ll check tomorrow. Till now I have used manually written sub-queries with extra() method (just one query) but this way is really painful and unmaintainable (eg. adding new column with date type).

    Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>