Skip to content

Optimize AggregateLast30DayUsageService to eliminate N+1 queries

Rajendra Kadamrequested to merge
561462-improve-usage-service-aggregate-performance into master

What does this MR do and why?

Optimize AggregateLast30DayUsageService to eliminate N+1 queries

Replace per-component database queries with batched bulk SQL updates using CASE statements. This reduces query count from 2 queries per component to 2 queries per batch of 1000 components.

For 10,000 components: 20,000 queries → ~20 queries (1000x improvement)

Changes:

  • Use find_in_batches with ActiveRecord to read usage counts
  • Use raw SQL with CASE statements for bulk updates
  • Add query performance spec to prevent regressions

References

#561462

Screenshots or screen recordings

NA

How to set up and validate locally

  1. The pipeline should be green, nothing changes functionally, it is optimization for a service that run as sidekiq job.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #561462

Merge request reports