A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearPHP - Query single value per iteration or fetch all at start and retrieve from array? - page 1
User InfoPosts
PHP - Query single value per iteration or fetch all at start and retrieve from array?#1
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?

posted date: 2008-12-10 09:03:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-10 09:03:01


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#3
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.

posted date: 2008-12-10 09:06:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#4
Definitely fetch all and retrieve from array.

posted date: 2008-12-10 09:07:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#5
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.

posted date: 2008-12-10 09:11:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#6
if you could elaborate a bit on the distaster part, the answer could become the accepted one. thank you!

posted date: 2008-12-10 09:18:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#7
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.

posted date: 2008-12-10 09:24:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#8
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, ...

posted date: 2008-12-10 09:26:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#9
so what kind of distaster... performance disaster?

posted date: 2008-12-10 09:29:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#10
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.

posted date: 2008-12-10 09:32:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#11
Yes, a performance nightmare that get's exponentially worse the more times you loop.

posted date: 2008-12-10 09:36:00


Re: PHP - Query single value per iteration or fetch all at start and retrieve from array?#12
RAD never means the application executes quickly, it always means that you can make applications with less effort.

posted date: 2008-12-11 15:25:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.