How Change Query In Cgridview

Yii 1.1 General Discussion
1

[font="Arial Narrow"][size="5"][size="4"]Hi dears,

When i use gii in generate model in Yii,method search return CActiveDataProvider to Cgridview,

In method use 2 query for fetch data from database:




SELECT COUNT(*) FROM `os` `t`

SELECT * FROM `os` `t` LIMIT 10

!os is tablename,

it is ok, but when in database We have 30 or 40 milion record, this query is very heavy beacuse this query fetch all 30 or 40 milion record with all column and send only 10 record to Cgridview,!!!!!!

How can I use below query to search method?




SELECT COUNT(*) FROM `os` `t`

1:select id from os `t` limit 10,

2:select * from os `t` where id in (result upper query)

This solution is very very faster than default solution in Cgrid view.

Can Any body help me???

thanks for attention[/size][/size][/font]

2

Hi

Please post the search method

did you try to set CActiveDataProvider with limit criteria ?


'criteria'=>array(                          

                      'condition'=>'...',

                      'order'=>'...',                          

                      'limit'=>10,

                    ),

check also

http://www.yiiframework.com/doc/api/1.1/CActiveDataProvider

3

take look at the guide is a pretty good source to get started

http://www.yiiframework.com/doc/guide/1.1/en/database.overview

4

Hi mahdi1986 and all,

I’m very interested in this statement. Is it really the case?

I don’t believe the 2nd one is very very faster than the 1st one. Frankly saying, I believe the 2nd one is slower.

Do you mean to say that the db engine will fetch all the rows and send back only the number of rows specified with limit? I believe the db engine will fetch only the number of rows specified with limit.

[EDIT]

OK. I googled a bit and learned that you are right in some cases.

‘LIMIT’ may slow down the query when, for example, used with ‘ORDER BY’ and ‘OFFSET’ is high, and especially when the table has a huge number of rows.

Well, I’m sorry but I don’t think that you can get what you want with CActiveDataProvider. Probably you’ll want to try CSqlDataProvider.

5

Hi,

I use pagesize in Cgridview and size of limitation is optional

6

Hi,

I tested this method,

In dataBase with 30 milion record select * from table where … limit 10 :3 min

select id from table where … limit 10 + select * from table where id in (…): 1 min

7

Thank you for the feedback. :)

I believe that CActiveRecord and CActiveDataProvider are the most efficient way in most of the cases. They will give you the well-balanced solution between the convenience(the ease of development) and the performance. But I think yours is an exception. You have to use every technique to optimize the performance in the raw SQL level.

There’s no need to throw away CActiveRecord and CActiveDataProvider, but you will want to optimize the critical functions with raw DAO and CSqlDataProvider.