Страницы: 1
RSS
Power Query Как зависит производительность при Table.Buffer, List.Buffer
 
Источник https://blog.crossjoin.co.uk/2015/05/05/improving-power-query-calculation-performance-with-list-buff... но понимания зачем нет
Например в https://msdn.microsoft.com/query-bi/m/table-buffer пишет
Буферизирует таблицу в памяти, изолируя ее от внешних изменений во время оценки
Это как?
"Правильно заданный вопрос,половина ответа!"
 
Доброго дня, ранее обсуждалось вот в таком ключе. Просмотрите, если что останется не ясным - спрашивайте дальше.
А если вкратце - эти функции заставляют PQ не строить цепочку преобразований - оптимизаций в интерпретаторе, а принудительно рассчитать выражение и результат закинуть в память присвоив этому результату имя шага на котором вызывалась функция, если к этому имени будете обращаться в дальнейшем, то результат будет не рассчитывать вновь, а сразу браться готовый из памяти.
Тут главное не переборщить с размерами массивов, которые планируете буфферизовать, а то можно получить обратный эффект.
Вот горшок пустой, он предмет простой...
 
Тоесть на больших таблицах при ограничениях на память ОЗУ могут быть проблемы?
"Правильно заданный вопрос,половина ответа!"
 
Обратите внимание на процессы в диспетчере задач во время выполнения скриптов. Есть там такие Microsoft Mashup Evaluation Container. Все вычисления PQ передает в них. Вот у них есть ограничения один процесс максимум 256 Мб, и одновременно может работать не более 4 процессов. Соответственно если вы напихаете в них данных больше чем на 1 Гиг, то все что сверху PQ начнет кэшировать на жесткий диск. И хорошо если это у вас SSD. А если HD? Со всеми вытекающими.
Как я использую функцию: Например нужно выполнить запрос, который на выходе дает список значений.
Код
let
    Today = DateTime.Date(DateTime.LocalNow()),

    Source2 = 
    Table.FromList(
        List.Dates(
            Today + Duration.From(1), 
            55, 
            Duration.From(1)), 
        Splitter.SplitByNothing() ),
    AddColMonths = Table.AddColumn(Source2, "Month", each Date.ToText([Column1], "yyyy/MM"), type text),
    DaysToForecast = List.Buffer( Table.Group(AddColMonths, {"Month"}, {{"Diff", each Number.From(List.Max([Column1]) - List.Min([Column1]) ) + 1, type date}})[Diff] )
in
    DaysToForecast

В дальнейшем эти значения будут применены для расчетов в примерно 300 000 ячеек, и если бы в последнем шаге я не применил List.Buffer, то каждый раз при обращении к значениям этого списка из-за ленивых вычислений весь код будет выполняться с самого начала, это миллисекунды, но если их умножить на 300 000, то оно вырастает в минуты. Применив же функцию, запрос вычисляется один раз, закидывает список в память и в остальные разы читает значения уже из памяти - ПРОФИТ!
Вроде ничего не соврал. На моем скрипте без буфера запрос вычисляется минут 5 с буфером 2-2,5 минуты
Изменено: PooHkrd - 05.04.2018 11:43:53
Вот горшок пустой, он предмет простой...
 
На последнем шаге В буфер НО 1. таблица остается в памяти в рамках расчета только одного запроса? или DaysToForecast в другом запросе -это уже обращение к буферу?
Изменено: volond75 - 05.04.2018 13:35:28
"Правильно заданный вопрос,половина ответа!"
 
Если данный код это отдельный запрос, то его финальный результат помещается в память и к нему можно обращаться по имени запроса из остальных. Если этот код будет частью запроса, и при этом шаг DaysToForecast не будет результатом его вывода, то обращаться к результату можно по имени шага и только в рамках этого запроса.
Изменено: PooHkrd - 05.04.2018 12:23:17
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал: Обратите внимание на процессы в диспетчере задач во время выполнения скриптов
1.List.Buffer или Table.Buffer освобождает один из Microsoft Mashup Evaluation Container?
2.Microsoft Mashup Evaluation Container это по сути соединение с кешированым файлом?
3.Как то можно обнулить буфер как в vba  tablebufer=Table.Buffer (table)  set  tablebufer=nothing
"Правильно заданный вопрос,половина ответа!"
 
1. Не понял вопрос
2. Этот процесс производит все вычисления, которые вы генерите вашим запросом
3. Как только запрос завершил работу память должна обнуляться автоматически. Способ принудительно в процессе работы запроса освобождать память мне не известен.
Вот горшок пустой, он предмет простой...
 
Применение в запросе List.Buffer или Table.Buffer освобождает один из Microsoft Mashup Evaluation Container? Просто тоже не понятно почему именно 4 шт Microsoft Mashup Evaluation Container  
"Правильно заданный вопрос,половина ответа!"
 
