Adventures in programming: database query times

As I mentioned yesterday, I am learning PHP.  I'm working my way through tutorials and reading all sorts of stuff.  At the same time I am refining and adjusting my design of Pray with Friends as I learn stuff.

Part of Pray with Friends will be a page where you actually read off a prayer.  The prayer will have lots of parts, like an opening, a closing, and some transitional statements.  I intend to have many different openings and closings, and just randomize them.  That way the prayers will seem new and different each time.

One of the things I have to do is handle the list of openings, closings, etc.  I have several lists that will exist somewhere.  I was thinking that they belong in the database.  I'm still learning, and so this is subject to change, but the test I just did indicates that I need to just build the lists as hard-coded arrays in an included PHP file.

Here's what I did.  I wrote a simple PHP page that includes (PHP-speak for 'connects to') a PHP file and connects to a database.  The main PHP page declares a start_time variable and then runs a counting do...while loop.  Each iteration of the loop prints a row that contains the counter number and a sentence.  The sentence is either pulled from the database or the hard-coded PHP array in my included PHP file.  The page also declares an end_time variable and subtracts the start_time variable from it to display an elapsed time.

The included PHP file's array and the database table were identical.

I just comment and uncomment the rows with the calls to either the database or included PHP file to run the tests different ways.

The database is on the same box where I am running everything else.  So there is no network latency in this test.

I ran the file both ways for 10,000 rows.  Care to guess the performance difference between the two methods?

Pulling 10,000 records from the database took 10 seconds.

Pulling 10,000 records from the hard-coded array in the included PHP file took 0.3 seconds.

All of this was processing on my local desktop.  The database query was overall much more processor intensive, too.  My quad core processors spiked to about 20% utilization for several of the 10 seconds during the database query (yes, all four of them.)  My processor monitor didn't even register the query to the hard-coded PHP array (not even a skinny little point.)