I am wondering what the best practices are for optimizing view performance. I have this view that takes over 90 seconds to load (with 5 queries, 2 of which take the bulk of time). It has some complex queries, which a few Subqueries and a bunch of calculations. Should I try to split all these calcu…

[image] valeriuv: It has some complex queries, which a few Subqueries and a bunch of calculations. You should run an explain on that query. It should help highlight which clauses are consuming the bulk of the time. It may also identify cases where additional indexes may help. Superficially, …

Ken, thank you for the detailed reply. [image] KenWhitesell: You should run an explain on that query. It should help highlight which clauses are consuming the bulk of the time. It may also identify cases where additional indexes may help. Can you point me to how I can do that? Django debug t…

So I ran QuerySet.explain() on the slower query and this is the output: GroupAggregate (cost=2067489.82..91166732.02 rows=6865 width=1096) Group Key: dispatchers_dispatcher.id, ((dispatchers_dispatcher.base_amount IS NULL)), (COALESCE((SubPlan 11), '0'::numeric)), (COALESCE((COALESCE((SubPlan 20…

[image] valeriuv: What would be a possible solution in this case? Indexing? If that’s what explain indicates as an issue, yes, that might help. (More to follow on this below) [image] valeriuv: The problem with saving intermediate results as model field values is that the calculations dep…

Ken, thank you so much for these suggestions. I’ll take some time to refactor the load_status and see if I can do that for the customer, as these are strings coming from the import and not consistent.

Ken, I refactored both the load_status and the customer to their own tables. Also, indexed a few columns including these both. Now I’m down to 35 seconds. Is there anything else I can try? Here’s the SQL explain: GroupAggregate (cost=267281.02..17853357.33 rows=7479 width=1096) Group Key: dispa…

You haven’t posted your new models (with the indexes defined) or the new query, so it’s going to be tough for me to offer any specific suggestions. However, it’s pretty clear that you’ve still got a couple of sequential scans being performed: [quote="valeriuv, post:7, topic:23707"] Presorted K…

[image] KenWhitesell: You haven’t posted your new models (with the indexes defined) or the new query Ken, here are the new models (added some indexes after your reply). class Dispatcher(models.Model): ... name = models.CharField(max_length=50, blank=True, null=True, editable=True, un…

[image] valeriuv: How do I break this query down into smaller queries? Unless I’m misunderstanding something, that’s what you’ve already done in the code segment here: [image] valeriuv: So I removed these 2 annotations from the dispatchers query and replaced them with the (dirty?) code b…

[image] KenWhitesell: There are times where the ORM, or even raw SQL, is not the most suitable language for expressing business logic for calculations. Sometimes both clarity and performance are enhanced by performing calculations within Python and not in the database. Thank you so much, Ken!…

Best practices for dealing with slow performance

Using Django Using the ORM

valeriuv October 6, 2023, 10:07am 11

Thank you so much, Ken! Your suggestions helped a lot!

Topic		Replies	Views
How to best reduce number of queries Using the ORM	3	477	July 28, 2023
Complex Queries, Multiple Aggregates, and Calculations - slow request time and reuse Using the ORM	6	2694	December 3, 2022
Query performance question Using Django	15	3405	September 15, 2021
How to efficiently render calculated fields on a table? Forms & APIs	17	5767	November 4, 2023
Inefficient Query Execution Using the ORM	28	2510	June 30, 2023

Best practices for dealing with slow performance

Related topics