In this afternoon, my boss asked me to improving performance of project to release to client before we have Lunar new year holidays. The problem has too many DESCRIBE queries on every page load. I Google-ed & found the Lysender‘s solution, simple & easy to fix this problem:
When you are using Zend_Db_Table (I sometimes only used Zend_Db) you will notice that when you try to retrieve data from the database, the first query called is to DESCRIBE the table. Zend_Db_Table uses the information on DESCRIBE query to do its magic on SELECT.
As I have profiled my queries, I noticed that DESCRIBE query is the longest query (in most cases) which mean a big overhead over you retrieval operation. You have two options:
- Don’t use Zend_Db_Table (go for Zend_Db)
- Cache the MetaData
On this post, I’ll use the caching of MetaData. On your bootstrap file, put this piece of code://Caching $frontendOptions = array( 'lifetime' => 25200, 'automatic_serialization' => true ); $backendOptions = array( 'cache_dir' => APPLICATION_PATH . '/tmp' ); $cache = Zend_Cache::factory( 'Core', 'File', $frontendOptions, $backendOptions ); //Cache table metadata Zend_Db_Table_Abstract::setDefaultMetadataCache($cache);
The code simply instructs Zend_Db_Table_Abstract to cache the result of DESCRIBE statement so that next time you retrieve data, the cache is used instead of repeatedly describing tables over and over again.
(But I noticed that INSERT and UPDATE statements don’t have DESCRIBE…)
After several tries with this caching, you will see files on the tmp folder like this: zend_cache—internal-metadatas—5e7576e3cd79114d46850714e998a3b0.
Still there is an overhead on reading the cache file. If you want better caching, use memory based caching like MemCache.