Страницы: 1 2 След.
RSS
Почему Power Query так медленно работает?
 
Подскажите в чем причина медленной работы самого редактора запросов?

С Power Query начала работать когда нужно было обрабатывать файлы с более милиона записей, все это соединять, обрабатывать и т.д. Так как работа с таким большым количеством даных для меня была впервые, я не особо парилась что долго, ведь даных то тоже много. Больше особо его не использовала, так как хватало формул и сводных.

Сейчас в силу кое каких причин не могу как результат предоставлять сводную, а нужно только просто несколько обычных таблиц результата с возмоностью обновления
Источник файл -130тис. строк всего лиш. Но продолжительность работы редактора запросов просто убивает желание с ним работать. То что в сводной делаеться в несколько минут, в редакторе запросов несколько часов... Делаешь любое "движение" и файл начинает думать..., долго..., и так при любом измении таблицы.

Мне казалось что если убрать все лишнии столбцы и профильтровать даные в самом начале это должно прискорить обработку, но нет . Даже тогда когда в таблице осталось всего 600 строк и при добавлении следующего крока редактирования файл дальше думает..., через некоторое время снизу видно как перечисляеться сколько строк он уже обработал, и вижу что он опять начинает обрабатывать 130тис. строк.  

Железо вроде нормальное, ноут с видяхой и оперативкой 12  и стационарный комп с оперативкой 16.
 
JosephineUa, вы хотя бы запрос показали бы
 
Вот пример простенькой обработки, все тоже самое что делает сводная.
Код
let
    Источник = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="tFileName"]}[Content]{0}[FileName]),true),
    #"выгрузка" = Источник{[Name="выгрузка"]}[Data],
    #"Измененный тип" = Table.TransformColumnTypes(#"выгрузка",{{"Column 1", type text}, {"Column 2", type text}, {"Column 3", type text}, {"Column 4", type text}, {"ТМ", type text}, {"ID", Int64.Type}, {"Column 6", type text}, {"Column 7", type text}, {"Column 8", type text}, {"Column 9", type text}, {"Column 10", type text}, {"Column 11", type text}, {"Date", type date}, {"Column 13", Int64.Type}, {"Column 14", Int64.Type}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Column 2", "Column 4", "ТМ", "ID", "Column 6", "Column 7", "Column 8", "Column 9", "Date", "Column 11"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Удаленные столбцы", each ([Column 14] = 1)),
    #"Замененное значение" = Table.ReplaceValue(#"Строки с примененным фильтром","Манго-лимон","Манго-Лимон",Replacer.ReplaceText,{"Column 1"}),
    #"Сгруппированные строки" = Table.Group(#"Замененное значение", {"Column 1", "Column 3", "Column 10"}, {{"Column 13 Sum", each List.Sum([Column 13]), type number}, {"Column 14 Sum", each List.Sum([Column 14]), type number}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "TI", each Number.Round ([Column 13 Sum]/[Column 14 Sum]*100, 2)),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"TI", type number}}),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Измененный тип1",{"Column 13 Sum", "Column 14 Sum"}),
    #"Сортированные строки" = Table.Sort(#"Удаленные столбцы1",{{"Column 1", Order.Ascending}, {"Column 3", Order.Ascending}, {"Column 10", Order.Ascending}}),
    #"Строки с примененным фильтром1" = Table.SelectRows(#"Сортированные строки", each ([TI] <> 0)),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Строки с примененным фильтром1", "Индекс", 1, 1),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Добавлен индекс",{"Индекс", "Column 1", "Column 3", "Column 10", "TI"})
in
    #"Переупорядоченные столбцы"
Изменено: JosephineUa - 26.04.2019 21:45:19
 
JosephineUa, Вы видели, как выглядит код у других? Вот и Вы его так же форматируйте. Для этого даже специальная кнопочка есть (см скрин). Найдите её и исправьте своё сообщение.
 
Спасибо, исправила
 
JosephineUa,на вскидку код совсем легкий и тормозить не должен. могу сделать несколько предположений:
1 в шаге с группировкой в колонке 13 есть нет только цифры но и ошибки или текст  -  PQ это не любит
2 в вашем файле этот запрос не единственный и при изменении одного начинают обновляться все остальные
3 источник данные я не совсем понял - это текущая книга эксель, а дальше что? все листы книги? какая то пользовательская функция? может где то здесь томоза

попробуйте последовательно устранить каждый из пунктов, и сравните быстродействие
 
Цитата
Blood81 написал:
3 источник данные я не совсем понял - это текущая книга эксель, а дальше что? все листы книги? какая то пользовательская функция? может где то здесь томоза
В даном случае источник даных текущая книга не отформатированая как таблица. Что б файл мог ссылаться сам на себя не зависимо от расположения самого файла,

Power Query работает медлено на этапе создания самого запроса, тоесть что б его создать уходить очень много времени, так как после каждого шага он долго думает и приходиться просто ждать.
Изменено: JosephineUa - 26.04.2019 22:15:04
 
Доброе время суток.
Цитата
JosephineUa написал:
и вижу что он опять начинает обрабатывать 130тис. строк.  
Power Query работает по модели с ленивыми вычислениями, перестраивая внутренний код с каждым новым шагом.
Но! Вопрос, а зачем создавать запрос сразу на 100500 строк данных? Этого нельзя никак сделать, подготовив/выбрав сотню другую строк? А уже потом готовый запрос простым Ctrl+C, Ctrl+V перенести в рабочий файл, возможно подправив путь и имя источника данных.
А если вы будете заниматься разработкой для промышленных систем, например, биллинга сотового оператора, то вы будете сразу на 100 миллионов/миллиарде другом строк код писать и отлаживать?
 
Цитата
Андрей VG написал: Power Query работает по модели с ленивыми вычислениями, перестраивая внутренний код с каждым новым шагом.
Хм, спасибо за обьяснение.
Цитата
Андрей VG написал: Этого нельзя никак сделать, подготовив/выбрав сотню другую строк? А уже потом готовый запрос простым Ctrl+C, Ctrl+V перенести в рабочий файл, возможно подправив путь и имя источника данных.
Обычно я б так и делала. Но задача поставлена немножко подругому, и к сожалению я ничего с этим сделать не могу... Должен быть только один CTRL+V и кнопка обновить (или скрипт), без формул, сводных и т.д.

Цитата
Андрей VG написал: А если вы будете заниматься разработкой для промышленных систем
Для этого наверно больше подойдет Pyton/R, наверное пора начинать осваивать)

 
Изменено: vikttur - 23.08.2021 23:37:12
 
Чтобы написать макрос, нужно видеть хоть немного исходных данных и желаемый результат.
 
Думаю у вас проблема вот в этой строке
Код
#"Замененное значение" = Table.ReplaceValue(#"Строки с примененным фильтром","Манго-лимон","Манго-Лимон",Replacer.ReplaceText,{"Column 1"}),

Думаю  что это итерация по строкам и столбцам. Если их очень много в таблице, то и результат будет очень долгий
Изменено: DrillPipq - 01.05.2019 09:27:01
 
Цитата
JosephineUa написал:
и к сожалению я ничего с этим сделать не могу..
Извините, но поверить не могу, что нельзя сделать упрощённую выборку, чтобы на её базе делать код. Чем бы вы не делали, на больших объёмах никакая среда не поможет выполнять отладку быстро - чудес не бывает.
Протестировал, пример данных прилагаю. База строилась с использованием СЛУЧМЕЖДУ, Манго-лимон заменялся 200000 раз (всего строк в источнике 200000). Финальная выборка 199993 строки (переборщил с диапазонами СЛУЧМЕЖДУ 1-1000000 в группирующих столбцах). Время выполнения на нетбуке восьмилетней давности чуть меньше минуты (одно ядро, 2Гбайта ОЗУ). Код чуть прооптимизировал.
Код
let
    Source = Excel.Workbook(File.Contents("c:\path\demoData.xlsx"),true){[Name="выгрузка"]}[Data],
    needed = Table.SelectColumns(Source, {"Column 1", "Column 3", "Column 10", "Column 13", "Column 14"}),
    upLimon = Table.TransformColumns(needed, {"Column 1", each Text.Replace(_, "Манго-лимон","Манго-Лимон"), Text.Type}),
    typedGroupCols = Table.TransformColumnTypes(upLimon, {{"Column 3", type text}, {"Column 10", type text}}),
    calcTI = Table.Group(typedGroupCols, {"Column 1", "Column 3", "Column 10"}, 
        {"TI", each Number.Round(100 * List.Sum([Column 13]) / List.Sum([Column 14]), 2), Number.Type}
    ),
    neededTI = Table.SelectRows(calcTI, each [TI] <> 0),
    ordered = Table.Sort(neededTI, {{"Column 1", Order.Ascending}, {"Column 3", Order.Ascending}, {"Column 10", Order.Ascending}}),
    addIndex = Table.AddIndexColumn(ordered, "Индекс", 1, 1),
    return = Table.ReorderColumns(addIndex,{"Индекс", "Column 1", "Column 3", "Column 10", "TI"})
