This works but performance is terrible. With several thousand audio
tracks
and release tracks connected to hundreds of releases and dozens of
artists,
thousands of queries are processed each time this page loads.
AR is selecting the releases for each artist, the tracks for each
release
and the audio for each track, when many of the artists and releases have
no
audio information.
Can anyone think of how I can optimize this?
Obviously, I could simply select the audio tracks but then I’m not sure
how
I would build the display so that artists and releases are grouped
correctly
in their hierarchy.
Instead of performing thousands of queries, perform one:
write the quad-join SQL yourself and pull all the data
down in one burst. Also, leave off any db sorting and
instead perform any sorting after the data is pulled.
seems as if you haven’t got any answers yet. So here are some thoughts:
You want to display AudioTracks: Then you write something about
several ‘thousand audio tracks’. Hopefully you don’t mean to load all of
them for one page everytime? If so: Have a look at pagination, this will
limit the audio tracks to let’s say 10 and obviously will decrease the
number of necessary associations that need to be fetched.
That said: Have you got indexes on all of your foreign_keys? At if
already the database work is terribly slow than you might want to put
some work in optimizing your index-structure.
Think about your database design: Is it really necessary to traverse
all these relations (from AudioTracks to Release Tracks to Releases to
Artists). For me it seems reasonable that a AudioTrack belongs as well
as to a ReleaseTrack to a artist and so on). If you want to display
AudioTracks than things should centralise around this AudioTrack (as
well as around Releases if you want to display them)
And post finally: Have a look at the sophisticated caching mechanisms
that Rails provides out of the box. If you are doing this right than you
wouldn’t need any database contact after you’ve fetched the data at the
first request and unless you haven’t updated any data (after that you
have to recreate the cache obviously)
Instead of performing thousands of queries, perform one:
write the quad-join SQL yourself and pull all the data
down in one burst. Also, leave off any db sorting and
instead perform any sorting after the data is pulled.
Why would you sort the data in ruby instead of in the DB? Whenever I
have to go down to SQL I always sort the data there. Is there some
reason not to?
reason not to?
for lightly-loaded dbs sorting on the db server is sufficient.
for heavily-loaded dbs sorting on the server does not scale,
and it turns out to be faster in many cases when it is done
either in the app server or on the end-user’s machine.
it’s just an optimization that one would do if they were having
efficiency problems, which the original poster says he has.
The 3 most expensive things you can do to slow an (otherwise optimized)
db:
thrash it with unnecessary queries
joins
sorts
This is based on my experience. Yours may be different.
I never said it was better
However, in this case, sorting is typicaly something you might leave up
to
the database.
Especially if you are dealing with large collections. In-memory sorting
can
take a lot of time and consume a lot of resources.
So in general, I would suggest to let the database do all the sorting
unless
this is impossible for some other (technical) reason.
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.