Database query in a loop¶
ID: rb/database-query-in-loopKind: problemSecurity severity: Severity: infoPrecision: highTags: - quality - reliability - performanceQuery suites: - ruby-security-and-quality.qls
Click to see the query in the CodeQL repository
When a database query operation, for example a call to a query method in the Rails `ActiveRecord::Relation` class, is executed in a loop, this can lead to a performance issue known as an “n+1 query problem”. The database query will be executed in each iteration of the loop. Performance can usually be improved by performing a single database query outside of a loop, which retrieves all the required objects in a single operation.
Recommendation¶
If possible, pull the database query out of the loop and rewrite it to retrieve all the required objects. This replaces multiple database operations with a single one.
Example¶
The following (suboptimal) example code queries theUser object in each iteration of the loop:
repo_names_by_owner.mapdo|owner_slug,repo_names|owner_id,owner_type=User.where(login:owner_slug).pluck(:id,:type).firstowner_type=owner_type=="User"?"USER":"ORGANIZATION"rel_conditions={owner_id:owner_id,name:repo_names}nwo_rel=nwo_rel.or(RepositorySecurityCenterConfig.where(rel_conditions))unlessnegnwo_rel=nwo_rel.and(RepositorySecurityCenterConfig.where.not(rel_conditions))ifnegend
To improve the performance, we instead query theUser object once outside the loop, gathering all necessary information in a single query:
# Preload User datauser_data=User.where(login:repo_names_by_owner.keys).pluck(:login,:id,:type).to_hdo|login,id,type|[login,{id:id,type:type=="User"?"USER":"ORGANIZATION"}]endrepo_names_by_owner.eachdo|owner_slug,repo_names|owner_info=user_data[owner_slug]owner_id=owner_info[:id]owner_type=owner_info[:type]rel_conditions={owner_id:owner_id,name:repo_names}nwo_rel=nwo_rel.or(RepositorySecurityCenterConfig.where(rel_conditions))unlessnegnwo_rel=nwo_rel.and(RepositorySecurityCenterConfig.where.not(rel_conditions))ifnegend