in
    return

Вывод, если так тормозит, то проблемы со входным файлом - много лишнего в описании структуры - долгий разбор xml структуры.
Изменено: Андрей VG - 27.04.2019 00:01:48
 
JosephineUa, попробуйте мои советы отсюда.
А ещё после крайних обновлений могут подвисать процессы, которые запускает редактор. Проверяется так: закрываете редактор, смотрите в диспетчер задач, если ресурсы процессора кушаются, значит процесс завис. Лечится закрытием/открытием книги.
Изменено: PooHkrd - 27.04.2019 01:47:25
Вот горшок пустой, он предмет простой...
 
Использую повер кверри для слияния и соединения файлов, после добавления новых данных новыми файлами, "Обновить все"  обновляет часа четыре...
Подскажите, может ли быть проблема в:
1. Файлы в нескольких запросах в формате xls, в других нескольких запросах в xlsx
2. Это можно сказать моя первая сборка повер, и на этапе "строительства" не определил файл примера, остался по умолчанию "первый". После нескольких обновлений  увидел это, и перед каждым обновлением стал открывать и закрывать с сохранением самый меньший по данным файл, что бы он был файлом примера. Думаю,но не уверен, что так системе будет "легче", чем каждый раз в качестве примера использовать новый файл с новыми данными. Но по итогам первых обновлений, в "Подключения" остались следы, может они мешают
Изменено: Mihail178 - 23.08.2021 23:42:25
 
Mihail178, xls не нужны тут, это о-о-очень медленно. Надо xlsx, а лучше всего текст - это самое быстрое чтение данных с диска. Диск лучше чтобы SSD, а если сеть то гигабитная.
Ну, и еще нужно ваш запрос смотреть, т.к. если он не оптимален, то может происходить многократное чтение одних и тех же файлов.
Вот горшок пустой, он предмет простой...
 
Отчего PQ  медленно вытягивает данные из источника? В источнике мало данных  А подключение оч долго происходит.. Мало того при переносе на другой комп где много пользователей Экселя  начинаются чудеса  Меняется ранее прописанный динамический путь как правило производятся попытки найти источник  данных в какой-либо открытой на этот момент на компе папке  Причем путь перезаписывается самостоятельно  в табличке с прописанным путем
Может из-за скрытых листов или именованных диапазонов (битых)  Может из-за добавления запросов друг к другу....Но тормоза!!!!
Причем как то через раз Бывает довольно быстро (сек 10-15) а бывает мин 3-5

Пример прикладывается
Прошу помочь\
Запросы просты как правда: Сбор идет из красных вкладок (в одной из них чуть отличная структура)

Делалось в 2016  Использоваться будет в 2010 (но это не сильно важно для такого уровня запросов как я понимаю)
При помещении на сервер удаленный начинаются проблемы (вернее запросы) конфиденциальности
Их как порешать?

Пример загружу "по требованию"

ПС  Файл-Источник  не вмещается сюда. Хотя на самом деле он не должен весить столько сколько весит. Все преобразования описанные  в соответствующей статье Н Павлова не помогают
Даже после удаления всего из файла пустой весит 420Кб. Это же ненормально?
 
