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
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. On 11/9/05, Hunter Hillegas <lists-HAWAbpnI61OZ1JSuHaJ1sQC/G2K4zDHf@public.gmane.org> wrote:> 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 > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
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 Donald
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 :) Cheers On 11/10/05, Peter Donald <peter.j.donald-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > 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 <http://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<http://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 <http://releases.id> > release_tracks.release_id '' ) > > Hope that helps, > > Peter Donald > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails