Everything

php

PDO questions

php.internals

Ron Korving

20 years ago

After reading the php|architect article on PDO by Ilia I have a couple of
questions:

1) Does unsetting $stmt trigger a closeCursor() as well? I assume so, but
the article doesn't mention it. It only talks about the importance of
calling that method.
2) Could it be a good idea (performance wise) and possible in the first
place to maintain a pool of prepared statements? Something like this:

class MyClass
{
private $stmts = array();

public function __construct($db)
{
$this->db = $db;
}

public function getInfo()
{
if (!isset($this->stmts["info"])) $this->stmts["info"] =
$this->db->prepare("select .........");

$this->stmts["info"]->execute(array("foo", "bar"));
...
}
}

3) Wouldn't it be nice if you could do a $stmt->execute("foo", "bar");
(numeric parameters) instead of $stmt->execute(array("foo", "bar")); ?

Nice article by the way, Ilia, although I think the php|arch editor(s)
really could do a better job. I wrote an article for them myself on SOAP a
while ago, and I can't say I was too satisfied on the job the editor did on
it.

Ron

Lukas Smith

20 years ago

Ron Korving wrote:

> 1) Does unsetting $stmt trigger a closeCursor() as well? I assume so, but
> the article doesn't mention it. It only talks about the importance of
> calling that method.

yes this is done automatically ..

> 2) Could it be a good idea (performance wise) and possible in the first
> place to maintain a pool of prepared statements? Something like this:

This should be done on the RDBMS end. I havent looked at how PDO does
this in detail, but it would be nice to get some more control over this.
Some RDBMS will pool this automatically, some do not (AFAIK pgsql will
always create a new statement instead of returning a handle to an
existing statement). There is a reason why you may want more control
over this. One of the key aspects of a prepared statement is that you
safe time due to not having to build a query plan on every execution.
This may bite you however if you prepare a statement and then things
change within the database that would make it more feasible to use a new
plan.

> 3) Wouldn't it be nice if you could do a $stmt->execute("foo", "bar");
> (numeric parameters) instead of $stmt->execute(array("foo", "bar")); ?

I think this would severely hurt the extensibility of the API not only
for PDO itself, but also for people who want to transparently extend the
API.

regards,
Lukas

Lukas Smith

20 years ago

Lukas Smith wrote:

>> 3) Wouldn't it be nice if you could do a $stmt->execute("foo", "bar");
>> (numeric parameters) instead of $stmt->execute(array("foo", "bar")); ?
>
>
> I think this would severely hurt the extensibility of the API not only
> for PDO itself, but also for people who want to transparently extend the
> API.

Also note your suggestion would only work for ? placeholders and not for
:foo style placeholders (which I would generally recommend for
readability reasons).

regards,
Lukas

Ron Korving

20 years ago

> Ron Korving wrote:
>
> > 1) Does unsetting $stmt trigger a closeCursor() as well? I assume so,

but

> > the article doesn't mention it. It only talks about the importance of
> > calling that method.
>
> yes this is done automatically ..

Great :)

> > 2) Could it be a good idea (performance wise) and possible in the first
> > place to maintain a pool of prepared statements? Something like this:
>
> This should be done on the RDBMS end. I havent looked at how PDO does
> this in detail, but it would be nice to get some more control over this.
>   Some RDBMS will pool this automatically, some do not (AFAIK pgsql will
> always create a new statement instead of returning a handle to an
> existing statement). There is a reason why you may want more control
> over this. One of the key aspects of a prepared statement is that you
> safe time due to not having to build a query plan on every execution.
> This may bite you however if you prepare a statement and then things
> change within the database that would make it more feasible to use a new
> plan.

It would be very nice if this was indeed done by the RDBMS. That would mean
you don't have to "remember" any prepared statements yourself and would
definately benefit the performance a great deal.

> > 3) Wouldn't it be nice if you could do a $stmt->execute("foo", "bar");
> > (numeric parameters) instead of $stmt->execute(array("foo", "bar")); ?
>
> I think this would severely hurt the extensibility of the API not only
> for PDO itself, but also for people who want to transparently extend the
> API.

Good point, I take that back.

> regards,
> Lukas

Thanks,

Ron


________________________________________
Scanned and protected by SecureMail v2.2
http://www.axit.nl

Wez Furlong

20 years ago

I plan to add some kind of prepared statement caching in a (not too
distant) future PDO release, however...

You'd only benefit from prepared statement caching when the underlying
driver supports it.
Not only does it require support from the driver, but it also requires
good, solid support for it to be worthwhile.

Taking postgres as an example, it's prepared statement implementation
has some issues with query planning that might make this an
unnatractive prospect--to the point where the postgres driver has a
flag to disable native prepared statements for those cases where it
makes a difference.

Caching PDO emulated prepared statements will have negligible benefit.

Just like any feature, you'll need to evaluate whether it's use will
make sense for your application.

--Wez.

On 9/22/05, Ron Korving <r.korving@xit.nl> wrote: