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
