Zend_Db_Table time overhead – about 25%

Here I will represent the results of a test, that I ran recently to determine how much time overhead does Zend_Db_Table brings to typical web application. The details follow, but the rough results are that it took about 25% more time for the test cases with Zend_Db_Table to run compared to using directly Zend_Db_Adapter.

My intend was to test this in as close to live enviroment as possible, so the test case that I ran was standart MVC architecture. We have some overhead also from the setup of the front controller and the dispatch process, loading configuration files, setting up Zend_Translate, loading some simple front controller plugin. The only thing that I have disabled is the layout and the viewRenderer action helpers by running

$this->_helper->layout->disableLayout();
$this->_helper->viewRenderer->setNoRender();

in the init() function of my test controller.

I’m running this with PHP 5.2.6 with APC, Zend Framework 1.5.2, MySQL 5.0.67, MySQLi DB connection adapter.

I have the following DB structure:
- table `users` with standart fields for this – username, password, email address, birthday, real name etc, filled in with 2000 users
- table `posts` with fields: id (primary key, autoincrement), userId (int), title (varchar 64), content (text) with index on userId column – filled in with 20000 posts with random generated text and random generated titles. Text is between 250 and 3000 bytes long.
- table `tags` with fields: id (primary key, autoincrement), tag (varchar 32) with index on tag column. Here we have 35000 tags, each tag is md5 hash of some value, so it is 32 bytes long.
- table `tag2posts` which links the two tables – tags and posts. So we have many-to-many relationship between these two. Fields: tagId (int) and postId (int). Here the primary key is on two columns (tagId, postId), and we have extra index on postId column, because we will search “give me posts for this tag” queries. Each post is linked with between 3 and 7 tags. The size of this table is 99896 rows.

My test case does the following actions:
1) Generates name of a test user. All test users have usernames in the form (test_xxx) where xxx is number between 1 and 2000. Then loads all the info about this user.
2) Loads last 5 post titles from the selected user.
3) Random selects one of these posts and loads it – with the text and all linked tags id. (Without loading the actual tag text, as I found later, but this is not crucial for the result of all this)
4) Random selectes one of the tags for this post and loads the titles of the last 20 posts tagged with it.
5) Prints these titles on the screen

I ran the test in 4 different variants. These 4 variants proved to distinguish one from another in the time they took to run.
These 4 variants are:
1) using Zend_Db_Table without metadata cache
2) using Zend_Db_Table with APC as metadata cache
3) writing the SQL queries directly in the controller, using direct Zend_Db_Adapter_Mysqli
4) writing the SQL queries in Model classes, using direct Zend_Db_Adapter_Mysqli

Fastest method is the 3rd one – where we write the SQL queries directly in the controller. This is not good practice and is error prone. The second place with only small difference is for the 4th method – where we hide the queries in Model classes, but do not use Zend_Db_Table. As for the last 2 places – the performance here drops significantly. When we use APC as metadata cache for Zend_Db_Table compared to the case when we do not use metadata cache at all the difference is about 10%.

I ran several times the benchmark about each of the methods, removed the slowest runs and averaged the other numbers to get the final results. Here are the exact numbers:

I tested with level of concurency 5 with 100 requests (ab -c 5 -n 100)
Requests per second:
method 3 – writing the SQL queries directly in the controller, using direct Zend_Db_Adapter_Mysqli: 12.93 rps
method 4 – writing the SQL queries in Model classes, using direct Zend_Db_Adapter_Mysqli: 12.66 rps
method 2 – using Zend_Db_Table with APC as metadata cache: 10.2 rps
method 1 – using Zend_Db_Table without metadata cache 9.2 rps

here is the code of the test controller class:

< ?php
 
class TestController extends Zend_Controller_Action 
{
    /**
     * DB Connection
     *
     * @var Zend_Db_Adapter_Abstract
     */
    protected $db;
 
    protected $cache;
 
    public function init()
    {
        $this->db = Zend_Registry::get('db');
        $this->_helper->layout->disableLayout();
        $this->_helper->viewRenderer->setNoRender();
 
        //setup cache for DB metadata:
 
        // First, set up the Cache
        $frontendOptions = array(
            'automatic_serialization' => true
            );
 
        $backendOptions  = array();
 
        $this->cache = Zend_Cache::factory('Core', 'APC', $frontendOptions, $backendOptions);
        //$this->cache->setOption('caching', false);
    }
 
	public function indexAction()
    {
 
    }
 
    public function generatetestdbAction()
    {
        //$this->insertTags();
        //$this->insertUsers();
        //$this->insertPosts();
        //$this->linkPostsToTags();
    }
 
