0

I have two models, Show and Episode. I need to get the first unrelased or the last released episode of each Show saved in the database. I use the following method but it's terrible slow (650ms~). Have you any idea to how speed up it? (episode.rb)

def last_episodes(status)
  [].tap { |e|
    Show.all(status: status).each { |show|
      episodes   = Episode.all show_name: show.name
      released   = episodes.select { |e|     e.complete? }
      unreleased = episodes.select { |e| not e.complete? }

      e << (unreleased.empty? ? released.last : unreleased.first)
    }
  }
end
Roxas Shadow
  • 380
  • 4
  • 8
  • Can you post the SQL this creates? – Beartech Jan 12 '14 at 23:52
  • Show#all generates `["SELECT \"id\", \"name\", \"tot_episodes\", \"status\", \"fansub\", \"translator\", \"editor\", \"checker\", \"timer\", \"typesetter\", \"encoder\", \"qchecker\", \"created_at\", \"updated_at\" FROM \"shows\" WHERE \"status\" = ? ORDER BY \"id\", \"name\"", [1]]` while Episode#all generates `["SELECT \"id\", \"episode\", \"translation\", \"editing\", \"checking\", \"timing\", \"typesetting\", \"encoding\", \"qchecking\", \"created_at\", \"updated_at\", \"show_id\", \"show_name\" FROM \"episodes\" WHERE \"show_name\" = ? ORDER BY \"id\", \"episode\"", ["Strike the Blood"]]` – Roxas Shadow Jan 13 '14 at 00:01
  • Obviously, Episode#all is called for each show (24, at the moment). – Roxas Shadow Jan 13 '14 at 00:02
  • And will it alway be an `OR` situation? So if a show has at least one unreleased episode, you want that. But if a show doesn't have any unreleased episodes you want the last released one? – Beartech Jan 13 '14 at 00:11
  • Given a show, I need the first unreleased episode (`complete? #=> false`). If all the episodes have been released (`complete? #=> true`), I need the latest. – Roxas Shadow Jan 13 '14 at 00:35
  • What does `Episode.all(:fields => [:show_name], :unique => true, :order => [:episode.asc])` return? I'm trying to see how Datamapper can do `GROUP BY` so that you could do some filtering with scopes and run a minimum of queries, instead of 26+ queries ever time. – Beartech Jan 13 '14 at 02:21
  • My thinking is you should create a scope for Episode that gets all incomplete episodes, grouped by `show_name`, then order the grouping by `episode ASC`, and limit to 1. Then create another Episode scope that gets all complete episodes, grouped by show name, ordered by `episode DESC`, limit 1. Then combine these two scopes with an `|` (OR) clause. That way it will choose the lowest incomplete episode, OR the highest complete one, for each unique show. A SQL expert could tell you which is the most efficient. – Beartech Jan 13 '14 at 02:29
  • 1
    To truly be efficient you should create a field for Show called "last_episode" that is a foreign key to the show's latest episode. Then have a callback that runs any time you update an episode that determines if the latest episode value has changed (due to add a new one that is 'complete' or setting an incomplete one to 'complete'). Or in the Episode table a `last_episode` field that is boolean, and use the same callback structure to flip it for that episode. – Beartech Jan 13 '14 at 02:34
  • You are my savior. With the improvements I have made thanks to your last comment, now it requires `170ms~`. Thank you very much! – Roxas Shadow Jan 13 '14 at 10:33
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/45160/discussion-between-beartech-and-giovanni-capuano) – Beartech Jan 14 '14 at 03:41

0 Answers0