RE: [PHP] Newbie Q: Fetching vs. Looping
Depends what you're after. Your query should always return exactly the data you are after. Your tables should be arranged so that is possible. ---John Holmes... -Original Message- From: Jed Verity [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 29, 2002 5:10 PM To: [EMAIL PROTECTED] Subject: [PHP] Newbie Q: Fetching vs. Looping Hello Again, Folks, I've been testing this for a while and keep coming up with mixed results. In general, is it faster and more efficient to query a MySQL database once with a large SQL select statement and then loop through the huge resulting table? Or does it make better sense to perform a number of smaller queries with smaller resulting tables? This is the kind of stuff they just don't seem to talk about in the manuals. Any insight is appreciated. Sorry for the ignorant question! Jed -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie Q: Fetching vs. Looping
Jed Verity wrote: In general, is it faster and more efficient to query a MySQL database once with a large SQL select statement and then loop through the huge resulting table? Or does it make better sense to perform a number of smaller queries with smaller resulting tables? This is a hard question to give an answer to. It depends largely on the amount of data you're talking about, the amount of memory on the machine your code is executing from, whether you are using persistent database connections, etc. In general, if you use persistent database connections, each additional query after the first will be quite fast. I'm sure your question stems from seeing someone say that queries are the most common bottleneck for Web applications, and this is true to a point. If the one query you speak of is huge, try some tests and see how much memory PHP has to use to maintain that in memory. Consider that multiple requests during the time that PHP is holding that data in memory will result in a pretty decent chunk of memory being used. If you have endless amounts of memory (some huge unix machine with 20+ GB of RAM), I would say go for the one query method. It is definitely going to be faster, so long as you have enough memory to handle it. In most cases, if your approach ends up running your machine out of memory under heavy load, it would be a poor approach, because it would end up slowing *everything* down. So, maybe that gives you some things to think about. Maybe someone else can come up with a more conclusive answer. In summation: 1) using memory to keep *all* database results can improve performance if you have plenty of memory to handle this approach 2) if not, try very specific queries that retrieve just the data you need, and carefully free all results as you finish - also try to use persistent connections if you forsee your site being under heavy load. I personally take approach #2, because most of my environments have had sane (less than 5 GB) amounts of memory. I *think* this method would be more appropriate for most people, even though it seems like you end up hitting the database a lot. I've generally gotten extraordinary performance (page execution takes less than a tentth of a second searching through a database with 50,000 records) from a multi-homed Web host with PHP/MySQL, even with 30,000+ transactions/day. It's also more difficult to get specific data sets with one query; that approach usually requires that you have to take a greatest common denominator style approach and end up with way more data than you really need. I'd be curious to see some other opinions. There's mine. :) Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie Q: Fetching vs. Looping
On Sat, 29 Jun 2002, Chris Shiflett wrote: 2) if not, try very specific queries that retrieve just the data you need, and carefully free all results as you finish - also try to use persistent connections if you forsee your site being under heavy load. Something I haven't picked up in my reading of the manual: Is a free required for every query? Or can I go connect, query, query, query, free, close? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie Q: Fetching vs. Looping
Duncan Hill wrote: Is a free required for every query? Or can I go connect, query, query, query, free, close? You never have to free your results if you don't want to. As I understand it, doing so simply frees up the memory that is being taken up by the result set ($result in most examples). Whenever your script finishes executing, all memory associated with it will be freed anyway. Explicitly doing so simply allows this memory to be freed up sooner. It is definitely beneficial to always free your result set when you're finished with it, but you'll be hard-pressed to notice a difference, to be honest. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Newbie Q: Fetching vs. Looping
On Sat, 29 Jun 2002, Chris Shiflett wrote: Duncan Hill wrote: Is a free required for every query? Or can I go connect, query, query, query, free, close? You never have to free your results if you don't want to. As I understand it, doing so simply frees up the memory that is being taken *tip* Ta. Good programming practice then. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
