CODEWITHSUNDEEP
djangopostgresqltastypie
user1738833
user1738833

Reputation: 339

Number of SQL queries for a django update

I have a django application that I have just inherited, knowing very little (generally) about django. The system uses TastyPie to provide RESTful access.

The feature I'm working on needs to be able to POST a new report to the system. The reports in the ORM model are associated with multiple "devices". In the ORM the devices have further relationships, such as to users, companies, other sub-devices and so forth, in a complex relational system.

When I try to POST the report, I frequently DoS myself out of the system. Watching PostGreSQL queries on the PostGreSQL logs, I can see that this performs literally thousands of SQL queries, retrieving all the objects in the relational model. However, ultimately, all it needs to do is to add a new entry in the "report" table and maybe a handful of entries in the "report_device" table (as report to device is a many-to-many relationship).

In the reference to the device in the (TastyPie) resource (called ReportResource), I don't reference the device with "full=True".

  1. Why is the system performing so many database queries when it needs only to update two tables?
  2. How do I stop it doing this and provide a more optimised update mechanism?

I'm an accomplished SQL developer myself, but I don't want to throw out the baby with the bathwater here by writing a custom update (and I wouldn't know how to insert the relevant code anyway). I assume there's a way to make django / tastypie do what I want in a sensible way.

I can provide more information, but I don't know what's pertinent. Please ask if you think you know something and I'll see if I can elucidate.

Upvotes: 0

Views: 82

Answers (1)

johntellsall
johntellsall

Reputation: 15170

  1. TastyPie tends to be liberal in its queries. I remember using a good bit of custom dehydrate() functions to get what I wanted. http://django-tastypie.readthedocs.org/en/latest/resources.html?highlight=hydrate#Resource.dehydrate

  2. TastyPie doesn't like table references -- it's too easy to get too much information. Cast a suspicious eyeball on code like user = fields.ForeignKey(UserResource, 'user')

  3. for your own app code, there's a way to ask the Django QuerySet machinery to translate a query into SQL. This, combined with your Postgres logs, should help determine if the issues are with TastyPie or your app queries.

Code:

#!/usr/bin/env python

'''
logquery.py -- expose database queries (SQL)
'''

import functools, os, sys

os.environ['DJANGO_SETTINGS_MODULE'] = 'project.settings.local'
sys.path.append('project/project')

from meetup.models import Meeting

def output(arg):
    print arg
    print


class LoggingObj(object):
    def __init__(self, other):
        self.other = other

    def log_call(self, ofunc, *args, **kwargs):
        res = ofunc(*args, **kwargs)
        print 'CALL:',ofunc.__name__,args,kwargs
        print '=>',res
        return res


    def __getattr__(self, key):
        ofunc = getattr(self.other, key)
        if not callable(ofunc):
            return ofunc
        return functools.partial(self.log_call, ofunc)


qs = Meeting.objects.all()
output( qs.query )


qs = Meeting.objects.all()
qs.query = LoggingObj(qs.query)
output( qs.query.sql_with_params() )

output( list(qs) )

Partial output, with SQL:

SELECT "meetup_meeting"."id", "meetup_meeting"."name", "meetup_meeting"."meet_date" FROM "meetup_meeting"

CALL: sql_with_params () {} => (u'SELECT "meetup_meeting"."id", "meetup_meeting"."name", "meetup_meeting"."meet_date" FROM "meetup_meeting"', ()) (u'SELECT "meetup_meeting"."id", "meetup_meeting"."name", "meetup_meeting"."meet_date" FROM "meetup_meeting"', ())

Upvotes: 1

Related Questions