Performance analysis of Stored Procedures with PDO and PHP

// Without stored procedures
$time = microtime(TRUE);
$mem = memory_get_usage();

$dsn = 'pgsql:host=localhost;dbname=gonzalo;port=5432';
$user = 'user';
$password = 'password';
$conn = new PDO($dsn, $user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$conn->beginTransaction();
$stmt = $conn->prepare('delete from web.tbltest');
$stmt->execute();

$stmt = $conn->prepare('INSERT INTO web.tbltest (field1) values (?)');
foreach (range(0,1000) as $i) {
    $stmt->execute(array($i));
}
$conn->commit();

print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));

// With stored procedures:
/*
CREATE OR REPLACE FUNCTION web.method1()
  RETURNS numeric AS
$BODY$
BEGIN
   DELETE FROM web.tbltest;
   FOR i IN 0..1000 LOOP
     INSERT INTO web.tbltest (field1) values (i);
   END LOOP;
   RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
*/
$time = microtime(TRUE);
$mem = memory_get_usage();

$dsn = 'pgsql:host=localhost;dbname=gonzalo;port=5432';
$user = 'user';
$password = 'password';
$conn = new PDO($dsn, $user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
$stmt = $conn->prepare('SELECT web.method1()');
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$out = $stmt->fetchAll();
$conn->commit();

print_r(array('memory' => (memory_get_usage() - $mem) / (1024 * 1024), 'seconds' => microtime(TRUE) - $time));

16 thoughts on “Performance analysis of Stored Procedures with PDO and PHP”

1. But remember – stored procedures can be sometimes less effective than plain query. Look at depesz blog:
   http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/ – here is example with prepared statements, but with stored procedures will be the same.

   Reply
   1. Cool post to bear in mind. I will read it in depth

      Reply
2. Is it a fair comparison? delete+inserts vs. updates? I think you should compare the same queries.

   Reply
   1. Sorry. There was a mistake in the post. The first version was only with only a update, but finally I changed it to delete + insert and I forgot to change the code of the pg-plsql code in the comented code of the post (but the real pg-plsql code at the DDBB is with delete + insert). Thanks for your hint, post updated (the outcome remains the same).

      Reply
3. I think it’s worth noting that this is for PGSQL. MySQL does not use true prepared statements via PDO (it emulates them). So I think that’s worth pointing out…

   Reply
   1. Yes. The test has been done with PostgreSQL. Problably it’s extrapolated to Oracle’s plsql. I’ve never used stored procedures with mysql.

      Reply
   2. In PHP 5.3 and PDO with mysqlnd you can use TRUE prepared statements by setting at connection a driver option:

      PDO::ATTR_EMULATE_PREPARES => false

      I used that… and you can really see the difference!

      Reply
      1. That setting is actually available when using LibMySQL with PHP 5.2 (and below). I’m not sure when it was added, but it is available in latest 5.2.x…

      2. You’re right! According to Ulf Wendel (http://blog.ulf-wendel.de/?p=189) they do work but there is still a little star for “libmysql”.

         The PDO::ATTR_EMULATE_PREPARES is a PDO specific attribute it has nothing to do with mysqlnd and/or libmysql… and indded appeared in 5.1.3 (www.php.net/manual/en/pdo.constants.php).

4. I’m not sure this is common task for most of applications. It’s like echo “Hello world!”. While someone taking a long time to configure web server, prepare framework, launch test environment and continuous integration server, other one could code thousand helloworld-like script in likely language. But when we’are talking about flexible, scalable and maintainable applications, various handmade bicycles could be an evil.

   I had experience with storing business logic in SP and can surely say that in large project that’s evil. Of course, may be I’m just can cook them, but I’ve met much troubles in various directions – from IDE to combining data processing from different subsystems.

   And, you can forget about much of mature frameworks goodnesses and rely only on basic data processing. If you’re agree – well, stored procedures are really rapid. If not – man, I surely will not touch it even by 36 meter stick.

   Reply
   1. I know SPs can turn our app into a mess if we spread code within stored procedures and outside them, but they’re useful to reuse business logic in web app and in desktop app for example. I normally prefer to code PHP code than pg/plsql code. I feel more comfortable, but I think they’re not bad as default. I’ve seen horrible things in stored procedures, but also I’ve seen horrible things outside them 🙂

      Reply
      1. Yeah, SP are not bad by default. But I’m not sure someday someone say me “dude, we’ll write PHP+MySQL web app and Java thin client, so we have to put BL to SP”. Well, I’d like to say “what about .NET, WPF or full code in Java?”. Actually, I never mind to use SP as common layer of BL. Speed optimizations – as one of choses – of course. Or may be in small project.

         And I can not trace the craving for stored procedures in enterprise practice.

5. The issue is where you want you complexity to live. If you have high enough performance demands then you do whatever it takes. I wonder if any ORM tools (doctrine or propel) could be extended so that they could generate & use stored procedures. In this way you could keep your business logic managed in the application layer in PHP. I have no idea if this is feasible or possible but it seems interesting.

   Reply
   1. I see SP as another tool we’ve got in our toolbox. We need to balance but they’re not bad as default. IMO the business logic concerning to relational databases is OK inside databases. But I know it’s a debatable opinion

      Reply
6. I believe SP are a great tool, especially if you are trying to squeeze some extra performance out of your database; however, it is one tool out of many. In many cases, what we forget is that we may simply be using the wrong tool altogether. In many cases, we are abusing relational databases when something else may be in order.

   Also, I notice we sometimes use SP when offline processing would have actually been better.

   We also have a tendency to use SP when a web service would have been the better choice to re-use our API across multiple channels.

   It is a simple matter of knowing how to choose our tools in a pragmatic and professional manner 🙂

   I do agree though that we need to not jump to saying that SP are _evil_ as a blanket statement. That is simply wrong. They serve a purpose but they are definitely not a panacea.

   Reply
   1. The silver bullets doesn’t exist within our work. It’s a pitty, I’d like to buy many of them. As you said, nothing is evil by default, except triggers (I hate them 😉 ).

      I like SP when we need to reuse the same business logic. I also like them when app server is located at different host than database server (and sometimes located in different cities). The performance of heavy database operations are really significant between them.

      Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.