CDbCriteria vs. findBySQL?

I have a requirement, for a query that has specifically defined select and condition parts. I could either use a CDbCriteria with findAll(), or I can just put everything into a findAllBySQL(), with the latter being much easier for me to read personally.

What are the advantages and disadvantages of each? My primary goal is speed, so it seems almost obvious that a CDbCriteria will be slower - but then again, findBySQL clearly goes through a lot more processing than a direct DB query, it’s just unclear what.

If you need speed and less memory usage consider using new query builder: http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

That is very interesting, I did not know that existed… could be very useful.

But, at the bottom, it says that it’s faster and easier to directly write SQL statements for simple queries - which I consider mine to be. Something along the lines of,


SELECT col_a, col_b FROM table WHERE crit_a = 1 AND crit_b = 5

With maybe an order or limit on the end of some. Is the query builder still faster for that?

The ascending order of performance is:

DAO - The frameworks base DB layer. Perfect for static SQL that hardly changes.

Query Builder - A lightweight utility to build complex SQL from code. Good if the structure of your SQL depends on some logic.

ActiveRecord - A high level abstraction of your DB. Great to produce small and simple code.

So for obvious reason’s i’d say, if you’re out for max speed, you should use DAO.

That is most helpful, thank you.

I tried giving the Query Builder a test drive, but ran into an issue. I tried the following code in my registration procedure:




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

	->select('id')

	->from('NAE_Users')

	->where('email = :email', array(':email'=>$email))

	->queryRow();



But it spat out an error:


Missing argument 1 for CDbConnection::createCommand(), called in /var/www/anthroempire/protected/modules/user/controllers/RegisterController.php on line 40 and defined

createCommand() seems to be looking for input as $sql. The Query Builder page explicitly states that it doesn’t pass that variable, but makes no mention of how to avert the error.

Are you using the latest version? … Query builder was added in version 1.1.6

Hurp, guess that would help. Now it works beautifully.

I notice that it returns an array, with keys being the column names. Very nice!