Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оптимизация запроса PQ. Новый столбец и удаление старого, или построчная замена по условию?
 
#1
19.03.2020 18:13:12
Здравствуйте.
Каковы общие рекомендации по оптимизации кода PQ, что в общих случаях будет быстрее
какой код более оптимизирован, из имеющихся, тот который создает столбец, или тот который делает построчную замену?

Что на Ваш взгляд будет быстрее из следующих вариантов:

этот, Вариант с созданием колонки и записью в нее данных по условию,
Код
= Table.RenameColumns(
   Table.RemoveColumns(
      Table.AddColumn(
         Table.AddColumn(ISTO4NIK, "NewColumn", each "NewValue"),
      "TEMPColumn", each (if [NewColumn]<>null then [NewColumn] else [OldColumn])
      )
   {"OldColumn","NewColumn"}
   ),
{{"TEMPColumn", "OldColumn"}})

или, Вариант с построчной заменой по условию
Код
= Table.RemoveColumns(
   Table.ReplaceValue(
         Table.AddColumn(ISTO4NIK, "NewColumn", each "NewValue"),
   each [OldColumn], each (if [NewColumn]<>null then [NewColumn] else [OldColumn]), 
   Replacer.ReplaceValue,   {"OldColumn"}),
{"NewColumn"}
)
Изменено: lostandleft - 20.03.2020 16:54:57
 
 
 
#2
19.03.2020 18:45:15
Доброе время суток.

Исходя из базового
Цитата
lostandleft написал: Table.AddColumn(ISTO4NIK, "NewColumn", each "NewValue")
Вот это выражение
Цитата
lostandleft написал: (if [NewColumn]<>null then [NewColumn] else [OldColumn])
Всегда "NewValue", следовательно, достаточно
Код
Table.TransformColumns(ISTO4NIK, {"OldColumn", each "NewValue"})
 
 
 
#3
19.03.2020 18:47:58
Андрей VG, опередили :)  
НА МЯСО В ПРОФИЛЕ
 
 
 
#4
19.03.2020 19:29:25
Андрей VG, Спасибо за участие!
Но все-таки, зачем же Вы сразу издеваетесь то над еще малоопытным пользователем?  :sceptic:
Понятно что значение в столбце одно, и поэтому его можно сразу трансформировать в другой столбец.
А, если,значение в новом столбце, все-таки будут с нулевыми строками? или с каким-то другим значением, для выборки?
Что будет быстрее тогда?
Изменено: lostandleft - 19.03.2020 19:30:37
 
 
 
#5
19.03.2020 23:14:06
lostandleft, а в чем проблема сгенерить таблицу на 1кк строк и протестировать оба ваших варианта с секундомером наперевес? Мы тут время от времени такими плюшками балуемся.
Изменено: PooHkrd - 19.03.2020 23:15:33
Вот горшок пустой, он предмет простой...
 
 
 
#6
20.03.2020 02:19:26
Цитата
lostandleft написал:
зачем же Вы сразу издеваетесь
То есть выложить в качестве примера хрень - это нормально? Я же
Цитата
lostandleft написал:
еще малоопытным пользователем?
Подобное в детском саду - хорошее оправдание :)  Но, добро пожаловать во взрослую жизнь.
Цитата
lostandleft написал:
А, если,значение в новом столбце, все-таки будут с нулевыми строками?
Что значит с нулевыми строками? Будьте столь любезны пример приложить? Или вас нужно уговаривать? А в рамках вашего примера, максимум что можно выжать, так это заменить значения null с столбце OldColumn - так для этого вполне и Table.TransformColumn хватит.
P. S. И добрый совет - не увлекайтесь вложенными функциями - теряете читабельность кода. Любой текст удобнее читать слева на право и с верху вниз :)
 
 
 
#7
20.03.2020 09:30:30
Скачал вот этот пример, Самый быстрый ВПР

Довел количество строк до 1 млн, скопировав последнюю строчку в конец листа.

Написал 2 варианта кода:

Вариант 1:
Код
let
    Источник = Table.NestedJoin(Таблица1, {"Груз"}, Таблица2, {"Груз"}, "Таблица2", JoinKind.LeftOuter),

    #"Развернутый элемент Таблица2" = 


