Как оптимизировать запись в файл .xls?

На данный момент есть проблемы с записью в файл так как результаты запросов с бд стали ОЧЕНЬ большие, во время генерации файла потребления памяти достигает около 2-3 ГБ. и это уже критично для проекта, надо уменьшить хотя бы до 200 мб. Можно пожертвовать скоростью(сейчас запись идёт около 10-15 секунд, так что пока норм). Смотрел в сторону генетраторов и SPL, но что-то не могу понять как использовать в моем случае.
Я как бы понимаю что не надо тянуть все данные за раз и хранить в памяти, но если начать брать частично данные то возникает много тяжелых запросов к бд(около 3-4 секунд), но как это всё связать хз.
Так же хотелось бы узнать можно ли как-то решить эту проблему с помощью доктрины/симфони ?
Так что же делать ?
Ниже пример моего кода в очень упрощенном виде
// берем данные
public function getData(array $data): array {
    $queryBuilder = 
        //..
        //..
        //..
    return $queryBuilder->getQuery()->getArrayResult();
}
// тут уже просто запись в файл
 public function generate($data) {
  $result = $this->getData($data);
   foreach ($result as $row) { 
     $xls->write($row['id']);
   }
  }
  • Вопрос задан
  • 209 просмотров
Решения вопроса 1
myks92
@myks92 Куратор тега PHP
Нашёл решение — пометь вопрос ответом!
В добавок к ответам выше хочу отметить, что для подобных ситуаций данные чаще подготавливают, а не «собирают» на лету.

Первое с чего нужно начать это проанализировать какое хранилище вам подойдёт, возможно это будет даже не реляционная БД, а какая-то NoSQL. Подробнее о выборе хранилища можно почитать статью на Хабр: https://m.habr.com/ru/post/487498/

Даже если вы не хотите изменять хранилище MySQL на какую-то другую, то вам следует подумать о денормализации данных в отдельные таблицы. Благодаря чему вам не придётся делать сложные JOIN запросы, а все данные будут уже в готовом виде с своих таблицах. Такие данные будут во много раз быстрее выгружаться в отчёты, но и нужно будет следить за актуальностью данных. Собирать эти данные можно по разному: по событиям на этапе создания/изменения/удаления, по крону, по запросу.

Оптимизировав запросы и произведя денормализацию можно пойти дальше и сделать кэширование на все запросы. Благодаря чему данные будут браться не из базы, а из кэша, а это всегда быстрее. При этом не нужно забывать, о том, что нужно актуализировать данные в кэше.

Для большего ускорения следует отказаться от объектов в пользу массивов. Особенно если запросы идут в базу не на простом SQL, а через какую-то ORM вроде Doctrine. В доктрине происходит маппинг данных на объекты, что очень сильно замедляет работу с данными.

Всё это общие пути оптимизации. Нужно знать больше информации о проекте, о проблеме, чтобы проанализировать и придти к какому-то верному решению.
Ответ написан
Комментировать
Пригласить эксперта
Ответы на вопрос 3
gscraft
@gscraft
Программист, философ
И Excel спокойно поглощает такое количество строк? Там вроде около миллиона строк, но Excel не предназначен для этого, если массив данных очень большой, то работать с ним лучше через СУБД.

Способов оптимизации много, но универсальных путей не так много, не зная бизнес-требований, структуры данных сложно сказать точнее. Из общего. Во-первых, да, брать данные порциями, например, по 10к или 50к строк. Во-вторых, не брать данные повторно, кэшировать (вряд ли у Вас меняются все данные каждый раз), сохраняя срезы Excel или денормализацией. В-третьих, оптимизировать структуру данных и / или запросы. В этом плане Doctrine или другой движок едва ли играют роль, тем более Вы используете конструктор запросов. В-четвертых, если память грузит запись в Excel, можно отказаться от движка (опять же, неизвестно, используете ли такой?), писать вручную в XML-шаблон, упаковывая в Excel опять же руками.
Ответ написан
SilenceOfWinter
@SilenceOfWinter Куратор тега PHP
та еще зажигалка...
1. https://solutioncenter.apexsql.com/how-to-import-a...
2. как вариант, выгружаешь из базы данные в csv формате, записываешь в файл и конверишь в xls.
SELECT order_id,product_name,qty
INTO OUTFILE '/data/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

3. https://github.com/box/spout довольно хорошо оптимизирован для работы с большим объемом данных
Ответ написан
Комментировать
@sl0
У меня написана своя exporterFactory, которая принимает qbProvider и Writer.
qbProvider возвращает iterate(), по которому проходится foreach и генератором отдает данные. На каждую 1000 итераций em->clear(). Writer банально все пишет через Spout. Возможно, это не самый оптимальный вариант, но файлы до 350 мб он генерирует без проблем.
ps Если же требуются большие файлы, то может имеет смысл выяснить зачем? Один раз заказчик просил добавить столько данных, что нужно было генерировать гигабайтные файлы. Выяснилось, что он потом просто считал в экселе строчки для отчета. Проблема решилась простым предоставлением уже готовых посчитанных данных.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы