php - Yii2: building complex queries with Query vs ActiveRecord -

i have these 2 queries, first written using active record, , second custom crafted using yii\db\query. in localhost query written query 2-4 miliseconds faster, harder write. query written ar execute several database queries more plus show create table ones, in total 10 or 12 queries more executed when ar. plus ar requires have ar model defined every table in relations network, while if avoid ar end less classes/files in app.

my question is, use ar or write queries yii\db\query ? ar prettier , easier write, generate many queries, problem ? working on site tables have few millions of rows , there 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(); 

first of all, possible cache schema , avoid "show create table" queries. set using enableschemacache attribute of db object.

second, joins not best way populate related records. example, if every entry in table has many related entries in table b, joining b result in entries table repeated. may performance issue.

for reason yii2 has with() , joinwith(). using with() related table make 2 queries, more not it's more efficient so.

lastly, ar immensely convenient tool once hang of it. me, benefits outweigh overhead.