Avoid N+1 queries
Fixing the N+1 queries raised in test and logged in development mode under Bullet.log from Bullet.
Ref. - N+1 Query and memoization
Loading all data once in memory affects the performance, this can be fixed by using limit and querying in batches.
Usage of find_each or find_in_batches
Performing the query operation in batches to avoid loading large number of records for performance issues. find_each queries result in a batch of 1,000 by default. Both methods differ as find_in_batches yields the result as an array of models instead of individual records.
Use/Remove Database Index if needed
Analyzing the database indexes through rails-pg-extras
and slow database transactions through NewRelic to measure utility of DB index on case-to-case basis.
Remove DB index
- Removing unused indexes analyzed through
- Analysis on removing the DB index if query plan
index scan
cost way higher than sequence scan
cost, and the column(s) have high frequency of insert, update and delete DB operations.
Add DB index
DB index can be added post critical analysis of given conditions -
- The
sequence scan
operations are higher analyzed from explain-analyze query plans on NewRelic and rails-pg-extras
through
- Higher filtering and sorting overhead in query plans, and lower frequency of insert, update, and delete operations affecting index maintenance.
Slow queries can be updated to use an already existing index or an added index can help improve the performance. Using .explain
on queries can be used to analyze if index is utilized, and single, composite or partial indexes can be added to improve query performance.
Usage of Bulk Operations
Creation - #create
accepts an array of hashes and returns one query instead of n, depending on the DB engine supporting bulk insert operation
Updation - Bulk update through update_all
Deletion - delete_all
can be used in place of destroy
, if after_destroy
callback invocation is not required
Prefer #size over #count
ActiveRecord::Relation#size
shall be preferred over ActiveRecord::Calculations#count
since #count
always performs query even if relation is loaded whereas #size
will return length on loaded relation is already loaded otherwise will use #count
Select only required attributes in query
#pluck
or #select
can be used based on whether array of strings or ActiveRecord_Relation is required to be returned. Order in performance preference - pluck > select > collect/map
Check existence of record with exists?
instead of present?
present?
loads all columns and the object in memory whereas exists?
limits only to 1 record and does not select any columns.
Usage of transactions for a group of operations
Enhance render partial performance
Usage of In-Memory calculation if needed
Contrary to using find_each
, where number of queries were more and memory was reduced, sometimes with judgement, in-memory computations can be used to reduce query count and increase memory use. Given that the latter is performing better justified by benchmarks.