Thread: pdo emulate prepares not working. still getting parse-bind-execute-deallocate in pg log.

Hi all,


I am trying to convince some of our php guys to not use prepared statements.


The reason for this is, first reasons is we are seeing no benefit IMO.
performance wise nearly all of the statements are being prepared, executed
and then deallocated in nearly all cases. The other times it's probably only
run twice so it's still not a win.

The second reason is I want to be able to use transaction pooling with
pgbouncer. This is the primary driving force.


So someone mashed up a quick bit of php to start testing a few thing. (more
about checking what error cases look like than anything else)

The problem we found is that even with "PDO::ATTR_EMULATE_PREPARES => true"
I still see it preparing statements when I set log_min_duration to 0.

I am not a php person but I googled around and I can't seem to figure out
why the below isn't working as expected. Any tips on what are we doing
wrong? What should this look like to disable/emulate prepared statements. I
am expecting that if this were working we wouldn't see
parse,bind,execute,deallocate in the Postgres log.


Apologies if this is the wrong list for this sort of thing.



-Mark

<?php
try {

    // Can run it in a loop to get timed results over a large number of
executions.
    $tStart = microtime(true);
    for ($i=0;$i<1;$i++) {

        $dbh = new PDO('pgsql:host=localhost;dbname=my_mainline',
'postgres', 'postgres', array(PDO::ATTR_EMULATE_PREPARES => true));

        // replace this with a valid int to test the expected case.
        $rname = 'P1\'Partner';

        // Purposely trying to bind a string to an integer column to
see what happens
        // when using client-side prepare emulation vs not using it.
        $stmt = $dbh->prepare("SELECT * FROM my_reseller WHERE
reseller_id=:rname");
        $stmt->bindParam(':rname', $rname);
        $stmt->execute();

        /* Print out results if you want to see them.
        foreach($stmt->fetch() as $row) {
            print_r($row); print("\n");
        }
        */

        $dbh = null;
    }
    printf("%17s: %.4f secs for %d iterations, avg: %.6f secs\n"
        , 'date'
        , microtime(true) - $tStart
        , $i
        , (microtime(true) - $tStart)/$i
        );
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Am 26.08.2011 05:30, schrieb mark:
>
>
> Hi all,
>
>
> I am trying to convince some of our php guys to not use prepared statements.
>
>
> The reason for this is, first reasons is we are seeing no benefit IMO.
> performance wise nearly all of the statements are being prepared, executed
> and then deallocated in nearly all cases. The other times it's probably only
> run twice so it's still not a win.
>
> The second reason is I want to be able to use transaction pooling with
> pgbouncer. This is the primary driving force.
--snip--

Well, in my systems I make the best of the forced prepare and use
my own cache of prepared statements in PHP. Statistics show that
some statements are repeated a lot, which might give a benefit in
speed.

And I am especially against connection pooling because it can
give you untraceable errors and just makes your systems more complex.

But if you want it: my first test had success. Just add

   $DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

after creating the connection, do not touch the connection string.

/Str