CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3
Raoot
Raoot

Reputation: 1771

Rails executing too much SQL

I've started to notice via Terminal that my Rails app is producing way too many SQL statements, many of which aren't required. I do remember reading somewhere this can be a problem and right now as my data grows it's noticeably slowing the app down.

For example, I have releases that have tracks. Artists can be assigned to both tracks and releases. When I load a release that has only 8 tracks it seems to be running through every single track in the DB to find those relationships!?!

For example, see below and this is a very small sample, but none of these tracks are actually associated to the release. It's going through every track in the DB!!

Any general pointers?

Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 12
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 19
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 21
  Artist Load (0.9ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 22
  Artist Load (0.7ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 23
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 24
  Artist Load (0.9ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 25
  Artist Load (1.0ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 26
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 27
  Artist Load (0.9ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 28
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 29
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 30
  Artist Load (0.6ms)  SELECT `artists`.* FROM `artists` INNER JOIN `artists_tracks` ON `artists`.`id` = `artists_tracks`.`artist_id` WHERE `artists_tracks`.`track_id` = 31

Here's the models involved:

class Artist < ActiveRecord::Base  
  has_many :artist_releases
  has_many :releases, :through => :artist_releases 
  has_many :artists_tracks
  has_many :tracks, :through => :artists_tracks
end

class ArtistRelease < ActiveRecord::Base
  belongs_to :artist
  belongs_to :release
end

class ArtistsTrack < ActiveRecord::Base
  belongs_to :artist
  belongs_to :release
  belongs_to :track
end

class Release < ActiveRecord::Base
  has_many :artist_releases
  has_many :artists, :through => :artist_releases
  accepts_nested_attributes_for :artists, :reject_if => lambda { |a| a[:name].blank? }
  accepts_nested_attributes_for :artist_releases    
  has_many :releases_tracks, :dependent => :destroy
  has_many :tracks, :through => :releases_tracks, :order => "releases_tracks.position"
  accepts_nested_attributes_for :tracks, :reject_if => lambda { |a| a[:name].blank? }, :allow_destroy => :true
  accepts_nested_attributes_for :releases_tracks    
end

class ReleasesTrack < ActiveRecord::Base
  default_scope :order => 'releases_tracks.position ASC'
  acts_as_list :scope => :release_id
  belongs_to :release
  belongs_to :track
end

class Track < ActiveRecord::Base
  has_many :releases_tracks, :dependent => :destroy
  has_many :releases, :through => :releases_tracks
  has_many :artists_tracks
  has_many :artists, :through => :artists_tracks
  accepts_nested_attributes_for :artists, :reject_if => lambda { |a| a[:name].blank? }
  accepts_nested_attributes_for :artists_tracks
end

Upvotes: 1

Views: 685

Answers (1)

georgebrock
georgebrock

Reputation: 30023

From the queries Rails is generating, it looks like you're doing something like this, which loads each artist individually when you refer to it:

release.tracks.each{ |t| t.artist }

You should be eagerly loading the artists so that they are pre-loaded in a single query:

release.tracks.includes(:artist).each{ |t| t.artist }

The includes method is very flexible, and allows you to eagerly load multiple associations, or even nested associations:

Release.first.includes(:releases_tracks => {:track => :artist})

This will load the first Release, then all of its ReleaseTracks, and then all of their Tracks, and the all of the Tracks' Artists in just 4 queries (1 per table). This is much more efficient than loading each record individually.

There's more information in the section on Eager Loading Associations in the Active Record Query interface guide.

Upvotes: 5

Related Questions