Working with large data in Yii

Working with large data in Yii

Rule number one - use console commands not only for CRON-tasks, but for a long-running operations, such as import/export data.
The difference between running tasks from the browser and from the console, is palpable: the console run time is limited and in the process, you can display information about the status of implementation.

Problem: Generate a sitemap.xml file with all the news of the project.

Rule number two - do not use ActiveRecord to work with large data, its use too much memory, and instead of 1 000 operations per second, you can only execute 10.

Then someone mentioned the memory? Right! Whenever you write News::model()->findAll() result falls in the server's memory, which is limited memory_limit parameter in the file php.ini, it usually has a value of 128M, so we can save your text, length 134 217 728 characters, which is nearly 2 000 text records on 65,000 characters each. And if you have more than 10 000 records? You'll receive from the PHP error that the memory limit is exhausted:

Fatal error: Allowed memory size of *** bytes exhausted (tried to allocate ** bytes)

Only one solution - you need to break up large data into small portions.

Go to practice:

<?php
 
$app = Yii::app();
// Our PDO database connection
$db = $app->db;
// Size of one part of the data to be processed
$part_size = 500;
// Several spaces or tabs for a beautiful alignment file
$tab = '  ';
$tab2 = $tab.$tab;
// The file that will record the result (in the root of the site)
$f = @fopen(Yii::app()->basePath.'/../test.xml', 'w');
$xml_header = '<?xml version="1.0" encoding="UTF-8"?>'.PHP_EOL.
        '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
// Write to the top of the header file for the sitemap-file
fwrite($f, $xml_header.PHP_EOL);
// Name of the table that stores news
$tbl = Publications::model()->tableName();
// Command to be portioned make retrieve news
$command = $db->createCommand()->select('id, title, date')->from($tbl);
// Determine the amount of data that we need to treat
$all_count = (int)$db->createCommand("SELECT COUNT(id) FROM $tbl")->queryScalar();
// Set a limit how many items to choose from table
$command->limit($part_size);
// Loop through all of the data
for ($i = 0; $i < ceil($all_count / $part_size); $i++) {
    // Here we add a piece of data to write to the file,
    // and each array element - a single line
    $xml = array();
    // Calculate the offset from the already processed data
    $offset = $i * $part_size;
    // Set the offset 
    $command->offset($offset);
    // Find the next piece of data
    $rows = $command->queryAll();
    // Loop through the data found
    foreach ($rows as $row) {
        // Open the tag <url> - beginning of the description element in the sitemap-file
        $xml[] = $tab.'<url>';
        $xml[] = $tab2.'<loc>'.$app->createAbsoluteUrl('news/view', array('id'=>$row['id'])).'</loc>';
        // Close the tag <url>
        $xml[] = $tab.'</url>';
    }
    // Remove from the memory of the data found
    unset($rows);
    // Add in our file of the data processed
    if (count($xml)) {
        // Here we combine all the elements in the array $xml to string
        fwrite($f, implode(PHP_EOL, $xml).PHP_EOL);
    }
    unset($xml);
}
// End the XML-file
fwrite($f, '</url></url></urlset>');
// Done with the file
fclose($f);