CGridView performance with thousands of data rows

Yii 1.1 General Discussion
1

Hello,

I am porting an old web application to Yii. For a certain view I’d like to use CGridView or something similar to display the result of a multi-table query with many rows (can be thousands).

Problem here is CGridView performance. Database query takes about half a second for ~1100 rows but rendering that takes over a minute on my test server. I have XCache installed. Is there anything else I can do to boost performance? I’m not sure whether the time is spent in AR or rendering stuff. Maybe I should use the database via DAO instead of AR to get better performance?

All ideas are welcome.

2

It sounds like to me, from your post, that you are displaying all 1100 at once? Try enabling pagination and limiting your result set?

3

I forgot to mention in the original post that users of this application actually PREFER to see all the rows at once to get a "quick glance" of the data. Otherwise pagination would be a perfect solution.

4

Active Record is too slow with so big amount of data. Why not cache the whole page and don’t regenerate it on each request?

5

AR is slow and probably not a good use for this kind of situation

One thing you can check if you get the data from different tables is the generated SQL … check if it uses a JOIN and retrieves all needed data in one SELECT… because if you don’t use a JOIN SQL CGridView makes a select for every row to get the additional data from other tables…

And use the classical suggestion for SELECT, specify what field you need, don’t use the select all data and use only what you need method…

6

There is a trick that can help you: http://www.yiiframework.com/forum/index.php?/topic/8527-what-the-real-limitation-of-activerecord/ I believe it’s not adapted to data provider so you have to find a way to apply it.

7

Thanks for replies. This can now be considered to be solved.

I implemented a simple grid view of my own which has enough functionality for this task (all fields sortable, table cell content can be generated using PHP expression) and uses DAO to fetch the data with a hand-crafted SQL query. Rendering 2200 rows of data takes roughly two seconds.

The data is generated by JOINs (seven tables, no less…) and the database query is not the problem. With profiling I can see that CGridView made only one SQL query which took less than a second, then spent about a minute rendering the rows.

8

Glad you solved it onre…

would be nice if you can post your code of the "simple but fast grid", maybe it would be of use to someone…

9

I could post it after some cleanups. It’s a bit application-specific as of now, I’ll refactor it to be more generic and reusable in some days because I thought of using it for all large-data grid views in the app.