Нет, наоборот, загружает. Т.е. память выделяется в рамках той, которую забирают себе эти контейнеры. Про 4 штуки не знаю - это из моего личного опыта, какой бы супер запрос я не запускал, больше 4 штук в списке не наблюдал, хотя в наглоязычных интернетах видел упоминания и об 6 штуках. Есть подозрение что количество контейнеров может быть ограничено количеством потоков в процессоре. На моем ноутбуке их 4.
Вот горшок пустой, он предмет простой...
 
PooHkrd,
Цитата
PooHkrd написал:
Вот у них есть ограничения один процесс максимум 256 Мб, и одновременно может работать не более 4 процессов. Соответственно если вы напихаете в них данных больше чем на 1 Гиг, то все что сверху PQ начнет кэшировать на жесткий диск.
Подскажите, я пытаюсь закинуть в буфер данные из нескольких файлов, общий вес которых около 80МБ. Но при этом открывая диспетчер задач, я вижу что все 4 контейнера полностью заполнены, как такое возможно? Может эксель в файле xlsx данные сжимает, а когда я с ними работаю в power query они становятся больше?
 
Цитата
Владимир Колотиев написал:
Может эксель в файле xlsx данные сжимает
Не может, а сжимает. Если внимательно почитать про формат xlsx то первое что пишут: данный файл представляет собой не что иное как zip-архив.  ;)
Можете даже сменить расширение файла на zip и полазить по внутренней структуре файла. Сохраните ваши таблички в текстовом формате и придет примерное понимание сколько они займут места в памяти.
Ну и надо понимать, что помимо файлов, засунутых в буфер остальная память этих контейнеров также используется для проведения расчетов.
Изменено: PooHkrd - 14.01.2021 20:51:06
Вот горшок пустой, он предмет простой...
 
PooHkrd,
Цитата
PooHkrd написал: ...файл представляет собой... zip-архив.  
Ага, спасибо)

Цитата
PooHkrd написал:...остальная память этих контейнеров также используется для проведения расчетов.
Получается, если я все контейнеры займу кэшем из файлов, то обрабатываться запрос будет бесконечно? + Ресурс компьютера в виде ОЗУ снижается до 1ГБ? Будь у меня 32ГБ оперативы или 2ГБ, ситуация не поменяется?
 
Цитата
Владимир Колотиев написал:
обрабатываться запрос будет бесконечно
Это как попрет, может быть выдаст ошибку  о переполнении стека, а может не выдаст и начнет все что сверх 1Гб свопить на винт.
Цитата
Владимир Колотиев написал:
Будь у меня 32ГБ оперативы или 2ГБ
Это вы крайности приводите. Сформулируем так: будь у вас 2 гига свободной памяти или 1 гиг, для контейнеров это не важно, если на вашей системе максимум можно запустить 4 контейнера, то для PQ будет доступен 1 гиг. Ну и неплохо бы проверить мою гипотезу про зависимость количества контейнеров от количества потоков процессора, тогда возможны варианты.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Если данный код это отдельный запрос, то его финальный результат помещается в память и к нему можно обращаться по имени запроса из остальных
PooHkrd, а до каких пор результат запроса будет храниться в буфере? Просто у меня есть несколько запросов, которые хотелось бы кинуть в буфер, но как я понимаю, их суммарный вес не должен превышать 1ГБ? Я не могу кэшировать как-то поочередно, потому как механизм PQ предполагает РАНДОМНЫЙ порядок обновления запросов, т.е. я не могу знать, когда можно буфер освободить, а когда еще нет?
 
Цитата
Владимир Колотиев написал:
потому как механизм PQ предполагает РАНДОМНЫЙ порядок обновления запросов
Ого, откуда такое откровение? Или вы про независимые друг от друга запросы?
Хранение в памяти промежуточных результатов производится до момента завершения вывода итога запроса в выбранный вами формат (таблица, кэш сводной, модель данных) Как только выгрузка завершается - память высвобождается.
Цитата
Владимир Колотиев написал:
я не могу знать, когда можно буфер освободить, а когда еще нет?
А вы её и не освобождаете, этим занимается транслятор языка М во время расчетов. Если нужно обновлять одновременно разные таблицы, друг с другом не связанные, то можно запускать их в разных книгах, в отдельных копиях Эксель. В таком случае, под каждый Эксель будет выделена своя область памяти и для каждого Экселя будут доступны по 4(6?) контейнера.
Изменено: PooHkrd - 26.01.2021 16:10:36
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Ого, откуда такое откровение? Или вы про независимые друг от друга запросы?
PooHkrd, https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=95054&am... вот тут пост №10, Максим Зеленский говорил, что запросы обновляются в "какой-то последовательности", но здесь скорее всего имелось в виду использование костыля в виде предварительной выгрузки какого-либо запроса на лист(своеобразный кэш). Хотя и в отношении обычной цепочки запросов это утверждение может быть справедливо, начать обновляться первым может и финальный запрос, выводящий данные на лист, но вот завершить обработку он не сможет, так как нужен результат предпоследнего запроса и тд.
Изменено: Владимир Колотиев - 26.01.2021 16:22:52
 
