Help With Pre-Loading


#1

I need a little bit of help to determine if pre-loading could help me
solve
a performance problem.

I have the following models:

Band (has_many Releases)
Release (has_many ReleaseTracks)
ReleaseTrack (has_many AudioFiles)
AudioFile

So, the situation arises that I want to see all the AudioFiles for a
band.
Since the band and the audio are several associations away from each
other,
would pre-loading even work in this situation?

I am having performance problems when writing code to answer the
following
question:

How many audio files does a band have?

Right now I am getting the releases, getting the tracks, and iterating
through the audio files. This takes a long time. Since the associations
are
so far apart, I don’t think the counter_cache thing will work, right?

If anyone has any suggestions as to how I could improve things, that
would
be great. Every time I ask a question like this I find out Rails has
some
kind of special feature that solves my problem.

Maybe I’ll get lucky again now?

Thx,
Hunter


#2

You can try to denormalize your schema by adding band_id to the
audio_file and make sure you set that everytime you create it.


#3

I am not the greatest of SQL gurus but I am sure you could do it more
efficiently inside the database using joins of some sort. Something
like

SELECT audio_files.*
FROM audio_files
RIGHT JOIN release_tracks ON release_tracks.id =
audio_files.release_track_id
RIGHT JOIN releases ON releases.id = release_tracks.release_id
WHERE releases.band_id = 1

may select the audio_files for a specific band … or maye complete
gibberish more likely but you should get the idea. Then in your code
you can do something like the following

AudioFile.find(:all, :conditions => [ ‘releases.band_id = ?’, 1 ],
:joins => 'RIGHT JOIN release_tracks ON release_tracks.id =
audio_files.release_track_id RIGHT JOIN releases ON releases.id =
release_tracks.release_id ', :select => ‘audio_files.*’ )

or

AudioFile.count( [ ‘releases.band_id = ?’, 1 ], 'RIGHT JOIN
release_tracks ON release_tracks.id = audio_files.release_track_id
RIGHT JOIN releases ON releases.id = release_tracks.release_id ’ )

Hope that helps,

Peter D.


#4

Hunter,

Just to expand on Peter’s suggestion…(careful I’m a noob) You could
wrap
the request in another method to make more DRY. You could make this a
method
of the model so that in your controller or view you could say

@my_band.count_audio_files

and include the code that Peter suggests in there

ie
def self.count_audio_files
AudioFile.count( [ ‘releases.band_id = ?’, self ], 'RIGHT JOIN
release_tracks ON release_tracks.id = audio_files.release_track_id
RIGHT JOIN releases ON releases.id http://releases.id/ =
release_tracks.release_id ’ )
end

I haven’t tested this… I hope it’s the right way to do it :slight_smile:

Cheers