Optimizing django db queries

Question

I am trying to optimize my db queries(mysql) in a django app.

This is the situation:

I need to retrieve some data about sales, stock about some products on a monthly basis. This is the function

def get_magazzino_month(year, month):
    from magazzino.models import ddt_in_item, omaggi_item, inventario_item
    from corrispettivi.models import corrispettivi_item, corrispettivi
    from fatture.models import fatture_item, fatture, fatture_laboratori_item
    from prodotti.models import prodotti
    qt = 0
    val = 0

    products = prodotti.objects.all()
    invents = inventario_item.objects.all().filter(id_inventario__data__year=year-1)
    fatture_lab = fatture_laboratori_item.objects.all().order_by("-id_fattura__data")

    for product in products:
        inv_instance = filter_for_product(invents, product)
        if inv_instance:
            qt += inv_instance[0].quantita
        lab_instance = fatture_lab.filter(id_prodotti=product).first()
        prezzo_prodotto = (lab_instance.costo_acquisto/lab_instance.quantita - ((lab_instance.costo_acquisto/lab_instance.quantita) * lab_instance.sconto / 100)) if lab_instance else product.costo_acquisto
    return val, qt

The problem is where I need to filter all the data to get only the product I need. It seems that the .filter option makes django requery the database, although all of the data is there. I tried making a function to filter it myself, but although the queries diminish, loading time increases dramatically.

This is the function to filter:

def filter_for_product(array, product):
    result = []
    for instance in array:
        if instance.id_prodotti.id == product.id:
            result.append(instance)
    return result

Has anyone ever dealt with this kind of problem?

I am having a hard time understanding your problem. But for invents=... and fatture_lab=... which appears just before your loop, you can remove .all() from both of them. just use objects.filter(...) and objects.order_by(..) — Vaibhav Vishal
– Vaibhav Vishal, Commented Oct 25, 2018 at 12:38
the problem is that my data takes too long being processed, and I think it comes from the lot of queries being done. I modified invents and fatture_lab, thanks. — MGassend
– MGassend, Commented Oct 25, 2018 at 12:55

bdoubleu · Accepted Answer · 2018-10-25 12:56:52Z

1

You can use prefetch_related() to return a queryset of related objects and Prefetch() to further control the operation.

from django.db.models import Prefetch

products = prodotti.objects.all().annotate(
    Prefetch(
        'product_set',
        queryset=inventario_item.objects.all().filter(id_inventario__data__year=year-1),
        to_attr='invent'
    )
)

Then you can access each product's invent like products[0].invent

answered Oct 25, 2018 at 12:56

bdoubleu

6,1473 gold badges29 silver badges57 bronze badges

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

Comments

John · Accepted Answer · 2018-10-25 12:37:51Z

1

Using select_related() will help optimize your queries

A good example of what select_related() does and how to use it is available at simpleisbetterthancomplex.

answered Oct 25, 2018 at 12:37

John

364 bronze badges

Collectives™ on Stack Overflow

Optimizing django db queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related