Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Курилка
Страницы: 1
RSS
Влияние комплектующих ПК на быстродействие Excel, VBA, Power Query, Power Pivot
 
#1
25.06.2024 09:51:34
Добрый день!

Т.к. приходится работать с большим количеством данных в Excel, используя VBA, Power Query, Power Pivot, неоднократно сталкиваюсь с ситуацией когда что то очень долго происходит. Понимаю что все можно оптимизировать и ускорить в разы, можно научиться использовать более подходящие для обработки и анализа больших объемов данных инструменты чем Excel, но также рассматриваю вариант ускорения работы посредством замены 12 летнего компьютера со встроенной видеокартой на современный компьютер. Сейчас стоит Core i7 3770, 16 гб ОЗУ, и 2 SSD диска  

Так как бюджет хочется использовать по максимуму рационально, исключительно на скорость в Excel, используя VBA, Power Query, Power Pivot и т.д., то стал изучать на чем можно сэкономить а  что лучше взять по максимуму. И вначале мысль была такая, в первую очередь нужен, понятно, быстрый процессор (побольше ядер, частоты и кэша), потом ОЗУ (объем и частота), потом SSD диски (скорость и объем). Видеокарта думал пойдет встроенная, так как ни игрушки играть ни фильмы смотреть не планируется

Натолкнулся на такую статью https://answers.microsoft.com/ru-ru/msoffice/forum/all/%D0%BB%D1%83%D1%88%D0%B8%D0%B9/ffc3f60­8-fba7-... и завис. И не знаю доверять ли ей, вроде источник солидный. Кто понимает в железе, в потребностях Excel, поделитесь пож своим мнением
 
 
 
#2
25.06.2024 10:40:32
Ливиан, добрый день. Выскажу лично свои наблюдения по поводу железа. Есть 2 компьютера:
1. рабочий: i5-11600(2.8 ghz), видеокарта - встройка, 16Gb оперативы, ссд обычный - чтение 750 мб/с
2. домашний ноут: i5 - 11400h (2.69 ghz), видеокарта - rtx 3050 Ti, 16Gb оперативы, ссд NVMe, чтение 1600 мб/с

Система и набор программ одинаковый, в т.ч. версии (win10, офис 365).

Так вот прогон следующего макроса имеет следующие результаты ( лучшее время, что видел)
Код
Sub tst()
    
    Dim s As Double
    s = Timer
    
    Const t As Long = 1000000
    Const x As Long = 100
    
    Dim arr(1 To t, 1 To x) As LongLong
    Dim i As Long, j As Long
    For i = 1 To t
        For j = 1 To x
            arr(i, j) = i * j
        Next j
    Next i
    
    Debug.Print Timer - s
    
End Sub
1. рабочий комп = 0.91992
2. домашний ноут = 0,95321

Поэтому склонен к тому, что рейтинг важности компонентов в статье не совсем верно указан. По тестам процессор важнее, по крайней мере для обработки массивов данных.
Изменено: Alex - 25.06.2024 10:44:54
 
 
 
#3
25.06.2024 11:38:32
Alex, спасибо за Ваше мнение!
Я тоже думаю, что не может видеокарта быть важнее чем процессор для Excel и встройки современных процессоров должно быть более чем достаточно. Такие же сомнения по поводу большего значения накопителя над процессором, диск имеет значение  для Power Query и большое, скорее всего быстрее запросы грузиться будут особенно если данных очень много, так что диск имеет большое значение, но не больше чем процессор, считать то эти данные ему. То есть процессор важнее всего, скорее всего.
Также мучают сомнения по поводу количества ядер, чем больше тем лучше. Конечно кашу маслом не испортишь, но будет ли пропорциональная разница между количеством ядер и скоростью Excel? По моим наблюдениям почти всегда Excel грузит только 15% процессора, т.е. одно ядро (или один поток) из 4 ядер (8 потоков) моего процессора. Это почти при любых операциях, запрос обновляется или макрос запущен, редко когда скачек до 25%.
Брать 16 ядерный процессор или скажем 6 ядерный - будет ли разница сколько-нибудь существенная или просто будет больше ядер простаивать?
 
 
 
#4
25.06.2024 11:59:52
Ливиан,
Цитата
написал:
Брать 16 ядерный процессор или скажем 6 ядерный - будет ли разница сколько-нибудь существенная или просто будет больше ядер простаивать?
По этому поводу не могу ничего прокомментировать.
Еще из личных наблюдений работы Эксель, обычно, когда работает макрос просто на привидения в какой-то вид данных, то тоже в пределах 15%, однако  бывает, что загрузка процессора  у меня может быть и до 100% за счет Экселя - это бывает, когда идет пересчет формул листа после работы макроса либо пересчет в сводной таблице после обновления данных в модели данных Power Pivot, как раз когда проходят вычисления.
Изменено: Alex - 25.06.2024 12:01:14
 
 
 
#5
25.06.2024 12:48:09
Цитата
Ливиан написал:
Также мучают сомнения по поводу количества ядер, чем больше тем лучше. Конечно кашу маслом не испортишь, но будет ли пропорциональная разница между количеством ядер и скоростью Excel?
нет, не лучше. Excel в большей степени работает как однопоточная программа, ну т.е. есть различные функции, которые умеют многопоточность использовать, но в большей степени нет. (хотя могу наврать, но я так понимаю работу программы) И в таком случае более важным будет производительность 1 процессора, и не их кол-ва. А так для Ваших потребностей важность комплектующих я бы отранжировал так:
1. Оперативка (самое важное для PQ и PP). 32 ГБ должно хватить на обработку десятков миллионов строк данных.
2. SSD
3. Процессор.
 
 
 
