Benchmarking PSQL

Since I’ve written PSQL I’ve wondered how well it performed compared to “old school” queries and “normal” prepared statements.

I’ve written a simple test suit that measures how quickly this function, a standard mysql_query() and mysql_fetch_assoc() and an optimized prepared statement can perform simple (and common) task a few thousand time.

The test for every function consists of 3 steps. First, the function has to insert a number of rows ((typically around 5000)) into a table which consists of two fields: an auto-incrementing id-field and a text-field. Secondly the function searches and returns every field it has inserted, one by one. Than all the rows are queried for, returned and looped trough. Lastly, every field is deleted again, one by one.

Here is what the test typically shows:

Comparison of SQL query methods. Each 5000 cycles. Less is better.
INSERT [s] Load every single line [s] Load all and loop through [ms] Delete [s]
PSQL 0.471911907196 9.14229488373 16.2379741669 5.98818397522
mysql_query and mysql_fetch_assoc 0.297914981842 9.21642112732 9.70196723938 4.69128489494
Standard prepared statements 0.35383105278 9.9942779541 12.2458934784 6.47421002388

And here is what it looks like:

If you like to, you can have a look at the benchmark source.

We see that PSQL is slower than both ordinary query and prepared statements, though not by much, and that mysql_query() is sometimes even faster than a prepared statement – which is quite surprising.