Why is Yii2's ActiveRecord using lots of single SELECTs instead of JOINs?

Question

I'm using Yii2's ActiveRecord implementation in (hopefully) exactly the way it should be used, according to the docs.

Problem

In a quite simple setup with simple relations betweens the tables, fetching 10 results is fast, 100 is slow. 1000 is impossible. The database is extremely small and indexed perfectly. The problem is definitly Yii2's way to request data, not the db itself.

I'm using a standard ActiveDataProvider like:

$provider = new ActiveDataProvider([
    'query' => Post::find(),
    'pagination' => false // to get all records
]);

What I suspect

Debugging with the Yii2 toolbar showed thousands of single SELECTs for a simple request that should just get 50 rows from table A with some simple "JOINs" to table B to table C. In plain SQL everybody would solve this with one SQL statement and two joins. Yii2 however fires a SELECT for every relation in every row (which makes sense to keep the ORM clean). Resulting in (more or less) 1 * 50 * 30 = 1500 queries for just getting two relations of each row.

Question

Why is Yii2 using so many single SELECTs, or is this a mistake on my side ? Addionally, does anybody know how to "fix" this ?

As this is a very important issue for me I'll provide 500 bounty on May 14th.

Could you add the code where you are doing the select and accessing related data? You will get better answers with it. — topher
– topher, Commented May 12, 2015 at 15:56
@topher There is no real select, I'm using a standard activeDataProvider. I'll add this to the question. — Sliq
– Sliq, Commented May 12, 2015 at 16:00
Some possibly related questions here. Maybe this one is essentially the answer? — halfer
– halfer, Commented May 12, 2015 at 16:52

topher · Accepted Answer · 2015-05-13 16:25:47Z

23

+500

By default, Yii2 uses lazy loading for better performance. The effect of this is that any relation is only fetched when you access it, hence the thousands of sql queries. You need to use eager loading. You can do this with \yii\db\ActiveQuery::with() which:

Specifies the relations with which this query should be performed

Say your relation is comments, the solution is as follows:

'query' => Post::find()->with('comments'),

From the guide for Relations, with will perform an extra query to get the relations i.e:

SELECT * FROM `post`;
SELECT * FROM `comment` WHERE `postid` IN (....);

To use proper joining, use joinWith with the eagerLoading parameter set to true instead:

This method allows you to reuse existing relation definitions to perform JOIN queries. Based on the definition of the specified relation(s), the method will append one or multiple JOIN statements to the current query.

So

'query' => Post::find()->joinWith('comments', true);

will result in the following queries:

SELECT `post`.* FROM `post` LEFT JOIN `comment` comments ON post.`id` = comments.`post_id`;
SELECT * FROM `comment` WHERE `postid` IN (....);

From @laslov's comment and https://github.com/yiisoft/yii2/issues/2379

it's important to realise that using joinWith() will not use the JOIN query to eagerly load the related data. For various reasons, even with the JOIN, the WHERE postid IN (...) query will still be executed to handle the eager loading. Thus, you should only use joinWith() when you specifically need a JOIN, e.g. to filter or order on one of the related table's columns

TLDR:

joinWith = with plus an actual JOIN (and therefore the ability to filter/order/group etc by one of the related columns)

edited May 13, 2015 at 16:25

answered May 12, 2015 at 16:05

topher

14.9k8 gold badges56 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

laszlovl Over a year ago

This is mostly correct, but it's important to realise that using joinWith() will not use the JOIN query to eagerly load the related data. For various reasons, even with the JOIN, the WHERE postid IN (...) query will still be executed to handle the eager loading. Thus, you should only use joinWith() when you specifically need a JOIN, e.g. to filter or order on one of the related table's columns.

soju Over a year ago

You are right laszlovl, this has been discussed here : github.com/yiisoft/yii2/issues/2379

topher Over a year ago

@soju, laszlovl I've updated the answer. Feel free to edit it as needed.

halfer · Accepted Answer · 2015-05-12 16:51:38Z

In order to use relational AR, it is recommended that primary-foreign key constraints are declared for tables that need to be joined. The constraints will help to keep the consistency and integrity of the relational data.

Support for foreign key constraints varies in different DBMS. SQLite 3.6.19 or prior does not support foreign key constraints, but you can still declare the constraints when creating tables. MySQL’s MyISAM engine does not support foreign keys at all.

In AR, there are four types of relationships:

BELONGS_TO: if the relationship between table A and B is one-to-many, then B belongs to A (e.g. Post belongs to User);
HAS_MANY: if the relationship between table A and B is one-to-many, then A has many B (e.g. User has many Post);
HAS_ONE: this is special case of HAS_MANY where A has at most one B (e.g. User has at most one Profile);
MANY_MANY: this corresponds to the many-to-many relationship in database. An associative table is needed to break a many-to-many relationship into one-to-many relationships, as most DBMS do not support many-to-many relationship directly. In our example database schema, the tbl_post_category serves for this purpose. In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TO and HAS_MANY. For example, Post belongs to many Category and Category has many Post.

The following code shows how we declare the relationships for the User and Post classes.

class Post extends CActiveRecord
{
    ......

    public function relations()
    {
        return array(
            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
            'categories'=>array(self::MANY_MANY, 'Category',
                'tbl_post_category(post_id, category_id)'),
        );
    }
}

class User extends CActiveRecord
{
    ......

    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

The query result will be saved to the property as instance(s) of the related AR class. This is known as the lazy loading approach, i.e., the relational query is performed only when the related objects are initially accessed. The example below shows how to use this approach:

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here
$author=$post->author;

You are somehow doing it the wrong please go through from the documentaion here http://www.yiiframework.com/doc/guide/1.1/en/database.arr

Thanks, but this is for Yii1, the question was for Yii2. However, your post is helpful, thanks! :)
Welcome, even if its in Yii 2 active record will only load joined data or in terms of class associated data class when that is requested (lazy loading). If you go through that article you will also come to know one can manually join tables as well if Primary/Foreign keys are not present.

Collectives™ on Stack Overflow

Why is Yii2's ActiveRecord using lots of single SELECTs instead of JOINs?

Problem

What I suspect

Question

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

What I suspect

Question

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related