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:
  1. 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:
  1. <?php
  2.  
  3. $app = Yii::app();
  4. // Our PDO database connection
  5. $db = $app->db;
  6. // Size of one part of the data to be processed
  7. $part_size = 500;
  8. // Several spaces or tabs for a beautiful alignment file
  9. $tab = ' ';
  10. $tab2 = $tab.$tab;
  11. // The file that will record the result (in the root of the site)
  12. $f = @fopen(Yii::app()->basePath.'/../test.xml', 'w');
  13. $xml_header = '<?xml version="1.0" encoding="UTF-8"?>'.PHP_EOL.
  14. '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
  15. // Write to the top of the header file for the sitemap-file
  16. fwrite($f, $xml_header.PHP_EOL);
  17. // Name of the table that stores news
  18. $tbl = Publications::model()->tableName();
  19. // Command to be portioned make retrieve news
  20. $command = $db->createCommand()->select('id, title, date')->from($tbl);
  21. // Determine the amount of data that we need to treat
  22. $all_count = (int)$db->createCommand("SELECT COUNT(id) FROM $tbl")->queryScalar();
  23. // Set a limit how many items to choose from table
  24. $command->limit($part_size);
  25. // Loop through all of the data
  26. for ($i = 0; $i < ceil($all_count / $part_size); $i++) {
  27. // Here we add a piece of data to write to the file,
  28. // and each array element - a single line
  29. $xml = array();
  30. // Calculate the offset from the already processed data
  31. $offset = $i * $part_size;
  32. // Set the offset
  33. $command->offset($offset);
  34. // Find the next piece of data
  35. $rows = $command->queryAll();
  36. // Loop through the data found
  37. foreach ($rows as $row) {
  38. // Open the tag <url> - beginning of the description element in the sitemap-file
  39. $xml[] = $tab.'<url>';
  40. $xml[] = $tab2.'<loc>'.$app->createAbsoluteUrl('news/view', array('id'=>$row['id'])).'</loc>';
  41. // Close the tag <url>
  42. $xml[] = $tab.'</url>';
  43. }
  44. // Remove from the memory of the data found
  45. unset($rows);
  46. // Add in our file of the data processed
  47. if (count($xml)) {
  48. // Here we combine all the elements in the array $xml to string
  49. fwrite($f, implode(PHP_EOL, $xml).PHP_EOL);
  50. }
  51. unset($xml);
  52. }
  53. // End the XML-file
  54. fwrite($f, '</url></url></urlset>');
  55. // Done with the file
  56. fclose($f);

Comments (0)


Leave a comment