Всем привет. В рамках сбора данных приходится к 7-10 разнородных таблиц фактов джойнить пару столбцов из одного и того же большого справочника, PQ со своими "ленивыми вычислениями" каждый раз лезет за данными из этого справочника на (мееееедленный) сетевой жесткий диск, а хочется как-то ускорить процесс. Вот здесь Андрей VG использовал функцию Table.Buffer для обращения к еще одному источнику в пределах одного запроса и размещения его в оперативной памяти. По справке МС не очень понятно: 1. таблица остается в памяти в рамках расчета только одного запроса? 2. если нет, то можно ли в одном запросе запихать какой-то справочник в память и обращаться к нему из других запросов? 3. если на предыдущие вопросы ответ да, то ускорит ли это вычисления?
PooHkrd написал: 1. таблица остается в памяти в рамках расчета только одного запроса?
Да 2. 3 Переходите на базу данных с индексированием столбцов таблиц по полям соединения и пополнением/изменением только нужных данных. Тут другого пути повышения производительности нет. Всё же ETL не предназначен для постоянного использования в расчётах.
А разве если вы одним запросом (назовем его "Выборка общая") сделаете выборку данных из этого "большого справочника" (со всеми полями и строками, необходимыми для всех этих 10 запросов-фактов), а в запросах в качестве источника для слияния укажете этот уже запрос-выборку - то разве при выполнении каждого из 10 последующих запросов будет постоянно 10 раз исполняться "Выборка общая"? Может, у вас просто джойны так хитро сделаны, что это происходит?
Ну почему же. Скорость обработки запросов - больная тема для Экселевского PQ (при этом ПаверКвэри для PowerBI Desktop, якобы, с точки зрения скорости обработки запросов работает чуть ли не в 10 раз быстрее). И эту проблему разработчики будут решать в любом случае. Вопрос - "когда" и "какого качества будет решение". На вопрос "когда" мы повлиять можем, т.к. приоритеты в решении задач, как это ни странно, могут устанавливать, в том числе, и пользователи. Почему бы не ускорить? А на качество мы повлиять, к сожалению, не сможем.
vector1 написал: ПаверКвэри для PowerBI Desktop, якобы, с точки зрения скорости обработки запросов работает чуть ли не в 10 раз быстрее
А можно ссылку на источник? Потому что насколько я знаю - нет такой разницы, т.к. инструмент фактически один и алгоритмы там одинаковые. Не думаю, что может быть такая разница в скорости.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Андрей VG написал: Переходите на базу данных с индексированием столбцов таблиц по полям соединения и пополнением/изменением только нужных данных.
Да оно мне не сильно надо, я ж PQ с PP использую только чтобы накидать модельку посчитать чего интересного из вагона разрозненных источников, по текущем проекту. Если следующий не будет связан с подобными расчетами то и работа с такими БД для меня будет избыточна. По факту требуется чтобы запрос и расчет сводного отчета выполнялся не 5-7 минут, а 1-2. Этого теперь добиваюсь в основном правильным комбинированием PQ и PP. Но все равно затащить в запрос данные из файла без написания к нему отдельного запроса - это интересно. За прием спасибо.
The_Prist написал: т.к. инструмент фактически один и алгоритмы там одинаковые. Не думаю, что может быть такая разница в скорости.
инструмент один, но внутри разных инструментов. В Excel есть некая архитектурная особенность (отсутствующая в Power BI), которая не позволяет ему эффективно кэшировать данные для передачи между запросами. Поэтому часто бывает, что Excel тыкается по нескольку раз в один и тот же источник (особенно если это файл). Плюс в Excel не работает автоматическое определение зависимостей запросов при обновлении. Т.е. условно, если в Excel нажать "Обновить всё", то запросы будут обновляться в КАКОЙ-ТО последовательности. В то время как Power BI выстраивает цепочку запросов, выполняет их в правильном порядке и кэширует нужные результаты в едином кэше (ну, примерно так). В общем, там много тонкостей, в том числе и что кэшируется, и как кэшируется, и каков источник данных. Вот здесь я почти год назад спрашивал у MS, и ответили очень подробно (хотя это и не так просто освоить с разбегу), как работает цепочка выполнения запросов. Это стоит почитать, хоть с гуглопереводчиком, хоть как
По поводу буфера: вот так:
Код
// 'Query1
let
Step = get some table
in
Table.Buffer(Step) // 'загнали в буфер
// 'Query 2
let
Source = Query1 // 'получили из буфера
...
Максим, а если попробовать сделать "типа локальное кэширование" вот в таком виде: - Сначала тянем запросом (назовем его Q0ext) данные из внешнего источника, и выгружаем их на лист. - Затем создаем запрос q0, который в качестве источника будет использовать таблицу Q0ext из книги - Затем уже в наших запросах (q1-q10) при джойнах используем данные из источника q0 С одной стороны, конечно, при нажатии общей кнопки "Обновить всё" движок полезет и обновлять Q0ext. С другой стороны, при выполнении запросов q1-q10, по идее, запрос Q0ext исполняться заново каждый раз не должен - ведь в их планах есть только источник q0, у которого, в свою очередь, источник данных - не внешний ресурс, а таблица Q0ext в книге. Конечно, здесь возникает вопрос с размером этого самого Q0ext - может, там миллионы записей...
В связи с вышеизложенным вопросы: Имеем один запрос, внутри него есть шаг N Далее на шаге N+а я результат его выполнения джойню к таблице из шага N Будет ли в таком случае шаг N выполняться 2 раза, т.к. для получения шага N+а мне его тоже было нужно пройти, а потом я опять обращаюсь уже непосредственно к нему? Получается что если мое предположение верно, то именно в такой ситуации Table.Buffer - это то что доктор прописал, чтобы кэшировать промежуточную таблицу шага N и к ней потом обращаться, так? И последнее, получается, что пока я внутри запроса все таблицы, которые я загоню в буфер будут там храниться, как только я выхожу из запроса, таблицы оттуда вылетают, а функции принудительной очистки буфера внутри запроса не предусмотрено, правильно?
Попробовал Table.Buffer в одном своем старом запросе, в котором собирались данные из 10 файлов по 10-50 мб, а потом добавлялось с десяток вычисляемых столбцов. Этот запрос вычислялся на 8-ядерном ксеоне с 32 гигами 7-8 минут и частенько зависал. После этого я разделил запрос на 2 - сначала тащил данные и сохранял их на листе в таблице, после чего второй запрос брал сведенные данные из этой таблицы и делал расчетные столбцы. Сейчас вместо этой таблицы использовал буфер, в результате запрос стал стабильно выполняться на селероне с 2 гигами оперативки за 2 минуты.
Андрей VG, Маким Зеленский, спасибо вам огромное за помощь.
AndreTM, Без сомнения, такой "ручной" кэш сильно ускорит обработку, даже при "Обновить всё" (но тут я бы не советовал его жать, потому что никаких гарантий, что он не обновится после остальных запросов). С другой стороны, даже макрорекордер нам поможет в этом случае, так как нам надо всего лишь пообновлять в нужном порядке таблицы. Но, как вы правильно заметили, вопрос в размере Q0ext.
Цитата
AndreTM написал: Затем уже в наших запросах (q1-q10) при джойнах используем данные из источника q0
да, всё верно. Таким образом, кстати, можно организовать инкрементную загрузку данных, которая сейчас в PQ и в Power BI пока не реализована (и сомневаюсь, что в ближайшее время будет реализована где-то, кроме Power BI Premium).
Цитата
PooHkrd написал: Имеем один запрос, внутри него есть шаг N Далее на шаге N+а я результат его выполнения джойню к таблице из шага N Будет ли в таком случае шаг N выполняться 2 раза, т.к. для получения шага N+а мне его тоже было нужно пройти, а потом я опять обращаюсь уже непосредственно к нему?
Предположим, на шаге N+a вы джойните N+a-1 c N. Если для получения N+a-1 вы используете N, то есть вероятность, что N может быть посчитан еще раз. Как писал Ehren,
Цитата
All M values are nominally immutable, but as I mentioned above, this isn't always true for complex values that are enumerable and for which each enumeration can force the underlying source to be revisited. This includes lists, tables and binary values.
То есть, скалярные величины не пересчитываются заново (например, число строк таблицы). А таблицы, списки и бинарные данные - могут пересчитываться (этому есть N объяснений). В принципе, об этом и в документации по языку есть, но не так внятно.
Цитата
PooHkrd написал: Получается что если мое предположение верно, то именно в такой ситуации Table.Buffer - это то что доктор прописал, чтобы кэшировать промежуточную таблицу шага N и к ней потом обращаться, так?
Если вы сначала пихнете шаг N в буфер, то на шаге N+a будет использоваться таблица из буфера. Но не забудьте, что из другого запроса вы не можете обратиться к шагу N этого запроса.
Цитата
PooHkrd написал: пока я внутри запроса все таблицы, которые я загоню в буфер будут там храниться, как только я выхожу из запроса, таблицы оттуда вылетают, а функции принудительной очистки буфера внутри запроса не предусмотрено, правильно?
В общем - да. Только с вот такой поправкой:
Код
section Section1;
Query1 =
let
StepN = Table.Buffer(blablatable) ' этот буфер будет жить только внутри Query1
....
Q1Result = Table.Buffer(some table expression) ' а эта величина является результатом вычисления Query1, и вот этот буфер может действовать между запросами.
in
Q1Result;
' или
' Q1Result = some table expression
' in
' Table.Buffer(Q1Result);
Query2 =
let
Step1 = ...,
StepN = Q1Result,
...
in
Q2Result;
и да, по завершении расчета всех запросов буфер освободится.
Андрей VG написал: Переходите на базу данных с индексированием столбцов таблиц по полям соединения
(sorry что вклиниваюсь - но уж очень интересную тему затронули - спасибо ТСу...) и для уточнения: а какая БД лучше для этих целей? (индексирования)?... попроще - типа SQLite даёт такие возможности?.. (и желательно с простотой использования оконных функций) - и чтоб для Windows, а не Oracle под Linux
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
А это зависит от того, сколько будет пользователей. Если до 10, то можно на любом компе с Windows 7/10 (ограничение по подключениям, если не ошибаюсь, а если ошибаюсь, то БМВ подправит - 10 штук). А если больше - тогда либо нужен Windows Server или, увы, Linux, благо что все вышеперечисленные движки баз данных вполне себе на него ставятся.
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
это в смысле на Windows Server 2008 ставить или просто на Windows 7 ?? или какие ? я Win7 имела ввиду - я на win7 работаю - она меня и интересует... на MSDN нашла ответ: Win Home Edition - да есть ограничения, Win Server - намного больше подключений можно создать... а LocalDB - (как для меня?) - no remote connections - только со своего компа (проще в установке и не требует администрирования)... хотя у ExpressAdvanced - свои преимущества... (и в любом случае SQL-Server-Management-Studio ! Complete - для исп-я Profiler - тест скорости бд) - recap at a glance))... всем спасибо за ликбез
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
timel1 написал: Попробовал Table.Buffer, но скорость только уменьшилась.
К сожалению не могу, ибо без исходников от них смысла ноль. В чем мне помог Table.Buffer на версии Excel полуторогодовой давности: если смысл запроса, в том, чтобы к исходной таблице приджойнить всяких справочных данных из кучи разных файлов, после чего делать с десяток столбцов, с вычислениями if then и тому подобным, которые используют эти справочники. В этом случае запрос начинал повторно по-многу раз обращаться к одному и тому же источнику. Победил так: сначала выполняю все джойны, результат в буфер, потом выполняю вычисления, тогда обращения к исходникам происходит по одному разу, и все. Вообще говоря я выполнял задачу, которую обычно выполняют макросами, и, уверен, макрос бы все посчитал секунд за 20-30 вместо 2.5-3 минут. Вот только свободного ВБА-шника не было под рукой - решал задачу чем было. Выкладывать не буду так же из соображений - стыдно . Это было на заре моего знакомства с PQ, так что многие решения сейчас бы уже сделал совсем иначе, и работать все стало бы гораздо быстрее. Но Времени на это нет от слова совсем, как бы оно и так работает - лучше не трогать. Лучшее враг хорошего (работающего).
Цитата
timel1 написал: Попробовал Table.Buffer, но скорость только уменьшилась.
Возможно что-то подкрутили в построении цепочки запросов в Excel'евском PQ и стало нормально работать без извращений с буфером. Так то время на буферизацию больших таблиц тоже откушивается не мало.
Может быть кто-то может посоветовать, если выложу примеры кода.. Ранее выгружал один и тот же файл проводок в 3 запроса, где производил различные действия. Для использования Table.Buffer сделал для повторяющегося действия, а именно загрузки файла отдельный запрос, результат которого забуферизировал. Потом в других запросах сделал ссылку на него.
Запрос osnova
Код
let
Источник =Folder.Files(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Значение]& "\БАКЛАЖАН"),
#"Удаленные столбцы" =Table.RemoveColumns(Источник,{"Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Строки с применным фильтром2" = Table.SelectRows(#"Удаленные столбцы", each Text.Contains([Extension],"xls")),
#"Добавлен пользовательский объект" =Table.AddColumn(#"Строки с применным фильтром2","Custom", each Excel.Workbook([Content])),
#"Удаленные столбцы1" =Table.RemoveColumns(#"Добавлен пользовательский объект",{"Content"}),
#"Развернутый элемент Custom" = Table.ExpandTableColumn(#"Удаленные столбцы1", "Custom", {"Name","Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind",
"Hidden"}),
#"Строки с применным фильтром3" = Table.SelectRows(#"Развернутый элемент Custom", each ([Name.1] = "TDSheet")),
#"Строки с применным фильтром1" = Table.SelectRows(#"Строки с применным фильтром3", each Text.Contains([Extension], "xls")),
#"Переупорядоченные столбцы" = Table.ReorderColumns(#"Строки с применным фильтром1",{"Name", "Extension", "Name.1", "Item", "Kind", "Hidden", "Data"}),
#"Удаленные столбцы2" = Table.RemoveColumns(#"Переупорядоченные столбцы",{"Name.1", "Item", "Kind", "Hidden"}),
#"Развернутый элемент Data" = Table.ExpandTableColumn(#"Удаленные столбцы2", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6",
"Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19",
"Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31",
"Column32", "Column33"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13",
"Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26",
"Column27", "Column28", "Column29", "Column30", "Column31", "Column32",
"Column33"}),
#"Повышенные заголовки" = Table.PromoteHeaders(#"Развернутый элемент Data"),
#"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"N", type text}}),
#"Строки с применным фильтром" = Table.Buffer(Table.SelectRows(#"Измененный тип", each [N] <> "N"))
in
#"Строки с применным фильтром"
Пример запроса
Код
let
Источник = osnova,
#"Строки с применным фильтром" = Table.SelectRows(Источник, each ([N] <> "N") and ([Счет Кт] = "90.01.1")),
#"Добавлен пользовательский объект1" = Table.AddColumn(#"Строки с применным фильтром", "Контрагент_строки", each if
[Счет Дт]="50.01" then "---РОЗНИЦА---" else [Субконто1 Дт]),
#"Переупорядоченные столбцы1" = Table.ReorderColumns(#"Добавлен пользовательский объект1",{"Контрагент_строки", "БАКЛАЖАН---6_мес_2018---30.10.2018.xlsx",
".xlsx", "N", "Дата", "Документ", "Организация", "Счет Дт", "Кол.", "Количество Дт", "Валюта Дт", "Вал. сумма Дт", "Подразделение
Дт", "Субконто1 Дт", "Субконто2 Дт", "Субконто3 Дт", "Счет Кт", "Кол._1", "Количество Кт", "Валюта Кт", "Вал. сумма Кт", "Подразделение Кт",
"Субконто1 Кт", "Субконто2 Кт", "Субконто3 Кт", "Сумма", "Содержание", "НУ", "ПР", "ВР", "Сумма НУ Дт", "Сумма ПР Дт", "Сумма
ВР Дт", "Сумма НУ Кт", "Сумма ПР Кт", "Сумма ВР Кт"}),
#"Сгруппированные строки" = Table.Group(#"Переупорядоченные столбцы1", {"Контрагент_строки", "Документ"}, {{"N_max", each List.Max([N]), type text}})
in
#"Сгруппированные строки"
День добрый. меня этот вопрос заинтересовал, давно хотел что то подобное провернуть. А если создать новый запрос так? Это не решит проблему?
Код
let
Источник = #shared,
#"Table Buffer" = Источник[Table.Buffer],
#"Вызванная функцияTable Buffer" = #"Table Buffer"(Запрос1)
in
#"Вызванная функцияTable Buffer"
Хотя нет. Не то...У меня Запрос1 ссылается на файл на 2гига, преобразуется в 60 мб. Запрос2, который я через буфера запустил на Запрос1, все равно обработал 2гига информации.
timel1 написал: Table.SelectRows(Источник, each ([N] <> "N")
зачем это во втором запросе, если в osnova уже отфильтрованы на последнем шаге эти значения?
Во-вторых, если объем памяти нужен большой, то может происходить переполнение буфера, и тогда содержимое памяти... кэшируется на диск. Понимаете, в чем засада?
Максим Зеленский написал: зачем это во втором запросе, если в osnova уже отфильтрованы на последнем шаге эти значения?
Там еще фильтр на кредит счета. Это условие пока не убрал, спасибо.
Цитата
Максим Зеленский написал: Во-вторых, если объем памяти нужен большой, то может происходить переполнение буфера, и тогда содержимое памяти... кэшируется на диск. Понимаете, в чем засада?
Понимаю.
Есть какой-то внутренний механизм у PQ по мониторингу времени исполнения каждого запроса, а лучше каждого отдельного шага? В идеале, если это также будет информация и о потребляемой памяти. В логах трассировки может быть??
Что-то можете еще порекомендовать для увеличения производительности? В файле исходника - 600 т. строк. Я уже пробовал и перевод в csv - увеличение скорости несущественное. Пока грешу на умные таблицы. Они иногда при загрузке новых данных начинают жестко тормозить..
P.S. Ранее загружал еще больший файл с проводками в модель PP. Так вот там запрос обновлялся довольно долго, но процесс не тормозил, "не зависал"..
timel1, 600к строк это объем для PQ ни о чем. Если выгружаете в таблицу, то основной тормоз в ней это автоподбор ширины строки при выводе данных. Уберите галочку как на картинке и будет вам щястье. По умолчанию эта галочка включена всегда. ПКЛ по таблице, выбираете выпадающее меню "Таблица", в нем пункт "Свойства внешних данных"