Forum: Ruby on Rails Is It Possible to Re-Work This for Better Performance?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A7db9ec803b5895ae5f916a74e2db329?d=identicon&s=25 lists (Guest)
on 2005-12-08 04:05
(Received via mailing list)
I have four AR classes:

Artists (has many Releases)
Releases (has many ReleaseTracks)
Release Tracks (has many AudioTracks)
Audio Tracks

They are all also connected in the other direction (i.e. belongs_to).

My page displays audio tracks, grouped by release track, then release
and
then by artist.

Right now I fetch the artists and simply use the defined relationships
to
display everything:

Loop through Artists
* Display Releases
** Display Release Tracks
*** Display Audio Tracks

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.

Any help is appreciated. Thanks much.

Cheers.
Ce60c4f78a63b0695e4dafc4bd7964f7?d=identicon&s=25 vanek (Guest)
on 2005-12-08 11:54
(Received via mailing list)
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.
E48d29dc8fedb2878fa518d41cc63d88?d=identicon&s=25 JanPrill (Guest)
on 2005-12-08 11:58
(Received via mailing list)
Hi, Hunter,

seems as if you haven't got any answers yet. So here are some thoughts:

1. 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.
2. 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.
3. 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)
4. Finally: Eager Loading: Right now you are fetching relations as you
are iterating over AudioTracks. You might as well prefetch this
relations on the serverside: Have a look at
http://api.rubyonrails.com/classes/ActiveRecord/As...
(search for Eager loading of associations)
5. 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)

Regards
Jan
89441a6c74647d292f5cc951eae24cfa?d=identicon&s=25 jackc (Guest)
on 2005-12-08 15:18
(Received via mailing list)
Lou Vanek wrote:

> 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?


--
Jack Christensen
jackc@hylesanderson.com
38a8230ed3d5c685558b4f0aad3fc74b?d=identicon&s=25 joevandyk (Guest)
on 2005-12-08 15:18
(Received via mailing list)
On 12/8/05, Jack Christensen <jackc@hylesanderson.edu> wrote:
> reason not to?
Generally, it's a good idea to keep business logic in Ruby if
possible.  Only reason I can think of, anyways.
F734b6ca0eb2ebd01744224437a72498?d=identicon&s=25 harmdelaat (Guest)
on 2005-12-08 15:22
(Received via mailing list)
It is probably faster to let the database do the sorting.
The DB engine is mostlikely better optimized to do this kind of thing.
38a8230ed3d5c685558b4f0aad3fc74b?d=identicon&s=25 joevandyk (Guest)
on 2005-12-08 15:52
(Received via mailing list)
On 12/8/05, Harm de Laat <harmdelaat@gmail.com> wrote:
> > > >
> > > >
> > > 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?
> >
> > Generally, it's a good idea to keep business logic in Ruby if
> > possible.  Only reason I can think of, anyways.
>
> It is probably faster to let the database do the sorting.
> The DB engine is mostlikely better optimized to do this kind of thing.

It's probably faster to write everything in highly-optimized assembly,
but that doesn't mean it's a good idea.  Speed isn't everything.
Ce60c4f78a63b0695e4dafc4bd7964f7?d=identicon&s=25 vanek (Guest)
on 2005-12-08 15:56
(Received via mailing list)
Jack Christensen wrote:

> 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.
F734b6ca0eb2ebd01744224437a72498?d=identicon&s=25 harmdelaat (Guest)
on 2005-12-08 16:00
(Received via mailing list)
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 topic is locked and can not be replied to.