Может и мне подскажете.

задача в следующем в таблице 'accural' указать хто принес результат
есть

1) таблица фактов
250 000 строк 11 000 кілобайт
http://joxi.ru/MAj8BlPT16O6xm
какой ресурс в какой день и в какое время принес результат

2) таблица с сотрудников что работали с ресурсом и в какой период
360kb           7 000 строк
http://joxi.ru/RmzdokPTj9K9jA

id_resource - ресурс
id_employees - сотрудник которий работал с ресурсом
date_started - когда начался период работи на ресурсе
date_finished - закончился период
time_started - время смены начало
time_finished  - время смены конец

есть функиция которая просматривает условия
смотрит в таблицу "accural" значения ресурс, дата и время начисления
дале в табл. "rules" ищет строку которая отвечает условиям и забирает с найденой строки значение "id_employes"  и подставляет значение в строку  в таблице "accural"
вот эта функция
Код
(aResource as text, aDate as date, aTime as time )=>
let
    Джерело = new_rules,
    #"Відфільтровані рядки" = Table.SelectRows(Джерело, each ([resource] = aResource) and ([position] <> 372)),
    #"Відфільтровані рядки1" = Table.SelectRows(#"Відфільтровані рядки", each [Started] <= aDate and [finished] >= aDate),
    #"Відфільтровані рядки2" = Table.SelectRows(#"Відфільтровані рядки1", each [Shift_started] <= aTime and [Shift_finish] >= aTime),
    ID_Employees = #"Відфільтровані рядки2"{0}[ID_Employees]
in
    ID_Employees

все вроди бы работает в редакторе
но когда я закрываю редактор "закрить и загрузить" загрузка занимает более полу дня, я наночь делаю обновления.
окно загрузки powerBi в это врямя показывает что загрузилось около 9 Г это за час а грузиться пол дня тоесть цыфра возможно достигает 100 Г
вопрос откуда такая цифра? что не так я сделал. возможно такие вещи не считаються в PowerQuery
 
отчего PQ долго отрабатывает в в фоновом режиме?
И самопроизвольно меняет путь к фацлу источника в случае написания динамического пути к файлу и размещении файла источника и файла сборщика на сервере
 
