Query Optimization

Yii 1.1 General Discussion
1

I have two cases for example as below for fetching data.

First Case :

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

->select('username, password')


->from('tbl_user')


->where('id=:id', array(':id'=>1))


->queryRow();

Second is :

$query = ’

            SELECT


                username, password


            FROM


                tbl_user


            WHERE


                id ='.$id;


    $user = Yii::app()->db->createCommand($query);


    $userResult = $user->queryAll();

Which is more better and why?

2

Hi,

let us examine the first query.

In the first query you are using select()->from()->where() which are built in function to create a query, meaning SQL query will be created by framework and this requires some time t1.

Also, the first query binds an element ID, which disables SQL injection attackes meaning query 1 is much safer than query 2. For binding parameter framework needs t2.

For execution we need t3

So total time to execute the first query is t = t1 + t2 + t3

In a query 2, SQL query is already written by you, and there is no t1 time. Also, you are not binding parameter ID which means there is no t2 parameter. So simply said time required to execute query 2 is t = t3.

[i]

[/i]

Query 2 is faster, but dangerous from security perspective. You should almost always use Query 1 in your real applications, because it is safer, and it is easier for maintainance.

Also, when you are using Active Record classes to work with database, then your queries are more slower than Query 1.

Generally said, you should not use AR classes if you are working with more than 50-100 rows, because it will get slow at some point.

3

@duri

Thanks for your response. It solved many points I have in mind.

I am more concern about security time is something that we can afford. So I will always use first one.

4

No problem, I’m glad that I helped :)