Владимир Колотиев, вы не совсем правильно поняли.
Если внимательно прочитать ту драгоценную ссылку, которую Максим дал в том посте, то можно увидеть, что непонятки с очередностью выполнения запросов могут быть только в случае когда вы запускаете по сути независимые друг от друга процессы выгружающие разные таблицы, хотя сами запросы ссылаются друг на друга. Вывод в разные таблицы на лист, это вообще абсолютно независимые друг от друга процессы, и каждый из них выполняется независимо от других. И здесь действительно непонятно заранее какой запустится раньше, а какой позже. Тут использование буфера дает мало толка.
Вывод в модель данных уже не такой сумбурный, там в процесс обновления вмешивается еще и движок ПоверПивота, который тоже встраивает определенную очередность выполнения запросов. Здесь буфер все таки позволяет ускорить процесс обновления.
Вывод одного запроса, который использует в работе много других - вот тут создается упорядоченная цепочка запросов, для которой и имеет смысл применять буфера для хранения промежуточных итогов преобразований, т.к. иначе скорость выполнения может быть запредельной.
Все вышеописанное это мой личный опыт, который может не совпадать с теорией. Используйте на свой страх и риск.
Вообще постепенно пришел к тому, что больше не пишу монструозных запросов в PQ. Использую его по назначению, как ETL. А все расчеты исключительно отдаю в РР. Результат по скорости и удобству отладки превосходит все ожидания.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
проверить мою гипотезу про зависимость количества контейнеров от количества потоков процессора
у меня 12-потоковый , все равно 4 контейнера. Так что, если нельзя специально настроить их (контейнеров) количество, то не влияет
Изменено: genosser - 26.01.2021 17:02:31
 
Цитата
PooHkrd написал: Хранение в памяти промежуточных результатов производится до момента завершения вывода итога запроса в выбранный вами формат (таблица, кэш сводной, модель данных) Как только выгрузка завершается - память высвобождается.
PooHkrd, а если я запрос A1 никуда не вывожу, а использую в дальнейшем в другом запросе B1(который выводится на лист), при этом в А1 на одном из шагов создаю буфер, который будет использован в пределах этого же запроса. Тогда кэш освободиться после выполнения А1 или после вывода на лист B1?
 
Цитата
Владимир Колотиев написал:
после выполнения А1
Вот горшок пустой, он предмет простой...
 
Понял, спасибо!
 
PooHkrd, В общем, после всего обсуждения по кэшированию у меня вытекает финальный вопрос к вам, как к знатоку: как быть, если у меня есть большой объем данных (папка с xlsx файлами с историей продаж за каждый месяц за последние 3 года, в каждом файле порядка 300к строк), я подключаюсь к этому объему данных, затем провожу разные манипуляции в виде джойнов небольших кусочков данных из других маленьких таблиц. И вот тут начинается трэш, потому что по несколько раз идет обращение к источнику данных(та большая папка с файлами продаж), всё затягивается на десятки минут, при этом параллельно что-то делать на компе не получается, ибо весь ресурс процессора задействован (смотрел через диспетчер). Буферизация тут не поможет точно, так как слишком уж большой объем данных, но мириться с ситуацией не очень хочется. Можете что-то посоветовать, есть ли выход?
Так уж получилось, что в компании сейчас используют Excel + PQ как основной инструмент для анализа данных, планируют переходить на пайтон, но это только планы, а работу делать необходимо сейчас.
 
Откажитесь от xlsx и перейдите на csv
 
Цитата
mechanix 85 написал:
Можете что-то посоветовать, есть ли выход?
Могу, покажите ваш код с примером из пары/тройки файлов по 20-30 строк с обезличенными данными и всеми справочниками, которые привлекаются в процессе выполнения обработки. Понятно, что клинов на таком размере мы не увидим, но какие-то советы по оптимизации кода, думаю вам накидаем сообща.
Только делать это все нужно согласно правил форума, в новой теме с описанием в названии темы смысла выполняемой задачи (тема с названием "Оптимизация кода" не прокатит, модераторы закроют).
Заранее предупреждаю, что если потребуется серьезная переделка кода, то не исключен переезд темы в раздел Работа, но это уже по вашему желанию.
Вот горшок пустой, он предмет простой...
 
PooHkrd, вот тут https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=136763&a... создал новую тему.
 
genosser, PooHkrd,
Цитата
genosser написал:
у меня  12-потоковый  , все равно 4 контейнера. Так что, если нельзя специально настроить их (контейнеров) количество, то не влияет
у меня 16 потоков, а контейнеров 7, правда впервые вижу, чтоб 7 контейнеров у меня было задействовано, раньше тоже только 4 было. Не понятно, от чего это зависит.
 
Владимир Колотиев,
Цитата
PooHkrd написал:
под каждый Эксель будет выделена своя область памяти и для каждого Экселя будут доступны по 4(6?) контейнера.
может это тот самый случай?
Страницы: 1
Наверх