Re: [PDO] Client side cache for prepared queries

On Tue, Aug 2, 2011 at 5:26 PM, Lester Caine <[email protected]> wrote:
> Julien Palard wrote:
>>
>> On Tue, Aug 2, 2011 at 3:17 PM, Ferenc Kovacs<[email protected]> =C2=A0wr=
ote:
>>>
>>> if you can use persistent connection, the performance gain will
>>> accumulate between your requests.
>>
>> If and only if the undocumented client side cache really exists, if
>> calling pdo::prepare create a new one each time, there are no
>> performance gain at all albeit persistent connection are used, maybe
>> should I search in the source code to confirm it :P
>
> The base premiss of PDO is that all the core functions work identically f=
or
> any database. MySQL is coming late into the game on prepared queries, and
> other databases do things differently,

"Support for server-side prepared statements was added in MySQL 4.1"
that was released in 2004 October,
PDO was around 0.1.1 beta, PDO_MYSQL at 0.1 alpha at that time, so
getting into the game is irrevelant for PDO imo.

> so if you want MySQL type operation,
> then it may be better to switch to the generic driver? In the case of
> Firebird and Oracle, the preparation has to be done on the at the server
> end, since only the parameters will be updated later, and the query has t=
o
> be handled in the context of a transaction,

mysql_stmt_prepare/mysql_stmt_execute also works that way, or did I
misunderstood something?

> so actually making some of this
> persistent at all causes problems when many users are holding transaction=
s
> open without any end in sight. While the connection can be held persisten=
t,
> holding a transaction open is a no-no, and so anything prepared within th=
at
> transaction also has to be closed.

thats acceptable, http://www.php.net/manual/en/pdo.transactions.php
also mentions that active transactions will be rolled back at the end
of the script execution.
and there was a good sum-up from Christopher Jones:
http://news.php.net/php.pdo/300

but you can still lock out yourself from a table(Locking a table from
a persistent connection then forgot to unlock before the end of the
execution), or you can have temporary tables lingering, or if you
change some mysql session variable, and forget that it will be a
permanent change for that permanent connection, etc.
so rollbacking the transaction fixes only just a small percent of the
problems that the persistent connections can cause in the wrong hands.

to properly reset a re-used mysql connection is calling the
mysql_change_user(), as it is also done in mysqli
http://php.net/manual/en/mysqli.persistconns.php
AFAIK we don't do this for ext/mysql or PDO.
if we would, the whole topic would be moot, as the prepared statements
are also released if  mysql_change_user() is called:

"This command resets the state as if one had done a new connect. (See
Section 20.9.11, =E2=80=9CControlling Automatic Reconnection Behavior=E2=80=
=9D.) It
always performs a ROLLBACK of any active transactions, closes and
drops all temporary tables, and unlocks all locked tables. Session
system variables are reset to the values of the corresponding global
system variables. Prepared statements are released and HANDLER
variables are closed. Locks acquired with GET_LOCK() are released.
These effects occur even if the user didn't change."
http://dev.mysql.com/doc/refman/5.1/en/mysql-change-user.html

> In these cases we rely on the internal
> caching within the database engine to speed things up, and asking the sam=
e
> query will pull data from memory normally, but with a current view based =
on
> what has happened in other transactions since.
>

yeah, usually running the same query multiple times is fast, either if
the db has some kind of cache like the query cache in mysql, or
expecting that the affected tables/rows are still in memory, as they
were used recently.
but this has nothing to do with prepared statements, but maybe it can
be a reason why was the emulated parameter binding used instead of the
native mysql prepared statements.

--=20
Ferenc Kov=C3=A1cs
@Tyr43l - http://tyrael.hu