Monday, November 9, 2009

When you play outside of django's ORM ... you get pain!

For one of my projects, I forgot to implement the relationship of one my models. I thought I could get away with it by writing a tag that made a query to the db upon being fed a string in the report. To my horror, this caused the report to take about 50seconds to generate for a paltry 3000 record db! Upon tailing the logs of query.log, I found that the multitude of query to the db was causing the problem. It was basically hitting the db about 3000+ times for a single page report. That is when I found out the good thing about Django's select_related(). The problem now is, I do not have that relationship and need to build that relationship into the db. Not exactly fun with a db that is already populated with data. Led me to thinking, while Django's ORM might be great for stuff like calling data from another table, but would it also incur a performance hit everytime data is being queried. I have heard Django being used for high traffic sites but then are these high traffic sites?

I tried looking around but I could not find a satisfactory answer to this: "What if you wanted to model data for an existing MyISAM table which does not have the relationship?" It would be nice if Django provided a mechanism to recreate the relationship between MyISAM tables that is implemented at the MiddleWare. Say if I were to go the Django way now, would I have to dump out the data, re-implement the tables in a 'Django-ic' model then reimport back my data?

Currently I am feeling that in order to play in Django ORM's park. I am being forced to play by it's rules. If I opt to write my sqls raw then the ORM takes back it's ball and refuses to let me even venture into it's side of the park and I am left to recreate back a lot of the convenient functionality that was provided by the ORM layer. There doesn't seem to be a nice middle ground and that just sucks.

3 comments:

Anonymous said...

You'll be pleased to hear that a fix for this kind of thing is a high profile intended feature for Django 1.2:

http://code.djangoproject.com/ticket/11863

mlouro said...

Sounds like you might be looking for something like south for schema migration -> south.aeracode.org

sorl said...

If I understand this correctly, why don't you just change the IntegerField to a ForeignKey? if the columns don't match you can specify the db_column keyword argument. Regarding select_related which is just JOIN, Django doesn't get in the way of somebody trying to kill the database. If you were coding a high traffic site chanses are you know SQL and you are aware of potential problems, these still apply using the ORM.