Table.RenameColumns(
   Table.RemoveColumns(
      Table.AddColumn(
         Table.AddColumn(
               Table.ExpandTableColumn(Источник, "Таблица2", {"Цена"}, {"Цена"}),
         "NewColumn", each "NewValue"),
      "TEMPColumn", each (if [NewColumn]<>null then [NewColumn] else [Цена])
      ),
   {"NewColumn", "Цена"}
   ),
{{"TEMPColumn", "Цена"}})


in
    #"Развернутый элемент Таблица2"
Вариант 2:
Код
let
    Источник = Table.NestedJoin(Таблица1, {"Груз"}, Таблица2, {"Груз"}, "Таблица2", JoinKind.LeftOuter),
    
#"Развернутый элемент Таблица2" = 
Table.RemoveColumns(
   Table.ReplaceValue(
             Table.AddColumn(Table.ExpandTableColumn(Источник, "Таблица2", {"Цена"}, {"Цена"}),
        "NewColumn", each "NewValue"),
   each [Цена], each (if [NewColumn]<>null then [NewColumn] else [Цена]),
   Replacer.ReplaceValue,   {"Цена"}),
{"NewColumn"}
)


in
    #"Развернутый элемент Таблица2"

Как Оказалось вариант 1, работает почти в 2 раза быстрее, т.е. следует избегать repalce, и все делать через дополнительный столбец...жаль я не проверил этого ранее :(
 
 
 
#8
20.03.2020 09:33:33
Цитата
Андрей VG написал:
P. S. И добрый совет - не увлекайтесь вложенными функциями - теряете читабельность кода.
Категорически стараюсь увлечься, и напротив, научиться писать все в одной переменной, стараюсь делать комментарии, пока не очень получается, но вдохновило на это статья:
Оптимизация производительности PQ пока что получается криво, но я стараюсь. Просто по 30-40 минут ожидать исполнения запроса - глупо. Кроме того, если переменных будет десятка 3, то читать код еще труднее. По мне, лучше 5 пременных с вложенным десятком функций и комментариями рядом, чем 50 переменных ссылающихся друг на друга.
Изменено: lostandleft - 20.03.2020 09:34:54
 
 
 
#9
20.03.2020 09:40:29
Андрей VG, Можете помочь написать функцию, которая будет добавлять столбец и выполнять проверку по условию?
В Функции в качестве вводных указывать
1 - Столбец со значениями в которых нужно делать изменения
2 - Столбец со значениями из которого нужно что-то выбрать.
3 - Условие для сравнения, null, Какое-тоЗначение, или какой-нибудь результат от других вычислений.

Код
(if [NewColumn]<>"КакоеТоЗначение" then [NewColumn] else [КолонкаДляПреобразованийИсходная])
Изменено: lostandleft - 20.03.2020 09:42:30
 
 
 
#10
20.03.2020 09:46:53
Цитата
lostandleft написал:
Оптимизация производительности PQ  пока что
По ссылке есть фраза:
Код
Вышеуказанный запрос выполняется менее чем за секунду
Кто нибудь разглядел этот запрос?
НА МЯСО В ПРОФИЛЕ
 
 
 
#11
20.03.2020 09:49:03
Цитата
lostandleft написал:
вариант 1, работает почти в 2 раза быстрее
В секундах сколько примерно у варианта 1?
НА МЯСО В ПРОФИЛЕ
 
 
 
#12
20.03.2020 09:50:55
Михаил Л, Сомневаюсь, но верю в истинность, при объявлении каждой новой переменной, по ощущениям PQ выполняет весь запрос заново. Это особенно хорошо видно на огромных файлах и на запросах в сторонние книги.
На каждой новой переменной PQ заново все просчитывает, поэтому число переменных утяжеляет код и увеличивает время в геометрической прогрессии.

Это мои ощущения, но возможно я что-то чувствую иначе.
 
 
 
#13
20.03.2020 09:52:08
Михаил Л, 13 секунд против 26 на моем компьютере, попробуйте на своем. Создайте 2 запроса в одной книге. Оба запроса я вывел из фонового режима и включил быструю загрузку
 
 
 
#14
20.03.2020 09:56:57
Цитата
Михаил Л написал:
По ссылке есть фраза
Если что, по ссылке переводная статья скопипасченная с сайта RADACAD и даже без ссылки на первоисточник. Фу, таким быть.
Цитата
lostandleft написал:
Это мои ощущения
Это именно только они. Это утверждение имеет зерно истины, но не в таких масштабах.
Изменено: PooHkrd - 20.03.2020 09:59:25
Вот горшок пустой, он предмет простой...
 
 
 
#15
20.03.2020 10:01:25
PooHkrd, для меня важнее суть, и благодарность за перевод, нежели авторское право, я ведь о производительности вопрос задал. Хоттелось бы оптимизировать свои запросы. Уж очень они долгие.
 
 
 
#16
20.03.2020 10:04:30
Цитата
lostandleft написал:
Уж очень они долгие
Поверьте, количество переменных в запросе новичка, это последнее что влияет на производительность.  ;)  Чтобы помочь не на пальцах, это нужно сидеть рядом с вами и по косточкам разбирать ваш код и ваши источники.
Вот горшок пустой, он предмет простой...
 
 
 
