Reducing database access when same query on multiple similar objects

Question

I have an operation in one of my views

order_details = [order.get_order_details() for order in orders]

Now order.get_order_details() runs one database query. So for current situation. Depending on size of orders the number of database access will be huge.

Before having to use cache, is there anything that can speed this up?

Is it possible to merge all the select operations into one single database operation?

Will making it an atomic transaction using transaction.atomic() increase any performance? because technically the query will be sent at once instead of individually, right?

Edit: is there any design changes/ pattern that will avoid this situation?

Edit:

def get_order_details(self):
    items = Item.objects.filter(order=self)
    item_list = [item.serialize for item in items]
    return {
        'order_details': self.serialize,
        'item_list': item_list
    }

You want select_related or perhaps prefetch_related (just below) — Alex Hall
– Alex Hall, Commented Jul 2, 2017 at 21:58
Oh, then we're going to need to see what get_order_details looks like. My only other guess is to use the __in operator. I can't do better than guess with so little information. — Alex Hall
– Alex Hall, Commented Jul 2, 2017 at 22:02
I am sorry I had misunderstood you previous comment. I thought you said about using select_related on order. I think it can be solved by it. Thank you:) Please put it as an answer so I can accept it. — Sidhin S Thomas
– Sidhin S Thomas, Commented Jul 2, 2017 at 22:06
Transactions will not send all queries at once. You still perform individual queries. Now, transactions do have some performance benefits, but not nearly as much as the benefits of performing just a single database roundtrip. — knbk
– knbk, Commented Jul 2, 2017 at 22:29

Alex Hall · Accepted Answer · 2017-07-02 22:30:45Z

1

Assuming orders is a QuerySet, e.g. the result of Order.objects.filter(...), add:

.prefetch_related(Prefetch('item_set'))

to the end of the query. Then use:

items = self.item_set

in get_order_details.

See the docs here: https://docs.djangoproject.com/en/1.11/ref/models/querysets/#prefetch-related

edited Jul 2, 2017 at 22:30

answered Jul 2, 2017 at 22:27

Alex Hall

36.2k5 gold badges63 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sidhin S Thomas Over a year ago

Item is a Model with Foreign Key to Order. Will this still work?

Alex Hall Over a year ago

@SidhinThomas that is what I assumed, so I think so. By the way I made a typo in case you already copied code, it's self.item_set not self.items_set.

Sidhin S Thomas Over a year ago

It's much more elegant solution that to use select_related on Item and retrieving the details which I just implemented. Thank you

Collectives™ on Stack Overflow

Reducing database access when same query on multiple similar objects

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related