N+1 Query

[sentry]

Sentry Issue: GENLAB-21

Offending Spans	db - SELECT %s AS "a" FROM "genlab_bestilling_a...

/staff/ - order_markers

[nicokant]

@claude can you find potential N+1 queries in the codebase?

[claude]

Claude finished @nicokant's task —— View job

---

N+1 Query Analysis Results

I've completed a comprehensive analysis of the codebase and identified several potential N+1 query patterns that could be causing the performance issues reported in the Sentry issue.

Todo List:

• Read repository setup instructions (CLAUDE.md)
• Analyze the codebase structure and identify Django models
• Search for the specific "/staff/" route and order_markers functionality
• Look for common N+1 query patterns in the codebase
• Provide recommendations for fixing identified issues

Critical N+1 Query Issues Found

1. Model Method N+1 Patterns

File: src/genlab_bestilling/models.py:666-669

for marker in self.markers.all():  # N+1: Each marker access loads species
    for sample in self.from_order.samples.filter(
        species__in=marker.species.all()  # N+1: marker.species accessed in loop
    ):

This nested loop in AnalysisOrder.populate_from_order() causes multiple N+1 queries.

File: src/genlab_bestilling/models.py:526-530

for sample in self.samples.all():  # N+1: accessing sample.has_error property
    try:
        sample.has_error  # N+1: Each access triggers multiple DB queries

2. Property-based N+1 Issues

File: src/genlab_bestilling/models.py:366-372

@property
def filled_genlab_count(self) -> int:
    return self.samples.filter(genlab_id__isnull=False).count()  # N+1 per access

@property  
def isolated_count(self) -> int:
    return self.samples.filter(is_isolated=True).count()  # N+1 per access

3. View-Level N+1 Patterns

File: src/staff/views.py:204-207

return {
    eo: eo.samples.filter(id__in=sample_ids).count() for eo in extraction_orders  # N+1 per order
}

Missing prefetch_related in several staff views:

• AnalysisOrderDetailView.get_context_data() line 226
• ExtractionOrderDetailView.get_context_data() line 303

4. API-Level N+1 Issues

File: src/genlab_bestilling/api/views.py:421-424

for marker in markers:
    for sample in samples:
        if not marker.species.filter(id=sample.species_id).exists():  # N+1 query per combination

5. Template/Table Rendering N+1

File: src/staff/tables.py:151-153

return ", ".join(
    sorted({sample.species.name for sample in value.all() if sample.species})  # N+1 per sample
)

Recommended Fixes

1. Add proper select_related and prefetch_related:

   # Example fix for AnalysisOrder.populate_from_order()
   markers = self.markers.prefetch_related('species').all()

2. Convert property queries to annotations:

   # Example: annotate querysets instead of using properties
   orders = Order.objects.annotate(
       filled_count=Count('samples', filter=Q(samples__genlab_id__isnull=False))
   )

3. Batch operations in loops:

   # Use bulk operations instead of individual queries
   SampleMarkerAnalysis.objects.bulk_create([...])

4. Add proper prefetching in views:

   def get_queryset(self):
       return super().get_queryset().select_related(
           'species', 'location', 'type'
       ).prefetch_related('markers', 'order__species')

The "order_markers" functionality mentioned in the Sentry issue is particularly affected by these patterns, especially where Sample, Marker, and SampleMarkerAnalysis relationships are accessed without proper prefetching.

These N+1 patterns explain the SELECT queries mentioned in your Sentry issue (GENLAB-21) where the same basic SELECT pattern is repeated many times instead of using efficient joins.

---

[nicokant]

@claude open a PR and fix this

[claude]

Claude finished @nicokant's task —— View job • claude/issue-448-20250807-0751 • Create PR ➔

---

I've successfully created a PR to fix the N+1 query issues identified in the previous analysis.

Todo List:

• Read repository setup instructions (README.md)
• Analyze current codebase structure and understand Django models
• Fix N+1 queries in models.py (AnalysisOrder.populate_from_order)
• Fix property-based N+1 issues in models.py
• Fix view-level N+1 patterns in staff/views.py
• Fix API-level N+1 issues in genlab_bestilling/api/views.py
• Fix template/table rendering N+1 in staff/tables.py
• Run linting/testing if available
• Commit and push changes
• Provide PR creation link

Fixed Issues:

1. AnalysisOrder.populate_from_order() (models.py:666-669)

• Added prefetch_related('species') for markers
• Added select_related('species') for samples
• Pre-computed marker species IDs to avoid repeated queries in nested loops

2. Property-based N+1 Issues (models.py)

• Added prefetch in validation loop for has_error property access
• Created OrderQuerySet.annotate_sample_counts() method with annotations for filled_genlab_count and isolated_count
• Updated views to use annotated values instead of properties

3. View-level N+1 Patterns (staff/views.py)

• Fixed get_extraction_order_sample_counts() to use annotations instead of individual count queries
• Added proper select_related/prefetch_related to AnalysisOrderDetailView and ExtractionOrderDetailView
• Updated OrderExtractionSamplesListView and SampleLabView to use annotated sample counts

4. API-level N+1 Issues (api/views.py:421-424)

• Pre-computed order marker names and marker-species mappings
• Eliminated repeated marker.species.filter() calls in bulk_create loops

5. Table Rendering (staff/tables.py:151-153)

• Added documentation comment explaining prefetch requirements
• Verified existing prefetch_related in views covers the species access

These optimizations should significantly reduce the N+1 queries reported in Sentry issue GENLAB-21, especially for the /staff/ routes with order_markers functionality.

---