Re: [fw-db] prepared statements

Hi,

Regarding caching your query, have you ever thought about using Zend_Cache?

More specifically Zend_Cache_Frontend_Function

http://framework.zend.com/manual/en/zend.cache.frontends.html#zend.cache.frontends.function

Zend_Cache_Frontend_Function caches the results of function calls. It has a single main method named call() which takes a function name and parameters for the call in an array.

You can declare a function that returns your data.

Then you cache this function and that's done!

Zend_Cache allows you to set its expiration time and also to clear a cache (or many)  whenever you want.

http://framework.zend.com/manual/en/zend.cache.theory.html#zend.cache.clean

So, if you have control over the database, you can catch when there is an update and then clear the cache of your function.

I think you are able to cache/clear cache just in your Zend_Db_Table.

Hope it helps. And please let me know if I am right or not.

Best regards,

Matheus Martins

2008/11/6 till <till@php.net>

Hello,

I'm sort of new here and I am asking for some advice.

I'm using Zend_Db_* with the Mysqli adapter and basically my issue is
that I am trying to get every bit of performance in my application
possible. (Along with other things in the framework, ) I've
indentified prepared statements to be a possible bottleneck (figures
from Xdebug). Another side effect of preparing all queries for Mysql
is that the query cache will not be used when you prepare
statements[1]. While I know that the query cache is far from being
perfect, this is a huge drawback for me since in fact the majority of
our database activity is read.

Also, I am aware that prepared statements are used by many people to
not have to quote() data, in Zend_Db's case, the data is quoted and
then prepared through all the convenience methods - e.g. insert(),
delete(), update(), etc..

I don't want to reinvent the wheel or change default behavior, so my
objective is to write "code" that will enable/disable prepared
statements on request. My first attempts all revolve around
Zend_Db_Adapter_Mysqli.

My primary concern is SELECT, I don't mind prepared statements too
much for all data changing queries though the goal in the end is to
turn it off completely.

Questions

1) Do people think this makes sense? ;-)

2) As far as I can tell, I can go about this in two ways: either by
writing a new class to "provide" fetch*() related methods to the
adapter (I'd call it Zend_Db_Adapter_Mysqli_Fetch) or by mocking a
mysqli_stmt class (Zend_Db_Statement_Mysqli_Unprepared). Now either
way doesn't look too great, so I am asking for suggestions

Thinking about it now, there is a third option: I could also do a ton
of if() in Zend_Db_Adapter_Mysqli to achieve the same, but I wanted to
avoid that and "override" using a class instead.

3) Though I know that there are adapters where prepared statements are
always needed, do other people need similar functionality in other
adapters? I must admit I am not so much familiar with internals e.g.
in Postgre, Firebird, etc..

4) I've been thinking of adding setPrepared(bool $use) to the
Zend_Db_Adapter_Abstract. Would that be wise, or should I aim for a
configuration option? Also, should I come up with a solution to
provide a feature such as, "turnItOffOnlyForTheNextOperation()" ? So
it rolls back to prepared statements when that one is through, or
should people call setPrepared(true) again to control this kind of
behaviour?

I can share some code too if people want to see. It's not pretty though. :-)

Thank you for any feedback,
Till

[1]: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html