Widget/DB Optimization Design

Yii 1.1 General Discussion
1

Hi,

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.

2931

db_queries.jpg

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?

Thanks,

Matt

2

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

3

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:

                $this->upVoteCount++;

                break;

            case Vote::DIRECTION_DOWN:

                $this->downVoteCount++;

                break;

            default:

                $this->totalVoteCount++;

                break;

        }

    }

}
4

:blink:

5

6

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.

7

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

Matt