In this afternoon, my boss asked me to improve performance of project to release to client before Lunar new year holidays. The problem is: we have too many DESCRIBE query 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.