ZF-5262: Bad performance of Zend_Db_Table_Abstract::find with Oracle (and in general with Zend_Db_Table_Abstract::_fetch)
All fetch's method of Zend_Db_Table_Abstract (fetchAll, fetchRow) don't allow to bind parameters. This is a problem with Oracle (and perhaps with others RDBMS). Example:
// $table is instance of Zend_Db_Table_Abstract $table->find(1); // generate: SELECT foo.* FROM foo WHERE (((foo.bar = '1'))) $table->find(2); // generate: SELECT foo.* FROM foo WHERE (((foo.bar = '2')))
For Oracle, this corresponds to 2 completely differents requests. So it needs 2 parsing and 2 executions.
If we allow parameters binding, we could obtain 1 single request:
SELECT foo.* FROM foo WHERE (((foo.bar = :baz)))
The first time, we have parsing and execution but each next time, we will have just the execution without any parsing.
I think this could be change without any BC breaks. We can change internal process of Zend_Db_Table_Abstract: - the method find() calls fetchAll(). - fetchAll could be called by 2 ways: -- fetchAll($where = null, $order = null, $count = null, $offset = null) -- fetchAll(Zend_Db_Table_Select $where) - in case of second way, the other parameters are ignored ($order, $count, $offset )
My proposition: - during find() method: creation of binding array - end of find() method: creation of Zend_Db_Table_Select instance - and calling of fetchAll like this: fetchAll(Zend_Db_Table_Select $where, $bind) - in fetchAll method, retrieve $bind in case of where is a Zend_Db_Table_Select instance otherwise null - and calling _fetch() with new optional $bind parameter - in _fetch() call db adapter query() method with $bind