CODEWITHSUNDEEP
phpmysqlperformancenested-loops
thpl
thpl

Reputation: 5910

What is faster? Firing a query within a loop or looping through an array?

I was just asking myself what is faster: I have a for loop where I have to check a certain table column against my loop variable.

Now I have two options: Within the loop I fire a query for each iteration and check my results. The second option is to fetch all the rows I need, and within my for-loop I run a foreach-loop and check if I have a valid match between my desired field and my loop variable.

It's a bit hard to explain but I just let the code speak. I don't use real code here within the loops but fictional function calls by way of illustration.

Option 1: Querying the database for each iteration.

<?php

// Option 1:

for($i = 0; $i < 5; $i++)
{
    $query_result = query_database_where_field_equals($i);
    if($query_result)
    {
        echo "Here is something";
    }
    else
    {
        echo "Here is nothing";
    }

}

// Option 2:

$results = query_database_and_get_all_results();

for($i = 0; $i < 5; $i++)
{

    foreach($results AS $result)
    {
        if($result['desired_field'] == $i)
        {
            echo "Here is something";
        }
        else
        {
            echo "Here is nothing";
        }
    }

}

?>

Both ways will work, but I'm pretty sure it's a worse practice to fire queries within a loop in a performant point of view.

But using a foreach loop within the for-loop also seems to be a bit odd to me.

So what is the prefered way of doing this?

Edit - Added information

I have a for loop that needs to run several times. The amount is static (e.g. 5) and does not depend on any records in the database. Each iteration represents a position.

I have a database table which might store information about what a position contains.

Example:

positions (id, position)

So an example record might be: positions (1, 2)

That means that I have something on position 2. What I want to do in my loop basically is, to check whether I got a record for that position or not. Expected output would be:

Here is nothing
Here is nothing
Here is something
Here is nothing
Here is nothing

Upvotes: 3

Views: 6290

Answers (3)

riverinyo
riverinyo

Reputation: 176

Both previous answers are correct.
@Euantorano: Code iteration is preferable over querying. You'll avoid DB access which is slower and you'll avoid overloading the DB.
Still, as Slaks said, you could add the WHERE condition and that would reduce your array. Improving performance at the loop for checking existence.

Finally I would suggest to get a single bucle to index the results, so you can just check if the results are set.

$res = array();
$results = query_database_and_get_all_results();
for($results as $r) {
    $res[$result['desired_field']] = $r;
}
for($i = 0; $i < 5; $i++) {
    echo (isset($res[i]) ? "Here is something" : "Here is nothing");
}

This code gives you a constant preformance based on the size of the array + your second bucle. Depending on your needs a better query could actually bring better performance, but this is based on your specific question.
Hope it helps!

Upvotes: 1

Euan T
Euan T

Reputation: 2141

The second option is the preferable of the two given options, but knowing some more information about exactly what it is you're looping through and querying might help you get a better answer.

Upvotes: 1

SLaks
SLaks

Reputation: 887225

Neither.

You should put the loop into the query, using WHERE field IN (0, 1, 2, 3, 4)

Upvotes: 1

Related Questions