Widget/DB Optimization Design


I’m trying to find a design that allows me to make a single DB query for a list of widgets - to increase performance. These widgets might be changing rapidly so I don’t want to use caching. The main goal is to delegate DB queries to a marshal object. The issue is delaying the marshal/DB queries until all widget instances have been loaded, so I know which IDs to query.

Originally, each widget was calling 3 queries to find counts of Votes - upVoteCount, downVoteCount, and totalVoteCount. 5 voting stations/page @ 3 queries each = 15 DB queries!

I’ve refactored it to make a single DB call per widget and calculate the vote counts server side. 5 voting stations/page @ 1 query = 5 DB queries. I still think that’s too high.



My first idea is to

[list=1][]disable the widget instances as they’re called[]add them to a list from their init method[]perform a single query based on their properties[]create and populate an object (Information Marshal?) with values[*]re-enable and run the widgets - they can then access the retrieved data[/list]

Can anybody shed some light on how to disable/enable the widgets or a similar technique?



Hm, how did you go about "redesigning" those widgets? Have you used eager loading?

No - just refactored it like so

public function loadData()


    $votes = Vote::model()->findAll('model=:modelName && model_id=:modelId', array(

        ':modelName' => $this->candidateName,

        ':modelId' => $this->candidateId));

    $this->upVoteCount = 0;

    $this->downVoteCount = 0;

    $this->totalVoteCount = 0;

    foreach ($votes as $vote)


        switch ($vote->direction)


            case Vote::DIRECTION_UP:



            case Vote::DIRECTION_DOWN:










Let me put it like this: You’re optimizing at the wrong end. Search the Yii guide for the following terms: Statistical queries and eager loading. And I’m pretty sure you’ll get a fine CDbCacheDependency out of the total number of votes.

Thanks, I’ll take a look at CDBCacheDependency and post back some results.