Forum Moderators: coopster
I know that pages are faster when you limit the amount of data that PHP has to wade through to get to the data to display. But is it still faster to do four separate database calls vs. getting all the data in the table and parsing it with PHP/arrays, etc?
I'm guessing this question depends on the size of the table, which, right now isn't that big but could be quite large down the road...
Of course if 5 db calls are overall heavier than doing the whole lot in 1 db call then advantages of cutting down on communication between script and database will not be sufficient.
All things equal I'd go for fewer db calls rather than greater.
If using 1 DB call results in a huge array that must be kept in memory, while five calls results in one small array that must be kept in memory and four more small arrays that are read into memory and then thrown away when done, then you might want five calls.
On the other hand, if the array is not really that big, then concurrent requests on the DB might be your limiting factor.
Another thing to keep in mind is that if you benchmark on a stand-alone development platform with lots of memory, you may not notice the performance hit of using a huge array, which will only become obvious as you get a lot of concurrent hits and you run out of memory.
Make sure to bench it not just using PHP benching with microtime(), but to check it with Apache Bench [httpd.apache.org] using different settings for the concurrency option (i.e. the "-c" flag).
Tom
<?php
//at script start
$start_time=microtime(true);
/*Your script here */
//at script end
$end_time=microtime(true);
$time_taken=$end_time-$start_time;
print $timetaken;
?>
Besides this you can also use the "Apache Benchmark tool" to check which one is faster.
[httpd.apache.org...]
Hope that helps,
Saurabh.
(how could you disagree with someone named Lord Majestic anyway?)
Its okay - my version of hell is that of where there is nothing to argue about ;)
Talking of benchmarking - I'd be careful there because of database caching issues - if your query is likely to be heavy to access disk then its imperative to ensure fair conditions for benchmarking two pieces of code. Generally speaking "cold start" of a database is a pre-requisite for these cases.
If you are positive that your whole database will be in memory anyway (fair assumption for commonly executed "point" queries, but could be very wrong for "ranged" data-warehousing type of queries (that I normally use)) then issue of caching is less of an ... well, issue.
Personally I prefer to analyse SQL's effectiveness in terms of IO's its likely to use. This is easy to do in commercial databases but might be harder on OSS alternatives - they all should have "show plan" type of tools however. This approach is also useful in a way that allows to optimise query as you will not be deceived by quick response on your test system, which might not work so well in multi-user environment.
Anyhow publishing SQL in question might allow us give more concrete answers.
I have site where I have one database call that gets a bunch of data to display on the home page. I'm also have 4 other database calls that gets data from the same table and orders it by "popularity" (for example) with a limit of 10 (to speed the data access process).
If you can build a single query that returns all of your target rows- that is, combine all 5 WHERE clauses in one SELECT- then yes, sorting and sifting the data will usualy be much faster.
The overhead of 4 additional database roundtrips is much higher than any sorting or grouping usually performed for a homepage.
if the data you want to display doesn't have to be different every time a user hits the page then why not do this ...
autogenerate the page as a static page every 24 hours (or every hour or whatever) and then just serve this page every time the homepage is called.
you do this by running a script every time period (whatever it is) which makes all the database calls, write the results to a text file which you can either call as an include for your home page or even write the whole of your homepage to this file.
write the results to a text
If you do that, then make sure you write data into temporary file and when its all done you rename it into final version - this is to minimise locking and partial file serving issues.
