Performance analysis of Stored Procedures with PDO and PHP


Last week I had an interesting conversation on twitter about the usage of stored procedures in databases. Someone told stored procedure are evil. I’m not agree with that. Stored procedures are a great place to store business logic. In this post I’m going to test the performance of a small piece of code using stored procedures and using only PHP code.

Without stored procedures

// 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:

// 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));
without stored procedures with stored procedures
memory: 0.0023880004882812
seconds: 0.31109309196472
memory: 0.0020713806152344
seconds: 0.065021991729736

So my conclusion: Stored procedures are not evil. The performance is really good. I know maybe it can be a bit mess if we place business logic within database and outside database at the same time, but with a good design and architecture this problem is easy to solve. What do you think?

About these ads

About Gonzalo Ayuso

Web Architect specialized in Open Source technologies. PHP, Python, JQuery, Dojo, PostgreSQL, CouchDB and node.js but always learning.

Posted on May 2, 2011, in databases, PDO, PostgreSQL, Technology and tagged , , , . Bookmark the permalink. 16 Comments.

  1. Daniel Podlejski

    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.

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

    • Gonzalo Ayuso

      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).

  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…

    • Gonzalo Ayuso

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

    • 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!

      • 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…

      • 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.

    • Gonzalo Ayuso

      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 :)

      • 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.

    • Gonzalo Ayuso

      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

  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.

    • Gonzalo Ayuso

      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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 949 other followers

%d bloggers like this: