[waterloomatt]

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.

 db_queries.jpg (167.03K)
Number of downloads: 11

My first idea is to

• 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

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

Thanks,

Matt

[Da:Sourcerer]

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

[waterloomatt]

Da:Sourcerer, on 04 June 2012 - 10:45 AM, said:

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:
                $this->upVoteCount++;
                break;
            case Vote::DIRECTION_DOWN:
                $this->downVoteCount++;
                break;
            default:
                $this->totalVoteCount++;
                break;
        }
    }
}

[Da:Sourcerer]

[waterloomatt]

[Da:Sourcerer]

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.

[waterloomatt]

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

Matt