Tracking and fixing issues for query optimisation

Queries being executed along with time and space complexity helps to optimise database in the best possible ways. Though this topic has been covered vastly, i will pull the very basic ones for the beginners.

In rails applications, active record is being used as a wrapper for SQL databases. In order to view each query which is being executed below to get a clear idea, add the corresponding MySQL query 

ActiveRecord::Base.logger = Logger.new(STDOUT) 

Use joins to combine tables if they have a association and then fetch data. When you loop over this fetched data in first place, queries to access related data from other model will not be executed as it is already available.
Eager loading will not work in NoSQL databases as in case with MongoDB due to the absence of joins.Even though 
The identity map in Mongoid is a current aid to assist with excessive database queries in relations, and is necessary for eager loading to work. Refer:   Mongoid Identity Map


Add to_a or .entries or .first after query as mongoid returns only the criteria.

        For ex- User.where(name: /a/).entries

so when that criteria is used, queries for each operation are fired. This will makes sure the entire data set is available and any operation will be performed on that data without hitting server to fetch data.

MongoDB works with lazy loading as it returns only criteria in first place and when you loop over the data each query is fired to fetch single row at a time which can be avoided as said above.

Bijendra Singh

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard.

0 comments: