Lots of DESCRIBE queries in Zend Framework

Question

I just set up FirePHP in Zend and I'm noticing a huge number of DESCRIBE queries. Some pages have 50 or more identical queries all on the same table. e.g.

0.00198     connect      NULL
0.00449 DESCRIBE `nodes`    NULL
0.00041 SELECT `nodes`.* FROM `nodes` WHERE (((`nodes`.`id` = 111)))    NULL
0.0037  DESCRIBE `nodes`    NULL
0.00155 SELECT `nodes`.* FROM `nodes` WHERE (((`nodes`.`id` = 111)))    NULL
0.00059 SELECT `nodes`.* FROM `nodes` WHERE (parent_id = '111') ORDER BY `order` ASC, `id` ASC  NULL
0.00366 DESCRIBE `nodes`    NULL
0.0054  DESCRIBE `nodes`    NULL
0.0049  DESCRIBE `nodes`    NULL
0.00519 DESCRIBE `nodes`    NULL
0.00492 DESCRIBE `nodes`    NULL
0.00691 DESCRIBE `nodes`    NULL
0.00741 DESCRIBE `nodes`    NULL
0.0048  DESCRIBE `nodes`    NULL
0.00556 DESCRIBE `nodes`    NULL
0.00516 DESCRIBE `nodes`    NULL
0.00487 DESCRIBE `nodes`    NULL

...and it goes on.

Are all those DESCRIBE queries generated by the framework (I'm using Zend_DbTable)? Are they all necessary? Should I be worried about them or are they not likely to be impacting performance?

Answer 1

Zend_Db_Adapter_Abstract::describeTable() does these queries to get the Metadata of your tables when using Zend_Db_Table This is used for instance when you do not specify a primary key explicitly. You can enable the MetaData cache or just use Zend_Db instead of Zend_Db_Table.

I think you should not have this many describe queries though. Once a Zend_Db_Table instance is set up, it will store the metadata after the first query for the remaining request. Try to use Zend_Debugger or Xdebug to find out what's causing this.

See

• ZF API entry for Zend_Db_Adapter_Abstact::describeTable()
• ZF manual entry for List and Describe Tables in Zend_Db_Adapter
• ZF manual entry for Zend_Db_Table with additional background info on metadata

Answer 2

Those queries are executed by Zend_Db_Table to detect the schema of the tables. You can ask Zend_Db_Table to cache the results using a Zend_Cache to prevent constant calls, but bare that in mind if you change the schema.

You can do so by using:

Zend_Db_Table_Abstract::setDefaultMetadataCache($cache);

Answer 3

I used a singleton pattern to store the Zend_DbTable instances in a static array on my base model class. This reduces the DB queries to one per request which is good enough for me and also reduces the number of objects which need to be instantiated.

For example:

protected $_dbTable;
protected $_table; //override the database table name in subclass

private static $_dbTableCache = array();

public function __construct()
{
    $this->_dbTable = $this->getDbTableInstance($this->_table);
}

protected function getDbTableInstance($tableName) {
    if (self::$_dbTableCache[$tableName] === null) {
        self::$_dbTableCache[$tableName] = new Zend_Db_Table($tableName);
    }
    return self::$_dbTableCache[$tableName];
}