Slow API calls: N+1 problem

[Tishka17]

Deployment Type

Self-hosted

Triage priority

N/A

NetBox Version

v3.7, v4.2

Python Version

3.11

Steps to Reproduce

1. Fill enough data for devices with cables and connections.
2. Do API requests to load device, interfaces, ip-addresses like
   http://localhost:8000/api/dcim/interfaces/?device_id=3971&limit=100&offset=0

Expected Behavior

Data is loaded quite fast, <0.5 seconds. Number of db queries pretty small up to 10-20 per HTTP-request

Observed Behavior

Some requests are pretty slow. Up to 3 seconds.
130 sql queries are produced on 3.7 and 160 db queries on 4.2

CabledObjectModel.link_peers has N+1 problem: https://github.com/netbox-community/netbox/blob/v3.7.8/netbox/dcim/models/device_components.py#L192
CablePath._get_path has N+1 problem as well: https://github.com/netbox-community/netbox/blob/v3.7.8/netbox/dcim/models/cables.py#L746

I tried to comment out these methods and got only 20 db queries on the same http request

[bctiemann]

It would be ideal if each of these identified points of improvement could be opened as a separate bug, so they can be individually fixed (i.e. if one is easier than another).

[jeremystretch]

Data is loaded quite fast, <0.5 seconds. Number of db queries pretty small up to 10-20 per HTTP-request

I think we need something more explicit here. Asserting that the application should perform faster is not sufficient justification for a bug report. @Tishka17 please extend your post above to cite the specific optimizations you believe are missing.

[Tishka17]

@jeremystretch sorry, I thought the "N+1 problem" words with links to corresponding functions are enough. What else can I add? For me it is a performance issue, but according to a code - it's just a technical bug with improper related data loading

[jeremystretch]

What else can I add?

Please detail the specific changes you are proposing.

[Tishka17]

I am proposing to fix code in a way, that it has no N+1 problem or lazy loading. I am not Django developer, so I am not familiar how it's technics like prefetch_related can be used in this specific case

[n-rodriguez]

https://dev.to/herchila/how-to-avoid-n1-queries-in-django-tips-and-solutions-2ajo

My 2 cents

[Tishka17]

The problem is that some fields are calculated are not just generated based on model data, but explicitly do requests to a database.

I've tried to fix link_peers replacing .exclude query with filtering already loaded self.cable.terminations but it references then GenericForeignKey and we need its children in response..., so I did not succceded.

- peers = self.cable.terminations.exclude(cable_end=self.cable_end).prefetch_related('termination')
- return [peer.termination for peer in peers]
+ return [peer.termination for peer in self.cable.terminations if peer.cable_end!=self.cable_end]

[jsenecal]

To answer to that specific example (prefetch w/ generic foreignkeys) there was an upstream issue to try to mitigate this (
https://code.djangoproject.com/ticket/33651) which has been implemented in Django 4.0.

Not sure if the netbox codebase uses that everywhere, but it is implemented in https://github.com/netbox-community/netbox/blob/main/netbox/utilities/fields.py#L74