optimizing this django code?

Question

Hi,

I'm having some performance issues because I'm making a lot of query calls that I'm not sure how to reduce.

user_item_rel_set is a m2m relation between user and items showing how much a user paid for a particular item. Each item can have multiple users and buyers, and I'm trying to get the m2m relation for a particular user.

        # find anything that you bought or used and how much you paid for it
        u = User.objects.get(id=self.uid)
        t = self.list.filter(user_item_rel__user__exact=u)
        y = self.list.filter(buyer_item_rel__buyer__exact=u)

        items = t | y 
        items = items.distinct()
        u = User.objects.get(id=self.uid)
        for t in items:
            try: 
                t.price = t.user_item_rel_set.get(user=u).payment_amount
            except:
                t.price = -1 * t.buyer_item_rel_set.get(buyer=u).payment_amount
        return items

and at another instance

    for i in new_list:
        if str(i.tag) not in x:
            x[str(i.tag)] = 0 

        if houseMode == 0:
            x[str(i.tag)] += float(i.user_item_rel_set.get(user__id__exact=self.uid).payment_amount)
        else:
            x[str(i.tag)] += float(i.price)

Answer 1

Some additional code from your model would help, because it's hard to see what the 'items' queryset contains.

I will try to help anyway...

Because you've modeled a relationship between users and items, there is no need to iterate over every item in that queryset when you can simply select the subset that are interesting to you.

Again, I'm having a bit of difficulty following your application logic, but I think your queries can be reduced to something of this nature:

# Find all the items where this user is the "user"
user_items = items.filter(user_item_rel_set__user=u)

# Find all the items where this user is the "buyer"
buyer_items = items.filter(user_item_rel_set__buyer=u)

I don't quite follow why you are assigning these values to 't.price' in the loop or I would expand on that code.

If that doesn't help your performance, I recommend dumping your SQL queries to the console so you can see exactly what's going on behind the ORM. In logic like this, it shouldn't take more than a handful of SQL statements to arrive at your calculation.

Furthermore, it is generally a bad idea to use floating point datatypes (float) anywhere in proximity to a monetary value. Floating point datatypes are generally for scientific applications where performance is more important than precision. If you're dealing with money, precision is almost always more important than performance, so you use a datatype capable of exact representation like decimal.Decimal everywhere.

Edit

Given the comments, I recommend starting your query with the "relationship" object instead of the Item. Since your sample doesn't tell me the name of that class, I will assume it's called UserItem:

from django.db.models import Q
from decimal import Decimal

price = Decimal('0')

# Get all UserItems where this user is the user or buyer
interesting_items = UserItem.objects.filter((Q(user=u) | Q(buyer=u)))
for ii in interesting_items:
    if ii.user == u:
        price += ii.payment_amount
    elif ii.buyer == u:
        price -= ii.payment_amount
    else:
        assert False, "Oops, this shouldn't happen"

# Do something with 'price'...

The Django "Q" facility lets you get a little more granular with your queries. If you need to filter based on some attribute of the item, throw that in there too.

The part that still confuses me in your examples, is why are you assigning 'price' to the item object when it is clear that many users will share that item.

Edit 2

You can also use the aggregation API to let the DBMS compute the sum if that's all you're interested in:

from django.db.models import Sum
buyer_price = UserItem.objects.filter(item=i, user=u).aggregate(
                 Sum('payment_amount'))['payment_amount__sum']