Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
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 Апр 2019 21:45:19
 
JosephineUa, Вы видели, как выглядит код у других? Вот и Вы его так же форматируйте. Для этого даже специальная кнопочка есть (см скрин). Найдите её и исправьте своё сообщение.
Тег VBA.jpg (19.2 КБ)
 
Цитата
Юрий М написал:
JosephineUa , Вы видели, как выглядит код у других? Вот и Вы его так же форматируйте. Для этого даже специальная кнопочка есть (см скрин). Найдите её и исправьте своё сообщение.
Спасибо, исправила
 
JosephineUa,на вскидку код совсем легкий и тормозить не должен. могу сделать несколько предположений:
1 в шаге с группировкой в колонке 13 есть нет только цифры но и ошибки или текст  -  PQ это не любит
2 в вашем файле этот запрос не единственный и при изменении одного начинают обновляться все остальные
3 источник данные я не совсем понял - это текущая книга эксель, а дальше что? все листы книги? какая то пользовательская функция? может где то здесь томоза

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

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

 
 
Цитата
Андрей VG написал:
А если вы будете заниматься разработкой для промышленных систем, например, биллинга сотового оператора, то вы будете сразу на 100 миллионов/миллиарде другом строк код писать и отлаживать?
Для этого наверно больше подойдет Pyton/R, наверное пора начинать осваивать)
 
Чтобы написать макрос, нужно видеть хоть немного исходных данных и желаемый результат.
 
Думаю у вас проблема вот в этой строке
Код
#"Замененное значение" = Table.ReplaceValue(#"Строки с примененным фильтром","Манго-лимон","Манго-Лимон",Replacer.ReplaceText,{"Column 1"}),

Думаю  что это итерация по строкам и столбцам. Если их очень много в таблице, то и результат будет очень долгий
Изменено: DrillPipq - 1 Май 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 Апр 2019 00:01:48
 
JosephineUa, попробуйте мои советы отсюда.
А ещё после крайних обновлений могут подвисать процессы, которые запускает редактор. Проверяется так: закрываете редактор, смотрите в диспетчер задач, если ресурсы процессора кушаются, значит процесс завис. Лечится закрытием/открытием книги.
Изменено: PooHkrd - 27 Апр 2019 01:47:25
Вот горшок пустой, он предмет простой...
Страницы: 1
Читают тему (гостей: 1)
Наверх