Lazy loading for large datasets in ActiveRecord via model

Archives Yii 1.0.x Discussions
1

Hi,

I am wondering how one should effectively perform reading of large datasets from defined models?

I already have defined model, say "Assignment" and I have relations with other 2 tables (inventory and users). Then I perform search query like this:





	$criteria=new CDbCriteria(array(

		'with' => array('inventory', 'usermodel'),

	));


	$criteria->compare('t.deleted', '0'); // deleted = 0

	$criteria->compare('t.user_id', $this->model->filter['user_id']);


	if($this->model->filter['from'] > 0){

		$criteria->compare('t.date_issued', '>='.$this->model->filter['from']);

	}


	if($this->model->filter['to'] > 0){

		$criteria->compare('t.date_issued', '<='.$this->model->filter['to']);

	}


	$searchModel = new AssignmentModel();

	$data = $searchModel->findAll($criteria);

This will return correct dataset but the problem is that returned dataset may become very large and it would consume lots of memory (thousands of rows loaded into a single array of AR objects). I need to read row by row with a simple forward stream, just like CDbDataReader does.

However, I could not figure out, how to read row by row from model?

Thank you in advance for any ideas.

Lubos

2

Well, I assume there is no way how to achieve lazy loading of large datasets at this time…

3

If you want to lazy load related records you’ll have to remove the with part from the criteria.

Not tested, but you may want to check if unsetting the related records (e.g. $data->inventory[$i]) after usage could help.

/Tommy

4

Hi Tommy,

thanx for response - that’s a pity - keeping the [WITH] relations was precisely reason why I wanted to use lazy loading via model. I have 3 tables with defined relations via [WITH], which are nicely JOINed if loading via model generating pretty complex SQL query. I am just wondering why one should I write completely new SQL query if it is already generated via model…

Thanx

Lubos

5

Obviously you are talking about eager loading. I suggest you search the forum for existing threads about e.g. AR, DAO, batch, memory.

/Tommy

6

For large datasets, IMHO, I would not use CActiveRecord wonders as the easiness of the object may bring a lot of weight and resource consuming.

For that, use DAO, much faster and you may find a more familar way to loop the rows of a resulting dataset.

Edit: Good catch tri

7

Hi,

Just wanted to add my two cents here as I think that it is an interesting topic in general. As Antonio already stated ActiveRecord isn’t ment to be perfectly suitable for all needs. I am currently working on a project where I want people to be able to search in a database of thousands of records. So if a user wants to display all records I would have to instantiate all the AR models in order to send them to my GridView or whatever you may use.

I do it this way:

Instead of using any AR methods or CDBCriterias I am building my SQL command via the command builder using a method. The results would be directly retrieved from the database. After that I only instantiate the models I really need.

So the heavy search is done via classic SQL and the simple things are done via ActiveRecord.

8

Hi guys,

thanx for your advices. Yes, I meant eager loading, sorry:-)

I solved the problem by building new query from scratch (DAO) - luckily the query builder proved to be a nice time saver even though I must have re-written pretty complex query from scratch…

Best regards,

lubos