Zend_Db表演

Question

After doing some research on differents libraries/framework to implement in a pretty big application (350 tables db, several millions entries on some tables) I found out that Zend_Db do pretty easily what I want to do: Adapter management for quick switch between databases.

The problem is that performances are really low, here's an example ($db is a basic adapter, the time is computed on the select/fetch only) :

SQL QUERY (the query used for testing, the table contains ~200 elements)

SELECT * FROM element WHERE id=2'

Basic PDO - 0.6392s

$db = new PDO('mysql:dbname=etab_191;host=127.0.0.1', 'root');
for ($i=0; $i<2000; $i++) {
    $stmt = $db->query($sql);
    $p = $stmt->fetch(PDO::FETCH_OBJ);
    $stmt->closeCursor();
}

Current Application database manager - 0.7401s (simple abstraction layer over mysqli core functions)

$db = GestionConnexionBDD::getInstance('default', 1)->gestionBDD;
for ($i=0; $i<2000; $i++) {
    $res = $db->query($sql);
    $p = $db->fetchObject($res);
    $db->freeResult($res);
}

Zend_Db manual query - 1.0647s (Mv_Core_Db_Manager is an abstraction layer based on Zend, returning a list of Zend_Db_Adapter_Abstract)

    $db = Mv_Core_Db_Manager::getInstance()->getAdapter('default', 1);
for ($i=0; $i<2000; $i++) {
    $stmt = $db->query($sql);
    $p = $stmt->fetch();
    $stmt->closeCursor();
}

Zend_Db_Table_Abstract query - 3.6702s (tweaked with Zend_Db_Table_Abstract::setDefaultMetadataCache($cache))

$elmt = new Element();
for ($i=0; $i<2000; $i++) {
    $elmt->find(2);
}

Querying on a loop kills zend performances. I know it's not the best thing to do, but the application is already developed and i'd love to change less code possible.

Some ideas ? Am i doing something wrong ?

Answer 1

Zend_DB_Abstract will query table metadata on each PHP request.

This means doing a DB DESCRIBE TABLE, that can be very slow on some databases.

To avoid this, you can cache such metadata, and this will improve query performance:

    /////////////////////////////
    // getting a Zend_Cache_Core object
    $cache = Zend_Cache::factory('Core',
        'File',
        array('lifetime' => 86400, 'automatic_serialization' => true ),
        array('cache_dir' => $config->cacheDir));

    // Next, set the cache to be used with all table objects
    Zend_Db_Table_Abstract::setDefaultMetadataCache($cache);