dbase query performance vs. iterating over large result array (:include)

Hello Folks. Please excuse my beginner’s ignorance …

My question regards database query performance vs. iterating over nested results in an array. In the example below, a Project has many Tasks. Tasks have a due date. When I display Projects, I would like to also display the cumulative delay for all open Project Tasks based on their due date, and today’s date.

Something like …

Project | Delay XYZ | 5 days (4 tasks late, 1.25 day average delay) … 100 records

As I learn RoR, I’ve discovered a couple ways I can do this. I can perform the calculations in a single query. I can use the :include option to build an array with nested children, and iterate over each Project’s Tasks with something like parent.child.each.do. Or, finally, I can issue 100 queries to the database, once for each record. Each has advantages, but which is the most commonly accepted method in the RoR community?

Thanks in advance


That's one of the instances where I'd have a stored procedure running on the database server, and call it from Rails. I figure all this sort of work should be done completely within the database server, and not tying up my Rails server.

I'm sure you'll get other perspectives on this, but that's my 2c worth.

Dave M.

I'm hard pressed to see the advantages of issuing a query for each child instead of using a single join. That's what databases are for.


A couple of comments...

* Stored procedures are a very _un_rails approach. No offense intended to the author, but Rails is in a world that recognizes that sprocs are another aspect of application programming and it's (opinionated) answer is to avoid them in favor of consolidating the code base into one repository/language/etc. Feel free to use them just make sure you have somehow integrated them and recognize them as a full-fledged part of the code base.

* Another alternative to both the sproc and the query that includes the tasks would be to do three optimized queries:   1. Obtain the Project:       project = Project.find(params[:id])   2. Calculate the last due date for the tasks on the project:       last_due_date = project.tasks.maximum(:due_date)   3. Calculate the number of tasks:       task_count = project.tasks.count I think that gives you enough info to render the view you're interested in and you avoid sprocs and excessive queries. You also avoid the potential delays of having the calculations (and objects) performed in memory.

HTH, AndyV