Issues

ZF-5262: Bad performance of Zend_Db_Table_Abstract::find with Oracle (and in general with Zend_Db_Table_Abstract::_fetch)

Issue Type: Improvement Created: 2008-12-15T02:27:56.000+0000 Last Updated: 2012-11-20T20:52:43.000+0000 Status: Closed Fix version(s): Reporter: Mickael Perraud (mikaelkael) Assignee: None Tags: - Zend_Db_Table

Related issues: - ZF-6463

Attachments:

Description

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:

<pre class="highlight">
// $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:

<pre class="highlight">
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

Comments

Posted by Rob Allen (rob) on 2012-11-20T20:52:43.000+0000

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts