Should I access the database multiple times or loop through an array?

Question

Which is the most efficient method for gathering related data?

Lets say we have multiple projects that are associated to one category 1:M.

Each project holds the ID value of the Category it is a part of. I have an array object of all my projects, I now want to add to that array object its category name rather than its ID.

So for example instead of:

project_id: 1 project_title: first project project_desc: bla bla project_category_id: 1

I want it to be:

project_id: 1 project_title: first project project_desc: bla bla project_category: first category

I'm not having trouble achieving this, I'm curious what the most efficient way to achieve it is.

Option 1

$projects = $this->projects_model->find_all();
$categories = $this->category_model->find_all();

foreach($projects as $project){
   foreach($categories as $category){
      if($project->category_id == $category->id){
         $project->category = $category;
      }
   }
}

Option 2

$projects = $this->projects_model->find_all();

 foreach($projects as $project){
   $project->category = $this->category_model->find_by('id', $project->category_id);
 }

It's likely there are many other methods for achieving my desired result and potentially in a more efficient manor, if that's the case I'd love to learn.

why not use SQL for this? i am assuming you have some sort of database so all you need is "select * from projects where category_id = '$category' — Eyal Alsheich
– Eyal Alsheich, Commented Nov 18, 2012 at 20:42
Sorry, shouldn't have used my prebuilt db helper method in my examples. Those find_all methods essentially do 'select * from projects' — hoppipolla
– hoppipolla, Commented Nov 18, 2012 at 20:44

John Conde · Accepted Answer · 2012-11-18 20:40:30Z

1

You should profile this yourself and you'll have your answer
Hitting the database is expensive. Accessing arrays data is not. This means you'll want to hit the database as few times as possible.

answered Nov 18, 2012 at 20:40

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sébastien Renauld · Accepted Answer · 2012-11-18 20:41:57Z

0

Assuming your find_all and find_by functions request data from a database, option 1 will be preferable until the ratio between categories sought and categories used becomes low. Sequential (i.e. read-then-read-the-next-row) read operations on SQL are much quicker than targetted read operations.

Ultimately, though, if I were you, I would worry less over the actual array looping and more about how you acquire your data, as this is where the performance drain will be.

answered Nov 18, 2012 at 20:41

Sébastien Renauld

19.8k2 gold badges51 silver badges69 bronze badges

Collectives™ on Stack Overflow

Should I access the database multiple times or loop through an array?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related