# Dbase query performance vs. iterating over large result arra **URL:** \ **Category:** Rails\ **Created:** [July 24, 2007, 11:31pm UTC](https://www.ruby-forum.com/t/dbase-query-performance-vs-iterating-over-large-result-arra/110289 "2007-07-24T23:31:57Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![amin1426](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4da419/32.png) [@amin1426](https://www.ruby-forum.com/u/amin1426)\ **Post date:** [July 24, 2007, 11:31pm UTC](https://www.ruby-forum.com/t/dbase-query-performance-vs-iterating-over-large-result-arra/110289/1 "2007-07-24T23:31:57Z")
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 John * * * Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. ---
**Author:** ![amin1426](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4da419/32.png) [@amin1426](https://www.ruby-forum.com/u/amin1426)\ **Post date:** [July 25, 2007, 9:37am UTC](https://www.ruby-forum.com/t/dbase-query-performance-vs-iterating-over-large-result-arra/110289/2 "2007-07-25T09:37:41Z")
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. ---
**Author:** ![amin1426](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4da419/32.png) [@amin1426](https://www.ruby-forum.com/u/amin1426)\ **Post date:** [July 25, 2007, 9:37am UTC](https://www.ruby-forum.com/t/dbase-query-performance-vs-iterating-over-large-result-arra/110289/3 "2007-07-25T09:37:43Z")
On 7/24/07, John C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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? 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. Chris ---
**Author:** ![amin1426](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4da419/32.png) [@amin1426](https://www.ruby-forum.com/u/amin1426)\ **Post date:** [July 25, 2007, 6:53pm UTC](https://www.ruby-forum.com/t/dbase-query-performance-vs-iterating-over-large-result-arra/110289/4 "2007-07-25T18:53:21Z")
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: HTH, AndyV On Jul 25, 12:04 pm, Jean N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![amin1426](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4da419/32.png) [@amin1426](https://www.ruby-forum.com/u/amin1426)\ **Post date:** [July 25, 2007, 6:04pm UTC](https://www.ruby-forum.com/t/dbase-query-performance-vs-iterating-over-large-result-arra/110289/5 "2007-07-25T18:04:17Z")
snacktime wrote: > 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. > > Chris /agreed CRUD functionality I always use the ActiveRecord stuff, but complex queries I drop the ORM layer and use queries right into the database. Either by issuing a find\_by\_sql query or an execute command for updates and such. The hard fact is 1 query \> multiple queries in an iteration ANY day. (With respect to network IO, database load, table / row locking etc etc) Hope this helps.