,
не до конца вас понял, я не очень сильно в этом всем разбираюсь((

тоесть при тех условиях что у меня ничего странного что так долго обрабативает? и если да! то не подскажете ли вы каким другим путем можно решить эту задчу?(
 
Oleh, для Ваших данных это очень долго. А есть файл с полным кодом запроса?
Изменено: surkenny - 20.11.2021 21:44:40
 
есть, если я вас правильно понял
https://fex.net/uk/s/r8mpevd
я тут сильно урезал, оставил только одну функцию но все равно загрузка в файл бесконечна(((
 
Oleh, Table.Buffer() последнего шага rules немного ускорит. Но Вы функцией заставляете 250000 раз фильтровать таблицу из 6000 строк.
Если подумать логически, то нас интересует только время начала в rules. Если доступ сразу у нескольких пользователей, но я вообще не знаю, как определить:) Вернее, по моему коду будет тот, кто получил доступ последним (но не ранее соответствующей операции), если получили доступ одновременно - тот, у кого дата окончания больше.
Так вот: мы просто к таблице данных добавляем таблицу rules и сортируем по дате. Затем заполняем вниз нужное Вам значение.
Посмотрите внимательно шаги запросов, чтобы понять суть.
Единственный минус - если в таблице rules нет данных на какое-то число, то заполнится тот, кто был последним.
Время выполнения на 250 000 строк 4,8 сек:

Код:
rules:
Скрытый текст

accural:
Скрытый текст
Изменено: surkenny - 21.11.2021 01:38:30
 
Спаисбо, за помощь.  попробую Table.Buffer() когда еще немного оптимизирую модель.

Я сначала так и считал но в одном периоде может быть несколько сотрудников в разные смены. фильтр по времени(

есть идея как оптимизировать правила и разделю файлы начислений, пока не найду ответ.
Ранее считал в екселе, бы ло бистрее, только там немного подругому по частям считало. 10 файлов. Хоть много делалось в ручную результат получал быстрее(((

Скажите пожалуйста где можна увидеть время выполнения запроса) интересно.

И другое ресурс компютера влияет на скорость вычислений по логике да. но мой не задействован по максимуму и не только мой. Оперативка максимум 8 с 24, а процесор так вообще до 10% изредка 20%.  
 
Oleh, Вы не поняли. Вы хоть смотрели код? Результат будет корректен, если сотрудники были в одну дату, но в разное время. Проблема, когда есть несколько сотрудников, у которых пересекается одно и то же время.
Но! Такого не должно быть в данных, так как тогда вообще нельзя определить одного сотрудника.
Ваш код в таком случае тоже выберет лишь первого из таблицы.

upd:
Наверное, некорректно написал, что сортируем по датам. Сортируем по дате и времени внутри каждого ресурса. Третья сортировка по полю date только лишь для того, чтобы когда совпадают датавремя события и доступа, строка из rules была выше и при заполнении вниз ответственный заполнился у этого события корректно.
Если не бывает такого, что доступ одновременно у нескольких сотрудников (есть событие 20.03.21 10:00:00, доступ а 10.01.21 12:00:00 - 25.03.21 12:00:00, доступ б 10.02.21 12:00:00 - 24.03.21 12:00:00), то все отработает корректно. Используйте мой код, он точно будет быстрее.

Если же во время события есть доступ у нескольких людей, то кого выбирать? В моем коде выбирается тот, кто получил доступ позднее (из примера выше это будет б). Если уж несколько человек получили доступ одновременно, то выберется тот, у кого доступ позднее закончился. Если и конец доступа совпадает, то выберется какой-то один :)
Ваш код такие ситуации вообще не обрабатывает. По Вашему коду, если во время события доступ у нескольких людей, то таблица rules, отфильтрованная функцией, будет содержать несколько строк и выберется тот, кто в первой строке.
Изменено: surkenny - 21.11.2021 13:33:46
 
Цитата
написал:
Вы не поняли.
Да наверное( поэтому принялся за функцию table.buffer()

Цитата
написал:
если сотрудники были в одну дату, но в разное время.
иначе не бывает.
Цитата
написал:
Вы хоть смотрели код?
смотрел но по коду сразу не сориентировался( перенес все в файл и понял как вы сделали.
Результат получен, только вот если сделана ошибка и нет правила. То в певом случае будет ерор а в вашем будет считать на последнего известного.
поробую адаптироваться к таким реалиям или может найду возможность в dax посчитать так чтоб божно было получать ошибку в случае когда нет правила.
в любом случае большое спасибо что попробовали помочь.
 
, скажите еще может знаете, а это норм что при таких сложних для powerQuery расчетах загрузка процесора на 10-20% а оперативка до 40% +-?. Спасибо
 
Oleh, если одновременных доступов нет, то проще все. Отсутствие записи о доступе и наличии в это время событий можно учесть. Мы просто возьмём время окончания доступа и прилепим к нему сотрудник = null. Тогда при заполнении вниз у всех записей набито время, когда ни у кого не было доступа, будет равно null. Ещё правда придётся добавить группировку и уже внутри неё делать заполнение вниз (чтобы с другого события сотрудник не залез при отсутствии данных в rules для начальных событий).
Но позднее, пока нет времени.
 
Oleh, во вложении. Если на какие-то датывремя события нет сотрудника, то будет пустое значение. Каждому концу доступа присваиваем id сотрудника 0. Потом этот 0 протягивается вниз на события, если после него нет нового доступа. В конце 0 заменяем на null.
 
Спасибо, осваиваю. немножко сложнее понять но разберусь. Кроме того расчитал паралельно функцию и получил разные значения. Пробую оптимизировать правила и придумать как избегать ошибок в будущем и отпишусь.
 
Иногда у загружаемых файлов настроен "Общий доступ к файлам". Нужно его отменить через вкладку "Рецензирование" и тогда загрузка будет быстрее.
Не знаю почему, но Power Quary с такими файлами работает очень медленно.
Страницы: 1 2 След.
Наверх