#17
20.03.2020 10:15:26
Цитата
PooHkrd написал:
это нужно сидеть рядом с вами и по косточкам разбирать ваш код и ваши источники
Эх, я понимаю. Но вариант с колонкой на замену REPLACER реально увеличивает производстельность в1,8-2 раза!
Наверняка еще масса таких хитростей есть.
Я этого не знал, старался везде реплейсер ставить, просто потому что код меньше. Получается это неправильно. Кто-бы с функцией подмогнул :-)
 
 
 
#18
20.03.2020 10:19:11
Цитата
lostandleft написал:
Можете помочь написать функцию, которая будет добавлять столбец и выполнять проверку по условию?
Не вопрос, если
Цитата
2.3. Приложите файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.
 
 
 
#19
20.03.2020 10:54:50
Цитата
lostandleft написал:
благодарность за перевод
спасибо, Goggle Translate!  :D
Цитата
lostandleft написал:
при объявлении каждой новой переменной, по ощущениям PQ выполняет весь запрос заново.
это только в редакторе при разработке - ему нужно сгенерировать для вас предпросмотр состояния запроса на конкретный шаг и закэшировать его. При рабочем выполнении запроса ему плевать на предпросмотр, поэтому работает совсем не так.
Вообще любой сколь угодно сложный код PQ можно записать вообще в одну строку, и никакого влияния на производительность запроса это не даст.

Возвращаясь к вопросу оптимизации - первый вариант быстрее. Но прийти к пониманию этого можно аж двумя способами:

1) заранее зная, какой механизм заложен в ту или иную функцию PQ и что она делает под ковром, и как работает внутренний оптимизатор PQ
2) методом проб и ошибок, тестирования, изучения документации, задавания вопросов, чтения форумов и блогов

Обычно наш метод второй (если кто-то из разработчиков не поделился сокровенным знанием).
Те, кто пишет более-менее оптимальный код - миксуют оба метода.
Так что все нормально, вопрошайте, только следите за корректностью примеров, и вам весьма воздастся.

Дополнительная оптимизация вам - постарайтесь понять, что и зачем сделано:
Код
let
    table2renamed = Table.RenameColumns(Таблица2, {{"Груз", "Gruz"}}),
    table2tiny = Table.SelectColumns(table2renamed, {"Gruz","Цена"}),
    Источник = Table.Join(Таблица1, {"Груз"}, table2tiny, {"Gruz"}, JoinKind.LeftOuter),
    AddNewColumn = Table.AddColumn(Источник, "CorrectedPrice", each List.First(List.Select({какоетовыражение,[Цена]}, each _ <> null))),
    Removed = Table.RemoveColumns(AddNewColumn , {"Gruz", "Цена"}),
    final = Table.RenameColumns(Removed, {{"CorrectedPrice", "Цена"}}),
in
    final

