Как ускорить создание большого прайса?

Question

[smoln]

День добрый, создаю прайс xlsx из базы данных mysql с помощью библиотеки PhpSpreadsheet, прайс после создания отправляется клиентам, каждому клиенту свой прайс с разными позициями и ценами (все делаю запросом в mysql и применяется php для создание цен), в каждом прайсе порядка 300 тыс позиций. Задание запускает cron по расписанию

Вопрос такой, прайс создается порядка +-40 мин, как возможно ускорить создание прайса?
Так же применяется Memcache, но у него всего 1 гиг, этого не хватает (максимум на хостинге)

Comments

[vyrkmod]

Что за таблица, какой запрос, сколькоресурсов выделяет хостинг... Данных маловато. Ну и да, 40 минут на такую задачу - совсем неадекватное время, у меня на целероне и харде упакованный лог за сутки на порядок быстрее прожёвывался, а это совсем не бд.

[Sergej]

На что у вас тратится время ?
На выборки или на формирование файла?
Реализуйте замеры и найдите узкое место. Тогда вам и нам станет понятно, где собака тело закопало.

Мб. вы выбираете сразу 300к позиций и пихаете их в XML. Тогда я удивлюсь, почему так быстро формируется файл.

[smoln]

SharkMan, выборка проходит достаточно быстро, если использовать запрос просто запрос, проблема думаю с записью в файл, записывает построчно.
Причем 90 тыс формирует всего 5 минут.
Код один в один, лишь запрос отличается (грубо говоря в одно delevery=1 в другой delevery=3 )

[Sergej]

smoln, Память на сколько забита при такой выборке и записи?

[smoln]

SharkMan, лимит памяти увеличивал по мере надобности, 3 гига не хватало, 4 норм

[Denis]

В таких случаях выбираю сохранять в csv формат, работа заметно ускоряется, тем более что формат легко открывается экселем

[smoln]

sidni, не подходит, мне нужно понять как я могу ускорить сей процесс

[d'Ivan]

smoln,

выборка проходит достаточно быстро, если использовать запрос просто запрос, проблема думаю с записью в файл, записывает построчно.

А чего не замерить сколько занимает запрос и формирование каждого файла в отдельности? Зачем гадать?

[Denis]

smoln,
Согласен, но как вариант...
Я в свое время много имел дел с прайсами и работа с форматом эксель это головная боль, сторонние библиотеки жрут много памяти и времени. Когда работаешь напрямую с текстовым файлом отпадает множество проблем, к примеру, создавать прайсы на 300МБ и особенно их потом грузить.
При том что можно легко записывать в csv файл порционно, а не по-строчно, а при загрузки их разбивать на более мелкие.

Answer 1

[antoo]

40 минут - звучит совсем дико, необходимо профайлить и понимать, что именно выполняется так долго.

PhpSpreadsheet скоростью никогда не отличался, особенно для больших файлов, рекомендую перейти как минимум на https://github.com/box/spout . Недавно ещё была интересная статья на хабре про генерацию больших и сложных Excel, ознакомьтесь: https://habr.com/post/422059/

Answer 2

[ponaehal]

ИМХО, общий подход (вне зависимости от используемой библиотеки):
Почти любая библиотека может заполнять одну ячейку, а может диапазон.
Нужно сформировать диапазон (диапазоны) в БД, а потом залить его в эксель одной (несколькими) операцией.
По опыту, на больших файлах это даст прирост производительности на несколько порядков

Comments

[Евгений Решетников]

Только не для PhpSpreadsheet :)
Там массив значений в конечном итоге записывается по одному cell и для каждого cell делается проверка

Answer 3

[ewb]

Из базы в CSV, а там его можно и прямо экселем открыть и смотреть точно так же (как обычный экселевский файл) или же делать уже, что-то с готовым файлом (конвертировать).
Так как CSV просто текст, то генерация должна пройти быстро.

Comments

[ponaehal]

зачастую, при формировании отчетов, данные заливают в заранее подготовленный шаблон XLS. Если это тот случай, то CSV не спасет.

Answer 4

[dmitriy]

Как вариант сформировать csv а в нужный формат перегнать утилитой например libraoffice

Answer 5

[marsdenden]

Есть еще интересный хак - сформировать html с < table >< / table >, сохранить его как xls - любой excel открывает такой файл, не чихая. Правда, на таких объемах я это не тестировал.

Comments

[smoln]

Спасибо за ответ, но данный вариант не подходит, любой эксель открывает этот файл, и предупреждает о неверном формате. Да и нужен мне формат xlsx.

[marsdenden]

smoln, ну тогда проще разобраться с внутренностями xlsx и полностью самому генерить, не пользуясь сторонними решениями. Не так уж он и сложен. А тормоза, мне кажется, из-за кривого кода, который после каждого изменения опять перепаковывает данные. Ведь по сути это zip архив, внутри xml. Что мешает сформировать xml по правилам и запаковать его?

Answer 6

[Randel Kovalsky]

И ещё один хак существует.

Рассматривать XLSX как zip-архив набора XML файлов, чем он по сути и является.

PHP довольно неплохо ворочает XML, в отличие от XLSX.

Но это довольно муторно, придётся разбирать методику как формируется всё это дело. Но может показаться интересным.

Answer 7

[smoln]

Наконец то добрался! Спасибо всем за обсуждение, отметил решение данного вопроса, может кому пригодится. Создание прайса 300 тыс строк (17мб ) теперь занимает две минуты! Решение в библиотеке https://github.com/box/spout