#6
25.06.2024 12:52:09
Я тоже работаю с большими объемами данных в Excel и Power BI (до 100 млн строк).

Вот этого мне хватает, + SSD.
 
 
 
#7
25.06.2024 12:57:08
у меня до 25% наверно из-за того, что формул не так уж много, в основном запросы, сводные и макросы, которые не могут в многопоточность.  Поэтому в моем случае наверное правильнее будет взять 6 ядерный.
Alex, спасибо Вам за помощь и Ваше время. Было очень полезно для меня
 
 
 
#8
25.06.2024 13:08:09
Vladimir Ch, а какая видеокарта у Вас? И как долго обновляются Ваши запросы, просто у меня, к примеру, есть запрос к папке с 400  файлами .xlsx, в которых примерно 1 млн строк (суммарно во всех файлах) и 60 столбцов, файл с запросом и с примерно 10 сводными таблицами обновляется 3 часа...  
 
 
 
#9
25.06.2024 13:12:25
Цитата
Ливиан написал:
а какая видеокарта у Вас?
intel® hd graphics 630 - вроде ниочем.
 
 
 
#10
25.06.2024 13:19:35
Цитата
Ливиан написал:
обновляется 3 часа
не должно так быть. А где файлы лежат, на сервере или на локальном диске? Если на серваке, то нужно копать в сторону скорости потока данных по сети. Думаю, такой объем данных (400 файлов суммарно на миллион строк) обновились бы за 10-20 минут. Если только в самом запросе нет "выкрутасов" с ДЖОЙНАМИ, сворачиваниями и прочего, что может съесть оперативку.
П.С. интереса ради наклепал 400 одинаковых файлов на 60 столбцов и 2500 строк в каждом. (30 текстовых и 30 числовых столбцов). Простая загрузка данных этих 400 файлов без каких-либо преобразований заняло всего-лишь 2 минуты.
Изменено: Vladimir Ch - 25.06.2024 13:35:58
 
 
 
#11
25.06.2024 13:49:38
Версии Microsoft Excel, предшествующие Excel 2007, используют один поток для всех вычислений на листе. Однако начиная с Excel 2007, excel можно настроить для использования от 1 до 1024 параллельных потоков для вычисления листа. На многопроцессорном или многоядерном компьютере число потоков по умолчанию равно количеству процессоров или ядер. Таким образом, потокобезопасные ячейки или ячейки, содержащие только функции, которые являются потокобезопасными, могут быть выделены в параллельные потоки с учетом обычной логики пересчета, необходимой для вычисления после их прецедентов. (https://learn.microsoft.com/ru-ru/office/client-developer/excel/multithreading-and-memory-contention...)
https://learn.microsoft.com/ru-ru/office/client-developer/excel/multithreaded-recalculation-in-excel
 
 
 
#12
25.06.2024 14:25:45
Vladimir Ch, спасибо что проделали такую работу.
Давно подозреваю что что то не так, но твердой уверенности не было, т.к. не знаю какие файлы у других людей,  как долго они обновляются, и что считать нормой. Переделал запрос, из запроса к папке с файлами в запрос к файлу .csv, куда те же данные  из 400 файлов загнал (думал будет быстрее). Два часа уже обновляется, видимо также 3 часа будет обновляться...
Попробую отключать/включать в настройках запроса разные опции, может поможет что, потому что в файле помимо модели данных (+десяток столбцов с простыми арифметическими формулами), 10 сводных и нескольких простых арифметических формул (без тяжелых двссыл и т.д.) на Листах и нет ничего. Буду удалять по очереди всё из книги, лист за листом, пока голый запрос не останется. Или с нуля буду файл воспроизводить, и смотреть где у меня возникает задержка
Спасибо огромное, теперь понимаю примерно сколько мои данные должны грузиться и обрабатываться, а то сидел и думал, а может 3 часа для моих данных это нормально и у всех так...
 
 
 
#13
25.06.2024 14:31:58
Кстати, запрос к файлу .csv обработался за 2 часа и 5 минут. Тот же запрос к папке с файлами .xlsx за 3 часа. Т.е. всегда лучше данные собирать в один .csv?
Изменено: Ливиан - 25.06.2024 14:34:36
 
 
 
#14
25.06.2024 15:10:35
Цитата
Alex написал:
Таким образом, потокобезопасные ячейки или ячейки, содержащие только функции, которые являются потокобезопасными, могут быть выделены в параллельные потоки с учетом обычной логики пересчета, необходимой для вычисления после их прецедентов
так там самое интересное, что в многопоточность могут только:
- Все унарные и двоичные операторы в Excel.
- Почти все встроенные функции листа, начиная с Excel 2007
- Функции надстроек XLL, которые явным образом зарегистрированы как потокобезопасные.
Как я понимаю, ни VBA, ни PQ, ни PP сюда не попадают. А значит используют при работе только 1 процессор. Поправьте меня, если я не правильно понял статью.
 
 
 
#15
25.06.2024 15:30:42
Vladimir Ch, я так же понял статью. Скинул ее сюда для понимания в целом. Но мне почему-то кажется, что сам DAX и пересчет в сводной таблице многопоточен.
 
 
 
Страницы: 1
Наверх