AR is super slow comparing to DAO?

Yii 1.1 General Discussion
1

I run a simple test:

Firstly:


$test = Test::model()->findBySql("SELECT * FROM test WHERE active = 1 ORDER BY counter ASC");

Secondly:


$db = Yii::app()->db;

$sql = "SELECT * FROM test WHERE active = 1 ORDER BY counter DESC";	

$command = $db->createCommand($sql);

$test = $command->query();

(Table has only two records)

AR took almost 0.3sec to run. ADO took just 0.01sec. Is this the price for AR`s convenience?

2

In addition: schemaCachingDuration is on.

3

Interesting post tedd… very interesting

4

This is not likely because the extra cost in your example in AR is only the call to createFindCommand() and the creation of the AR object, which can’t take that much overhead.

Because you are using schema caching, it is possible that the overhead is caused by that part, depending on what caching you are using.

To reduce the impact of the schema caching, you may conduct the test by running findBySql() 1000 times versus query() 1000 times.

5

Actually, my server`s CPU is heavily used right now. Maybe this is the reason. I guess DAO doesnt require calculations at all, thats why it performed well.

6

As I know twitter initially was built using AR, after their initial crashes they had to rewrite all to direct querys to db, this removes some overhead, and also gives you fine grain control, on the other hand only small amount of web apps become such a hit so AR for simple web app/site is good choice, also AR includes security features as I know still very fresh here.

7

95% percent of our app is built on top of AR. We didn’t launch it yet but I hope we won’t have significat performance problems.

8

Yes AR is a little bit slower than DAO.

But I cannot see in your test how you proof this.

You don’t do it in a debug session because the execution time is not exact and slower as in a normal usage.

For example I test the following: Table "watchdog" with 1000 rows.




<?php

//AR

$start = microtime(true); 

  $mydata=Watchdog::model()->findByPk(12995);

$end = microtime(true);

$sql_time_1 = $end - $start;




$start = microtime(true); 

   $mydata=Watchdog::model()->findAll(array(

    'select'=>array('wid', 'location', 'hostname', 'referer', 'timestamp'),

    //'condition'=>'wid=:wid',

    //'params'=>array(':wid'=>12602),

   ));

$end = microtime(true); 

$sql_time_2 = $end - $start;   





//DAO

//$conn = Yii::app()->db;

$start = microtime(true); 

  $result = $Yii::app()->db->createCommand()

   ->select('wid, location, hostname, referer, timestamp')

   ->from('watchdog w')

   //->where('wid=:id', array(':id'=>12995))

   //->limit(50)

   ->queryAll();


   foreach ($result as $row) {

     echo '<li><strong>'.CHtml::encode($row['location']).'</strong></li>';

    }

$end = microtime(true); 

$sql_time_3 = $end - $start; 




echo 'The sql time 1 is: ' . $sql_time_1 . '<br>';

echo 'The sql time 2 is: ' . $sql_time_2 . '<br>';

echo 'The sql time 3 is: ' . $sql_time_3;

My results:

The sql time 1 is: 0.00576901435852s 5.7ms

The sql time 2 is: 0.0338799953461s 33.8ms

The sql time 3 is: 0.00135612487793 1.3ms

But you has not often a sql query that get 1000 rows.

When you get one row or a few(like a list e.g. 50 rows) then you have results with AR about 3-5 ms and this is ok in the most cases.

But test it by your own in your special situation with conditions and params or without.

9

I think you should also look at memory…

getting user via dao compared to the same object via AR is about 200kb more…

in raw php you can write full app that will take 200kb… :lol:

But this is the cost…

My rule is - if you can cache it for at list 5 minutes - use AR, if you can’t - only dao

10

Hi everyone, I’m doing some performance tests for my very first Yii webapp (a sort of online lottery).

I have a couple of actions that causes a lo of writes in a db table and I’m traying to catch the best solution to do it.

I’ve been trying both AR and DAO and results shown that with AR operations takes almost half the time than DAO and this sounds a little strange to me.

I used both sqlite and pgsql as db.

My DAO code is




$connection = Yii::app()->db;

$sql = "INSERT INTO instant_tickets values ('$id', '$time_now', '$uid', '$time_expiration', $is_pending)";

$connection->createCommand($sql)->execute();

And the corresponding AR code




$ticket = new InstantTickets();

$ticket->id = $id;

$ticket->time_issued = $time_now;

$ticket->time_expiration = $time_expiration;

$ticket->id_user = $uid;

$ticket->is_pending = $is_pending;

$ticket->save();

Is it possible that DAO is souch slow in write op. compared to AR?

I’m running 3 inserti operations like that in sequence for 1000 times without apc.

With apc both are speeding up but AR still remains faster

thanks

11

So in your case it is the other way around? AR is FASTER than DAO? That is really strange. AR is built on top of DAO so it doesn’t make sense at all. Maybe you are using 2 different db components to connect to the database? One with schemaCaching and the other one without? If that’s not the case than I think that there is some code in your loop that slows down the process.

12

Connectios string is the same like any other part of the code. I’ve only replaced DAO code with AR code leaving the rest unchanged. Maybe AR uses some optimizations for INSERT querys?