CODEWITHSUNDEEP
phpapacheyii
AssafW
AssafW

Reputation: 97

Yii CDbCommand queryAll crashes when trying to retrieve big data sets

I am using Yii 1.1.14 and I am trying to retrieve a big data set (~80,000 records / 10 columns) from an oracle 11.2 database.

My problem is that the entire script just crashes after a few minutes without triggering any errors / exceptions. I also checked the php and apache logs but there is no indication for any error.

The script is executed from a browser and I only get a blank page back with no data.

Here is the code:

set_time_limit(0);
$connection = new CDbConnection($dsn,$username,$password);
$command = $connection->createCommand('SELECT * FROM TEST_DATA');
$result = $command->queryAll(); //this is where the script crashes
print_r($result);
//Please disregard any typos, I pasted only a portion of the code.

After some debugging, I realize that the script fails on the $command->queryAll() line, But When I set a limit to 60,000 records (~9MB file) the script dos not fail and I get the results printed on the page, so I know the codes works fine.

I suspect that the crash is related to memory consumption but usually it triggers an error and I have tried raising the memory_limit in php.ini to 256MB but this didn't solve it. I also tried setting error reporting to E_ALL in order to try and get any errors / exceptions raised.

I am using apache with php 5.3 on a centos 6 server.

I am all out of ideas and I would really appreciate it if anyone here could help me out.

EDIT: I found the problem, it is a memory issue, I figured out that for a ~10MB result set / 10MB file you will need to set the memory limit on php.ini to 256MB (which is a lot!), I guess I will not use Yii AR but issue a plain php command.

Upvotes: 1

Views: 394

Answers (1)

Maug Lee
Maug Lee

Reputation: 915

I can hardly imagine a situation, when 60K records must be printed on the page. I suggest you not doing findAll() but using paginated results as in CGridView or CListView:

$dataProvider=new CActiveDataProvider( TestData::model() );

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'=>$dataProvider,
));

Its very simple to rewrite your code. E.g. if you are doing (even with scopes)

TestData::model()->published()->top(5)->findAll()

just cut-off last part and you have an argument to CActiveDataProvider contructor

new CActiveDataProvider( TestData::model()->published()->top(5) );

UPDATE

Try to query by portions:

$limit = 1000 ;
$passes = ceil( Yii::app()->db->createCommand( 'SELECT COUNT(*) FROM {{test_data}}' )->queryScalar() / $limit ) ;

for ( $pass = 0 ; $pass < $passes ; $pass++ ) {
    $command = Yii::app()->db->createCommand()
        ->select('*')
        ->from( '{{test_data}}' )
        ->limit( $limit )
        ->offset( $pass * $limit ) ;
    $result = $command->queryAll() ; // here is your peace of data
}

Upvotes: 1

Related Questions