Re: [fw-general] Disable prepare statement in Zend_Db_Select
On Jun 8, 2010, at 8:11 AM, Thomas D. wrote: Ryan Chan wrote: After reading the article, I think 'Query cache does not work" is a strong enough reason not to use prepare statement. @Ryan: You need to understand, which prepared statements cannot utilize the query cache. At the time that blog was written (2006-08-02), the GA release of MySQL was 5.0.24. In that release, the query cache would not benefit *any* prepared statement. MySQL 5.1.17 added some more intelligence so prepared statements could benefit from the query cache under compatible conditions, described here: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html Also, some queries can never use the query cache regardless of whether you use prepared statements or whether you execute the query directly. E.g. if it contains references to functions or user variables that might change the result set on successive executions. Regards, Bill Karwin
RE: [fw-general] Disable prepare statement in Zend_Db_Select
Hi, Ryan Chan wrote: > After reading the article, I think 'Query cache does not work" is a > strong enough reason not to use prepare statement. @Bill: Remember our conversation from last month? Quoting such sources, if the reader isn't able to fully understand the content is as dangerous as talking about the overhead and prepared statements ;-) @Ryan: You need to understand, which prepared statements cannot utilize the query cache. There are binary and text statements and there is a different between calling just "prepare()" on a SQL command or on a SQL command, containing placeholders like "?" (=using binded parameters). This "problem" depends also on the used MySQL version. Because Zend_Db* is just calling prepare() and doesn't bind or use placeholders, we can easily say, that Zend_Db* isn't affected by any problems. You can check this too: Flush the query cache and reset the counters. Run a Zend_Db* query twice. Check the counters, the second run should be answered from the query cache. -- Regards, Thomas
Re: [fw-general] Disable prepare statement in Zend_Db_Select
Hi, On Tue, Jun 8, 2010 at 12:47 AM, Bill Karwin wrote: > Keep in mind that the round-trip you fear incurs too much overhead is > actually not always a problem. This article shows that at least in some > cases, a prepared MySQL query actually runs 14% _faster_ than a non-prepared > query: > http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/ > After reading the article, I think 'Query cache does not work" is a strong enough reason not to use prepare statement.
RE: [fw-general] Disable prepare statement in Zend_Db_Select
Hi, Ryan Chan wrote: > I have traced the source code of ZFW, and found the database adapter > Zend_Db_Adapter_Mysqli always do a prepare when execute any SQL. > > However, I found it is not needed, since most of my query only run > once in their life cycle - no reuse is needed. It is possible to > disable auto prepare so it can save a MySQL roundtrip for the prepare > statement? No. It comes from "Zend_Db_Statement_Mysqli". As you can see in the code you have traced - there is currently no "switch". Please overwrite "Zend_Db_Statement_Mysqli::_prepare()" (just replace the method's body with a statement creation) and benchmark you application. You should notice, that the roundtrip hasn't any measurable impact on the whole application performance: If your application is currently not as fast you want, this won't speed it up ;-) -- Regards, Thomas
Re: [fw-general] Disable prepare statement in Zend_Db_Select
There has been a feature request for a long time to provide this alternative, but no one has developed it. http://framework.zend.com/issues/browse/ZF-1398 Keep in mind that the round-trip you fear incurs too much overhead is actually not always a problem. This article shows that at least in some cases, a prepared MySQL query actually runs 14% _faster_ than a non-prepared query: http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/ Don't suppose you have a problem that needs to be solved. First, measure the performance and identify whether you have a problem -- and if so, how bad is the problem. Besides, this gives you the chance to measure again afterwards to be certain that you've mitigated the problem if there is one. If you do need to run a query that doesn't work as a prepared statement (there are still a few even in MySQL 5.1, such as CREATE TRIGGER), you have this workaround: $db = Zend_Db::factory( ... ); $sql = "CREATE TRIGGER ..."; $db->getConnection()->query($sql); You could also use exec() if your Db adapter uses PDO, but query() will work in both PDO and MySQLi. Regards, Bill Karwin On Jun 7, 2010, at 8:07 AM, Ryan Chan wrote: I have traced the source code of ZFW, and found the database adapter Zend_Db_Adapter_Mysqli always do a prepare when execute any SQL. However, I found it is not needed, since most of my query only run once in their life cycle - no reuse is needed. It is possible to disable auto prepare so it can save a MySQL roundtrip for the prepare statement?
