Yii2: building complex queries with Query vs ActiveRecord

Question

I have these two queries, first is written using Active Record, and second is custom crafted using yii\db\Query. In my localhost query written with Query is 2-4 miliseconds faster, but is harder to write. Also query written with AR will execute several database queries more plus SHOW CREATE TABLE ones, in total it is like 10 or 12 queries more that are executed when I do AR. Plus AR requires you to have AR model defined for every table in relations network, while if you avoid AR you will end up with less classes/files in your app.

My question is, would you use AR or would write queries with yii\db\Query ? AR is prettier and easier to write, but it generate so many queries, is that a problem ? I am working on a site where tables have few millions of rows and there are about 100k site visits every day.

AR query :

return self::find()->select('id, news_users_id')
                   ->with([
                        'newsUsers' => function ($query) {
                            $query->select(['ID', 'cmpid']);
                        },
                    ])
                   ->with([
                        'newsUsers.firme' => function ($query) {
                            $query->select(['id', 'skraceni_naziv']);
                        },
                    ])
                   ->with([
                        'newsUsers.firmeBh' => function ($query) {
                            $query->select(['id', 'skraceni_naziv']);
                        },
                    ])
                   ->with([
                        'newsUsers.firmeOstalo' => function ($query) {
                            $query->select(['id', 'skraceni_naziv']);
                        },
                    ])
                   ->orderBy(['id' => SORT_DESC])
                   ->limit(6)
                   ->all();

Query:

$query = new Query;

$query->select(['club.id',
                'firme.id', 'firme.skraceni_naziv',
                'firme_bh.id', 'firme_bh.skraceni_naziv',
                'firme_ostalo.id', 'firme_ostalo.skraceni_naziv']) 

      ->from('club')
      ->innerJoin('news_users', 'club.news_users_id = news_users.ID')
      ->leftJoin('firme', 'news_users.cmpid = firme.id')
      ->leftJoin('firme_bh', 'news_users.cmpid = firme_bh.id')
      ->leftJoin('firme_ostalo', 'news_users.cmpid = firme_ostalo.id')
      ->orderBy(['club.id' => SORT_DESC])
      ->limit(6);

$command = $query->createCommand();

return $command->queryAll();

Answer 1

First of all, it is possible to cache your schema and avoid those "SHOW CREATE TABLE" queries. Set it using enableSchemaCache attribute of your db object.

Second, joins are not always the best way to populate related records. For example, if every entry in table A has many related entries in table B, joining B to A will result in entries from table A to be repeated. That may be a performance issue.

For this reason yii2 has with() and joinWith(). Using with() to get the related table will make 2 queries, but more often than not it's more efficient to do so.

Lastly, AR is an immensely convenient tool once you get the hang of it. For me, its benefits outweigh the overhead.