devel.reinikainen.net

Introduction

Testing environment: Intel Pentium III (Katmai) 600MHz@672MHz, 256mb SDRAM,
80gb Western Digital 5400rpm IDE hard drive, Debian GNU/Linux 3.0r1 aka.
Woody/Stable, PHP 4.1.2, MySQL 3.23.49, Apache 1.3.26.

The test was run with actual snippets of applications currently in use
by undisclosured communities. The test scripts used to use a simple database
abstraction layer for PHP, which was written by me. In these new verions,
I have changed over to PEAR’s database abstraction layer, as maintaining my
own was too much work (and a bit like re-inventing the wheel). The benchmark
itself is a part of PEAR. Incase you’re wondering about the LATS.php, it’s
just a collection of text and string related functons, written by me.

Benchmarking

• Method 1: Limit number of retrieved rows in SQL statament.
  • Advantages:
    • Lower strain on database, SELECT is LIMITed.
  • Disadvantages:
    • SQL arguments need to be passed to each page, so
      that the script knows where we left off.
  • Benchmark:
    • sqlbench1.phps
    • Result: Using 250 rows of inserted data, execution time was
      on average 0.028655 seconds.

• Method 2: Read all rows to a PHP array and manipulate the array.
  • Advantages:
    • Err… it works?
  • Disadvantages:
    • Each time a new page is loaded, a new “SELECT ALL” SQL query
      is made -> more strain on database.
    • SQL arguments need to be passed to each page, so
      that the script knows where we left off.
    • Huge array with irrelevant data, which is not displayed to
      the user.
  • Benchmark:
    • sqlbench2.phps
    • Result: Using 250 rows of inserted data, execution time was
      on average 0.070571 seconds.

Conclusion

Method one is clearly faster (not to mention memory usage), even on small databases like my testing
environment. With larger databases, the difference in speed is even more
noticeable, in favour to method one.

References

• sqlfoobar.phps: a PHP script I wrote, that
  inserts random data into a SQL table, for testing purposes.
• MySQL
• PHP
• PEAR

Tags: mysql, php

This entry was posted on Saturday, October 3rd, 2009 at 11:17 and is filed under SQL databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.