а вот то же самое, записанное в одну строку:
Код
 = Table.RenameColumns(
     Table.RemoveColumns(
         Table.AddColumn(
             Table.Join(
                 Таблица1, {"Груз"}, 
                 Table.RenameColumns(Таблица2, {{"Груз", "Gruz"}})[[Gruz],[Цена]], {"Gruz"},
                 JoinKind.LeftOuter), 
            "CorrectedPrice", 
            each List.First(
                List.Select(
                    {какоетовыражение,[Цена]}, each _ <> null)
                    )
                ),
        {"Gruz", "Цена"}),
    {{"CorrectedPrice", "Цена"}}
    )

Как по мне, так первый код понятнее (хотя он на 100% соответствует второму).

дальнейшая оптимизация зависит больше от какоетовыражение, и это выражение может в корне поменять код.
F1 творит чудеса
 
 
 
#20
20.03.2020 11:54:56
Цитата
PooHkrd написал:
статья скопипасченная
Что-то у меня по времени не сходится. Ресурсы только жрет. И первый запрос , и второй достаточно быстро обновляются
Сработало, скорее всего, это:
Цитата
Максим Зеленский написал:
только в редакторе при разработке - ему нужно сгенерировать для вас предпросмотр состояния запроса на конкретный шаг и закэшировать его. При рабочем выполнении запроса ему плевать на предпросмотр
Изменено: Михаил Л - 20.03.2020 12:28:02
НА МЯСО В ПРОФИЛЕ
 
 
 
#21
20.03.2020 12:29:07
Цитата
Михаил Л написал:
Что-то у меня по времени не сходится.
Вот именно это я и имел в виду когда писал.
Цитата
PooHkrd написал:
Это утверждение имеет зерно истины, но не в таких масштабах.
То, что описано в статье больше имеет отношение именно к редактору запросов, но не к выполнению самого запроса.
Вот горшок пустой, он предмет простой...
 
 
 
#22
20.03.2020 13:42:29
Цитата
Михаил Л написал:
Что-то у меня по времени не сходится
Проверяйте, код я выписал, фаил скачать от куда тоже написал.
Не поленился сходил еще на одну машину с i5 на борту, запустил фаил с 1 млн строк, 16 секунд выполняет запрос 1 и 27 секунд выполняет запрос 2
 
 
 
#23
20.03.2020 13:54:43
Цитата
PooHkrd написал:
То, что описано в статье больше имеет отношение именно к редактору запросов
Господа, Вы сломали мои стереотипы.
Спасибо. Не буду излишне нагромождать переменные.

Однако, честно, Вариант написанный Максим Зеленский, вторым для меня немного понятнее, я еще не знаю как работает List, и мне сложно понять что происходит тут,
Код
each List.First(                List.Select(
но остальное вполне читаемо, особенно если влепить комментарии, по моему воспринимается проще, чем в голове держать несколько переменных. С другой стороны, если операторы не знаешь, как в моем случае с list, то конечно тыкалками натыкать и понять что происходит не получится.
Изменено: lostandleft - 20.03.2020 13:55:47
 
 
 
#24
20.03.2020 16:27:25
Цитата
lostandleft написал:
13 секунд против 26 на моем компьютере
Цитата
lostandleft написал:
Проверяйте, код я выписал
Я не ставил под сомнение.

Цитата
Михаил Л написал:
Что-то у меня по времени не сходится
Это относится к статье.  В статье речь про два запроса, один из которых, якобы, обновляется долго. В файле есть эти запросы(укороченные). Оба запроса обновляются, на мой взгляд, недолго.
НА МЯСО В ПРОФИЛЕ
 
 
 
#25
21.03.2020 15:10:20
lostandleft, ещё один голос не в пользу большого количества вложенных функций это поиск багов. Рано или поздно в каком-то из источников кто-то что- подменяет и запрос выдаст ошибку. Поверьте искать ошибку в запросе с малой вложенностью значительно проще чем наоборот.
Вот горшок пустой, он предмет простой...
 
 
 
#26
21.03.2020 18:57:00
Вот это:
Код
List.First(List.Select({какоетовыражение,[Цена]}, each _ <> null))
аналог функции COALESCE в DAX или SQL, возвращает первое из значений списка, не равных null
F1 творит чудеса
 
 
 
Страницы: 1
Наверх