    public function runtestcaseAction()
    {
//1) Generates name of a test user. All test users have usernames in the form (test_xxx) where xxx is number between 1 and 2000. Then loads all the info about this user.
//2) Loads last 5 post titles from the selected user. 
//3) Random selects one of these posts and loads it - with the text and all linked tags id. (Without loading the actual tag text, as I found later, but this is not crucial for the result of all this)
//4) Random selectes one of the tags for this post and loads the titles of the last 20 posts tagged with it. 
//5) Prints these titles on the screen
//        
        $mode = $this->getRequest()->getParam('mode');
 
        if (!isset($mode)) $mode = 1;
 
        $startUser = 'test_' . mt_rand(1, 2000);
 
        if ($mode == 1)
        {
            //mode 1 is with Zend_Db_Table
            //$users = new Users();
            $users = new Users(array('metadataCache' => $this->cache));
 
            $u = $users->selectUserByUsername($startUser);
            if (!$u) throw new My_Exception('not such start user');
 
            //load last 5 post titles for $u
            //$posts = new Posts();
            $posts = new Posts(array('metadataCache' => $this->cache));
 
            $select = $posts->select();
            $select->from($posts, array('id', 'title'))
                //->where($posts->getAdapter()->quoteInto('userId = ?', $u->id))
                ->where('userId = ?', $u->id)
                ->order('id DESC')
                ->limit(5);
 
            $rows = $posts->fetchAll($select);
 
            //load full info for random post of these:
            $cnt = $rows->count();
            $rows->seek(mt_rand(0, $cnt-1));
            $p = $rows->current();
            $p = $posts->find($p->id);            
            $p = $p->current();                                    
 
            //$t2p = new Tag2Posts();
            $t2p = new Tag2Posts(array('metadataCache' => $this->cache));
            $select = $t2p->select();
            //$select->where($t2p->getAdapter()->quoteInto('postId = ?', $p->id));
            $select->where('postId = ?', $p->id);
            $rows = $t2p->fetchAll($select);
 
            $cnt = $rows->count();
            $rows->seek(mt_rand(0, $cnt-1));
            $tag = $rows->current();
            $tagId = $tag->tagId;
 
            $select = $posts->select();
            $select->from($posts, array('id', 'title'))
                ->join('tag2posts', 'tag2posts.postId=posts.id', array())
                ->where('tag2posts.tagId = ?', $tagId)
                ->order('id DESC')
                ->limit(20);
 
            $rows = $posts->fetchAll($select);
 
            foreach ($rows as $r)
            {
                echo $r->title . "<br />";
            }
        }
        else if ($mode == 2)
        {
            $stmt = $this->db->query("SELECT * FROM users WHERE username = ?", $startUser);
            $u = $stmt->fetch();            
            if (!$u) throw new My_Exception('not such start user');
 
            //load last 5 post titles for $u
            $stmt = $this->db->query("SELECT id, title FROM posts WHERE userId = ? ORDER BY id DESC LIMIT 5", $u['id']);
            $rows = $stmt->fetchAll();                
 
 
            //load full info for random post of these:            
            $key = array_rand($rows);
            $p = $rows[$key];
            $stmt = $this->db->query("SELECT * FROM posts WHERE id = ?", $p['id']);
            $p = $stmt->fetch();                                                
 
            $stmt = $this->db->query("SELECT * FROM tag2posts WHERE postId = ?", $p['id']);
            $rows = $stmt->fetchAll();
 
            $key = array_rand($rows);
            $tag = $rows[$key];
            $tagId = $tag['tagId'];
 
            $stmt = $this->db->query("SELECT id, title FROM posts JOIN tag2posts t2p ON t2p.postId=posts.id " . 
                "WHERE t2p.tagId = ? ORDER BY id DESC LIMIT 20", $tagId);
 
            $rows = $stmt->fetchAll();
 
            foreach ($rows as $r)
            {
                echo $r['title'] . "<br />";
            }                        
        }
        else
        {
            $users = new Users2();
            $u = $users->selectUserByUsername($startUser);
            if (!$u) throw new My_Exception('not such start user');
 
            //load last 5 post titles for $u
            $posts = new Posts2();
            $rows = $posts->getLastPostsForUser($u['id'], 5);
 
            //load full info for random post of these:
            $key = array_rand($rows);
            $p = $rows[$key];
            $p = $posts->getPostById($p['id']);
 
            $tags = $posts->getTagsForPost($p['id']);
            $key = array_rand($tags);
            $tag = $tags[$key];
 
            $rows = $posts->getLastPostsForTag($tag['tagId'], 20); 
 
            foreach ($rows as $r)
            {
                echo $r['title'] . "<br />";
            }
        }
    }
 
    protected function insertTags() {
        //generate 35000 tags
        $this->db->query("TRUNCATE TABLE tags");
        $stmt = $this->db->prepare("INSERT INTO tags SET tag = ?");
        $stmt->bindParam(1, $newTag);
        for ($i = 1; $i < = 35000; $i++)
        {
            $newTag = md5(microtime());
            $stmt->execute();                        
        }
 
        echo '35000 tags inserted';
    }
 
