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

Question

[Never Ever]

На данный момент есть проблемы с записью в файл так как результаты запросов с бд стали ОЧЕНЬ большие, во время генерации файла потребления памяти достигает около 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']);
   }
  }

Comments

[Дмитрий]

Чтение с базы и запись в csv?

[N]

Сколько строк для записи из БД получаешь?

Сама запись в файл не должна быть прям долгой и ресурсоёмкой...скорее всего у тя хранение данных полученных из БД занимают место в "памяти"...

сейчас запись идёт около 10-15 секунд

Это не только же именно запись? А плюс получение данных из БД...

Как и сказали...из БД по частям получай данные...

Answer 1

[Максим]

В добавок к ответам выше хочу отметить, что для подобных ситуаций данные чаще подготавливают, а не «собирают» на лету.

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

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

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

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

Всё это общие пути оптимизации. Нужно знать больше информации о проекте, о проблеме, чтобы проанализировать и придти к какому-то верному решению.

Answer 2

[Gennady S]

И Excel спокойно поглощает такое количество строк? Там вроде около миллиона строк, но Excel не предназначен для этого, если массив данных очень большой, то работать с ним лучше через СУБД.

Способов оптимизации много, но универсальных путей не так много, не зная бизнес-требований, структуры данных сложно сказать точнее. Из общего. Во-первых, да, брать данные порциями, например, по 10к или 50к строк. Во-вторых, не брать данные повторно, кэшировать (вряд ли у Вас меняются все данные каждый раз), сохраняя срезы Excel или денормализацией. В-третьих, оптимизировать структуру данных и / или запросы. В этом плане Doctrine или другой движок едва ли играют роль, тем более Вы используете конструктор запросов. В-четвертых, если память грузит запись в Excel, можно отказаться от движка (опять же, неизвестно, используете ли такой?), писать вручную в XML-шаблон, упаковывая в Excel опять же руками.

Comments

[Never Ever]

Сейчас в запись идет около 120-150к строк.
Разве mysql может сразу генерить Excel ?
1) Вот этот момент что-то не могу собрать :(
2) ?
3) Запросы оптимизированы, индексы расставил
4) был PhpSpreadsheet, сейчас кастомный, стало легче

[N]

Never Ever,

был PhpSpreadsheet, сейчас кастомный, стало легче

Сравните с этим пакетом...если думаете, что дело именно в нём...
https://opensource.box.com/spout/docs/

[Gennady S]

Never Ever, если 120к строк берутся долго, значит проблема в оптимизации запросов. Для MySQL это должно быть как семечки.

1) Разбивать постранично, 1 запрос от записи 0 до 10 000, потом от 10 000 до 20 000 и т.д., но у Вас данных немного;

2) Зависит от бизнес-логики, если, например, данные берутся раз в день, и из 120к строк только 5к новые, то почему не брать данные, начиная с даты, идентификатора от последней выборки?

3) Но почему тогда запросы по 3-4 секунды? Посмотрите, может сложные JOIN, может сложная выборка, может вложенные запросы, может есть смысл создать view или упростить что-либо, смотрите EXPLAIN;

4) Тоже, смотреть в сторону пакетной записи, писать часть в памяти и скидывать на диск порциями и т.д., 10-15 секунд это долго для такого числа записей.

Без деталей сложно сказать точнее. Приведены слишком общие пути решения.

[N]

во время генерации файла потребления памяти достигает около 2-3 ГБ

...

был PhpSpreadsheet, сейчас кастомный, стало легче

Попробуйте тот пакет, который я дал...он вроде намного меньше памяти потребляет, чем этот древний PhpSpreadsheet...

[Never Ever]

Gennady S, Хорошо, спасибо за ответы. Сейчас проверил лог, нет тот лог запроса смотрел, так около 500-700 ms

[Never Ever]

N, хорошо, попробую

[Gennady S]

Never Ever, PhpSpreadsheet поддерживает кэширование и пакетную запись fromArray (может пошустрее будет).

[N]

Gennady S,

(может пошустрее будет)

Да тут вроде автору больше важны мегабайты оперативки...что, как раз, PhpSpreadsheet любит потреблять...

Answer 3

[Антон Шаманов]

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 довольно хорошо оптимизирован для работы с большим объемом данных

Answer 4

[sl0]

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