PHP - Query single value per iteration or fetch all at start and retrieve from array?

Question

I have a function that looks something like this:

//iteration over scales
foreach ($surveyScales as $scale)
{
    $surveyItems = $scale->findDependentRowset('SurveyItems');

    //nested iteration over items in scale
    foreach ($surveyItems as $item)
    {
        //retrieve a single value from a result table and do some stuff
        //depending on certain params from $item / $scale
    }
}

QUESTION: is it better to do a db query for every single value within the inner foreach or is it better to fetch all result values into an array and get the value from there?

Answer 1

One query that returns a dozen pieces of data is almost 12x faster than 12 queries that return 1 piece of data.

Oh, and NEVER EVER NEVER put a SQL inside a loop, it will always lead in a disaster.

Depending on how your app works, a new connection might be opened for each query, this is especially bad as every DB server has a limit on the number of connections. Then also realize this will happen for each user, so 50 queries with 5 users and you already have 250 queries at any given moment. But even if all the queries do share just 1 connection, you're taxing the DB server X times more, slowing it down for everything else, every page, because users are hogging the DB server on this page, and everybody has to share.

I've seen an entire application fail in the past because of this 1 design flaw, just don't do it.

Answer 2

Definitely fetch all and retrieve from array.

Answer 3

I agree with the others -- and I'm the one who designed and coded the table-relationships API in Zend Framework that you're using!

The findDependentRowset() is useful if you already have a reference to the parent row, and you might need to fetch related rows. This function is not efficient in the least, compared to a query joining both tables. You shouldn't call findDependentRowset() in a loop, ever, if performance is a priority at all. Instead, write an SQL query consisting of a JOIN of both tables.

It's unfortunate in retrospect that Zend's goal for their Framework was simplicity of design, rather than performance.

If I had continued working at Zend, I would have tried to improve the Table interface with a convenient way to perform joined queries against related Zend_Db_Table objects. The solution implemented after I left the project is to build a Select object and pass it to fetchAll(), which is terribly ugly.

edit: In reply to your comment, I did my best to create a solution given a set of requirements. I feel fine about what I did. But Zend is an IDE tools company, so naturally their value is in convenience of coding, not runtime performance. "Rapid Application Development" can mean to develop rapid applications, or to develop applications rapidly. For a tools company, it means the latter.