    protected function insertUsers()
    {
        //insert 2000 users
        $this->db->query("DELETE FROM users WHERE username LIKE ?", array(1=>'test_%'));
        $stmt = $this->db->prepare("INSERT INTO users SET username = ?, password = ?, email = ?, active = 1");
        $stmt->bindParam(1, $username);
        $stmt->bindParam(2, $password);
        $stmt->bindParam(3, $email);
 
        $password = Users::computePasswordHash('123456');
        for ($i = 1; $i < = 2000; $i++)
        {
            $username = 'test_'.$i;
            $email = 'test_'.$i.'@example.com';
 
            $stmt->execute();
        }
 
        echo '2000 users inserted';
    }
 
    protected function insertPosts()
    {
        //insert 20000 posts 
        //$this->db->query("TRUNCATE TABLE posts");
        //$this->db->query("TRUNCATE TABLE tag2posts");
 
        $stmt = $this->db->prepare("INSERT INTO posts SET userId = ?, title = ?, content = ?");
        $stmt->bindParam(1, $userId);
        $stmt->bindParam(2, $title);
        $stmt->bindParam(3, $content);
 
        //@todo: increase your time limit, because this one may take some time
        for ($i = 1; $i < = 20000; $i++)
        {
            $userId = mt_rand(16, 2015);
            $title = $this->getRandomText(rand(30, 60));
            $content = $this->getRandomText(rand(250, 3000));
 
            $stmt->execute();
        }
 
        echo '20000 texts inserted';                
    }
 
    protected function linkPostsToTags()
    {
        //link each post to about 5 tags
        //posts are from 1 to 20000
        //tags are from 1 to 35000
 
        $stmt = $this->db->prepare("INSERT INTO tag2posts SET tagId = ?, postId = ?");
        $stmt->bindParam(1, $tagId);
        $stmt->bindParam(2, $postId);
 
        for ($i = 1; $i < = 20000; $i++)
        {
            $postId = $i;
 
            //@todo: better testing for duplicate tag id should be implemented. 
            $numTags = mt_rand(3, 7);            
            for ($j = 1; $j <= $numTags; $j++)
            {
                do
                {
                    $newTagId = mt_rand(1, 35000);                
                }
                while ($newTagId == $tagId);
                $tagId = $newTagId;
 
                $tagId = mt_rand(1, 35000);
                $stmt->execute();
            }            
        }
 
        echo 'tags links inserted';
 
 
    }
 
    public static function getRandomText($numCharacters)
    {
        //algorithm: generate words (2 to 15 characters long) then space until $numCharacters is reached
        $text = '';
        while ($numCharacters > 0)
        {
            $currentWord = '';
 
            $charset = "abcdefghijklmnopqrstuvwxyz";
            $chl = strlen($charset)-1;
 
            $length = mt_rand(2, 15);
            for ($i=0; $i< $length; $i++) $currentWord .= $charset[(mt_rand(0,$chl))];            
 
            $text .= $currentWord;
            $text .= ' ';
            $numCharacters -= ($length + 1);
        }
 
        return $text;
    }
 
 
 
}

Classes Users, Posts and Tag2Posts are very simple, they just extends Zend_Db_Table_Abstract and define the $_name property. For variant 4 – here are example classes Users2 and Posts2:

< ?php
 
class Users2
{
    protected $db;
 
    public function __construct()
    {
        $this->db = Zend_Registry::get('db');
    }
 
    public function selectUserByUsername($username)
    {
        $stmt = $this->db->query("SELECT * FROM users WHERE username = ?", $username);
        $u = $stmt->fetch();            
        return $u;
    }
}
< ?php
 
class Posts2 
{
    protected $db;
 
    public function __construct()
    {
        $this->db = Zend_Registry::get('db');
    }
 
 
    public function getPostById($postId)
    {
        $stmt = $this->db->query("SELECT * FROM posts WHERE id = ?", $postId);
        $p = $stmt->fetch();
 
        return $p;
    }
 
    public function getTagsForPost($postId)
    {
        $stmt = $this->db->query("SELECT * FROM tag2posts WHERE postId = ?", $postId);
        $rows = $stmt->fetchAll();
        return $rows;
    }
 
    public function getLastPostsForUser($userId, $num)
    {
        $stmt = $this->db->query("SELECT id, title FROM posts WHERE userId = ? ORDER BY id DESC LIMIT 5", $userId);
        $rows = $stmt->fetchAll();
        return $rows;
    }
 
    public function getLastPostsForTag($tagId, $num)
    {
        $num = (int) $num;
        $stmt = $this->db->query("SELECT id, title FROM posts JOIN tag2posts t2p ON t2p.postId=posts.id " . 
                "WHERE t2p.tagId = ? ORDER BY id DESC LIMIT $num", $tagId);
 
        $rows = $stmt->fetchAll();
        return $rows;
    }
}

conclusion: Zend_Db_Table is not good for heavy loaded web applications. My personal choice is to go with something like variant 4. The time overhead here is minimal and the code in the controller is most readable.

Be Sociable, Share!