views:

1182

answers:

3

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?

+10  A: 

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.

TravisO
I would say that a query retrieving x rows is *more than* x times faster than x queries each retrieving 1 row. The more rows, the bigger the difference gets.
Tomalak
if you could elaborate a bit on the distaster part, the answer could become the accepted one. thank you!
tharkun
To elaborate on what he's saying: You'll be hitting the database 12 separate times. That's (at least) a dozen PHP function calls, possibly 12 round trips across a network to the DB server, 12 times the server has to parse the query and construct an execution plan, ...
Ant P.
so what kind of distaster... performance disaster?
tharkun
Yes, a performance nightmare that get's exponentially worse the more times you loop.
TravisO
thanks a bunch! I'll not do it anymore :)
tharkun
Exactly. You might not notice it with 10 or 20 rows, but it'll get exponentially slower with more, and that's assuming it doesn't start interacting with things like cache size limits...
Ant P.
Also the performance may be impacted suddenly, due to the state of data. Problems like this are often surprises because tests usually uses modest amounts of data.
Bill Karwin
+1  A: 

Definitely fetch all and retrieve from array.

Logan Serman
+3  A: 

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.

Bill Karwin
you are criticizing your own work :) do you dislike Zend Framework nowadays or is it just this particular issue which you find ugly? I'm asking because it sounds like you know a lot about ZF and if someone who knows it, doesn't like it at all... well.
tharkun
RAD never means the application executes quickly, it always means that you can make applications with less effort.
TravisO