Should i do math in Django ORM or in Python
Question: I work with Django and have big database with around 30.000 records. My job is
to do some simple math with all of the records. Data in the database is
related to 50 persons. I have to filter rows for each person and then to
calculate the average value for each of the 10 columns. So i would have to
filter the data for 50 times (because i have to calculate and present data for
each of the 50 persons separately) and to do the math. I have done this task
with just one line of code (that is looped over 50 times):

    
    
    qs = Mymodel.objects.filter(person=person).values()
    records[i] = qs.aggregate(Avg('question-1'),Avg('question-2'),...Avg('question-10'))
    # Heavy SQL operation
    

but django debug toolbar says it takes around 6.6 seconds for this request to
be done (with around 55 sql queries, which i expected).

This is only one task and i will have to do additional 7-8 similar
mathematical tasks, so the whole job could easily sum up to 400 - 500 sql
queries.

I am concerned about performance issues so i would like to know what would be
better and faster approach - should i do the math directly in the database
with row filtering and aggregate queries or it would be better to prefetch
whole database table with 30.000 rows (but it is expected to grow in future
with 30.000 rows per year) and to do the math in python?


Answer: I don't know anything about django, but I do know SQL. I have an outline of a
solution for you.

30,000 records isn't big; it's hardly a warmup. The reason your code is slow
is that you're issuing one query per person. Your DBMS can produce multiple
aggregations for _all_ persons much faster using the GROUP BY feature of SQL:

    
    
    select person, avg(question-1), avg(question-2)
    from tablename
    group by person
    

The important rule is that every non-aggregate in the `select` set must appear
in the `group by` set.

If all your aggregates are about `person` and all come from the same table,
you need only a single query. I bet it will take longer to process the results
than it will take the DBMS to produce them.

