Search code examples
pythonmysqldjangooverhead

Overhead of a Round-trip to MySql?

So I've been building django applications for a while now, and drinking the cool-aid and all: only using the ORM and never writing custom SQL.

The main page of the site (the primary interface where users will spend 80% - 90% of their time) was getting slow once you have a large amount of user specific content (ie photos, friends, other data, etc)

So I popped in the sql logger (was pre-installed with pinax, I just enabled it in the settings) and imagine my surprise when it reported over 500 database queries!! With hand coded sql I hardly ever ran more than 50 on the most complex pages.

In hindsight it's not all together surprising, but it seems that this can't be good.

...even if only a dozen or so of the queries take 1ms+

So I'm wondering, how much overhead is there on a round trip to mysql? django and mysql are running on the same server so there shouldn't be any networking related overhead.

Solution

  • There are some ways to reduce the query volume.

    1. Use .filter() and .all() to get a bunch of things; pick and choose in the view function (or template via {%if%}). Python can process a batch of rows faster than MySQL.

      "But I could send too much to the template". True, but you'll execute fewer SQL requests. Measure to see which is better.

      This is what you used to do when you wrote SQL. It's not wrong -- it doesn't break the ORM -- but it optimizes the underlying DB work and puts the processing into the view function and the template.

    2. Avoid query navigation in the template. When you do {{foo.bar.baz.quux}}, SQL is used to get the bar associated with foo, then the baz associated with the bar, then the quux associated with baz. You may be able to reduce this query business with some careful .filter() and Python processing to assemble a useful tuple in the view function.

      Again, this was something you used to do when you hand-crafted SQL. In this case, you gather larger batches of ORM-managed objects in the view function and do your filtering in Python instead of via a lot of individual ORM requests.

      This doesn't break the ORM. It changes the usage profile from lots of little queries to a few bigger queries.