Tuesday, 4 November 2014

Why should you use PDO?

I was recently asked if I could think of any good questions to ask a PHP programmer in an interview. Obviously this got me curious as to what Google might return for this query.

I noticed that a few people recommended asking questions in relation to PDO. All people suggesting questions on PDO were forming questions where the answer essentially was that PDO should be implemented so that switching database is easy/easier.

I have worked on numerous commercial products for a number of years and none of these have ever switched database.

How common a change it is to switch database on a project? I can't imagine the % is very high, although I have not been able to find any figures on this. Additionally of the systems that require switching I suspect they are probably moving from an archaic language / database which does not support the system they are moving to, and so you are largely doing a complete rewrite anyway. Will SuperSQL++ 2040 support your PDO queries from PHP 5.6? Perhaps a database abstraction layer is of minimal real benefit?

There are only 3 real reasons for switching database that I can think of:

Proprietary features
If you want to use features that only exist in a specific database then generics are not the answer, use of proprietary features implies PDO is not useful in this case.

Cost
A reasonable cause for switching DB vendor, but how often will payment terms be altered over the lifetime of a product? Is switching worthwhile vs the cost of testing, development and deployment, even if you did have PDO implemented?

Support
If the DB is no longer supported then sticking to it becomes a significant business risk, however, if you are using a system that is no longer supported and PDO would have been beneficial then you really did back the wrong horse. Most databases have been around for many years and I doubt any of the PDO driver ones are going away soon.

So does PDO bring any other benefits?

Well it does have named prepared statements. These are superior to MySQLi's "?" sequencial parameters.

Then there is client side prepared statements, this can actually lead to improved performance in PDO statements over using MySQLi.

What are the Costs?

Implementing PDO in terms of coding does not really add any noticeable cost, however, there are resource costs which should be considered. There is a small overhead on every PDO command versus using proprietary extensions. During my testing I have found this overhead to be 2-10% depending on the nature of the query. This essentially translates to a 2-10% increased database server resource cost. Some examples can show PDO is faster, but I suspect that most people would agree that overall in an application there is a small percentage loss in performance.

Perhaps more significantly you can't use proprietary features. There are minor inconviences like not having num_rows() for select statements. 

Then there are major issues like not being able to perform asynchronous queries. Depending on your use case you can see up to 50% performance increases performing asynchronous MySQLi queries.

So who wins?

While I think there are always more important things to do than change database easily having the option is always a benefit, even if it is a very small one. I would think that a 2% performance overhead would be enough to displace this benefit, but when you consider that you might be turning your nose up at 50% performance increases then PDO quickly becomes the loser.

I am curious if there is any evidence to the contrary to my believe that switching DB is uncommon, and perhaps more importantly if I have missed any other benefits that using PDO may have over MySQLi.