Попалась мне задачка оптимизации, а так как я большой фанат Экселя, то и выбор инструмента был скорым. Единственная пакость: Эксель дико медленный. Так, на одну итерацию уходило как минимум 35 минут, а таких итераций планировалось сделать 1275 (как минимум)!
Цель этого небольшого проектика – ускорить исполнение VBA скриптов задействуя все доступные мне железяки: GPU и CPU. Ну и до кучи, так как библиотека моя, была реализована многозадачность.
Для тех, кто любит читать только код и не любит "растекания мыслию по древу", код находится здесь, инсталлятор здесь, архивированная библиотека и примеры (Excel/VBscript) здесь. Примеры также включены в инсталляцию (папка "demo").
Предуведомление
Требования:
Excel (можно и без него, см. про ненормальное программирование в самом конце).
.Net framework v4.0.
Windows/System32/ должен содержать opencl.dll.
Структура
Внутренности библиотеки состоят из двух непересекающихся частей: конфигурации доступного оборудования (только информация, поменять там ничего нельзя) и, собственно, программирования найденных устройств.
Конфигурация
Конфигурация просто вываливает в табличку всю доступную информацию об устройствах, "до самой последней гнилушки". Логически OpenCl делит компьютер на платформы, внутри которых находятся устройства. Так, на моём домашнем компьютере обнаружилась только одна платформа с CPU на 4 процесса (Pentium 4417U 2.30GHz) и 12-ядерным GPU (Intel HD Graphics 610). А вот на компьютере жены обнаружилось целых 2 платформы: первая, с i3-7100U и 23-ядерной Intel HD Graphics 620, и вторая, опять же с i3-7100U и 5-ядерным GPU Hainan. Что интересно, интернеты говорят, что Intel HD Graphics 620 идёт с 24 ядрами. Либо OpenCl недосчитался, либо ядро отвалилось.
Программирование было реализовано на двух примерах: оценке производительности и параллельного исполнения кода.
Производительность
Производительность оценивалась двумя способами:
Умножением больших матриц.
Кодом, заимствованным с CodeProject ("How to Use Your GPU in .NET").
Время, затраченное на умножение двух матриц 2000 на 2000 (вычисления с двойной точностью) приведено на рисунке ниже. Обратите внимание на логарифмическую шкалу!
Обозначения CPU и GPU соответствуют использованию OpenCl на CPU и GPU. Native CPU – случаю использования простейшей программы на C# для перемножения матриц. Ну а VBA он и в Африке VBA. Кроме измерения времени результаты OpenCl вычислений сравнивались с результатами VBA (все сошлись!).
Довольно неожиданным открытием для меня стало то, что OpenCl на всех CPU считает в 8 раз быстрее, чем C# на одном процессоре. Так как у меня 2 процессора с 4 потоками, можно было бы предположить четырёхкратное уменьшение времени исполнения. Видимо здесь играет существенную роль то, что драйвер процессора, который компилирует CL-код, компилирует сразу в машинный код, а не использует интерпретатор, как C#.
Ну и результаты теста производительности полностью:
Время вычисления VBA: |
3.1 |
минуты. |
OpenCl на CPU в |
73 |
раза быстрее VBA. |
OpenCl на GPU в |
327 |
раз быстрее VBA. |
OpenCl на GPU в |
4.5 |
раза быстрее CPU. |
OpenCl на CPU в |
8.3 |
раза быстрее C#, CPU. |
C#, CPU в |
8.9 |
раз быстрее VBA. |
Следующим тестом производительности был тест, позаимствованный с CodeProject. Для моего домашнего Pentium 4417U (2 x 2.30 ГГц) выдал мне такие результаты производительности в Гфлопсах:
GFlops, single |
GFlops, double |
|
CPU |
18.1 |
9.1 |
GPU |
358.5 |
89.7 |
Асинхронность
Как, наверное, всем известно, Эксель до боли однозадачен. Чтобы запустить две задачи одновременно, надо приложить довольно много усилий. Всё описанное выше запускалось командой ExecuteSync. Тем не менее, кроме функции ExecuteSync имеются также и функции ExecuteAsync и ExecuteBackground. Отличие первой функции от второй в том, что ExecuteAsync использует callback, в то время как вторая возвращает информацию о завершении исполнения в свойстве ExecutionCompleted (True/False), которое надо время от времени опрашивать.
В принципе, первой была создана функция ExecuteAsync, но по мере тестирования было замечено, что с callback работать довольно сложно:
Эксель не любит и рушится:
Когда при отладке исполняется callback.
Когда из callback функции что-либо пишется в таблицу.
Поэтому, в примере я использую ExecuteBackground. Результат работы в асинхронном режиме просто ошеломляет! Прогресс-бар бегает, процессы исполняются параллельно на CPU и GPU, поочерёдно завершаются, причём на один завершённый процесс CPU приходится 4 процесса GPU, как и предсказывали расчёты производительности умножения матриц.
Ну и обещанное ненормальное программирование (а всё написанное до того было нормальным программированием?).
Как оказалось, Эксель особо-то и не нужен! VBscript тоже поддерживает COM, правда со своими ограничениями: отсутствие типизации приводит к тому, что массивы необходимо передавать через object или ArrayList. Тем не менее, конфигурация работает как есть даже без дополнительных телодвижений:
И, наконец, всё в одном месте:
Примеры в папке "demo".
Комментарии (24)
Melonom
11.03.2022 08:17+3Я в экселе профан конечно, но эту штука на скрипты работает или на теже формулы тоже?
Krasnoarmeec Автор
11.03.2022 18:02Не проверял, но должна работать.
Пользовательские формулы ведь тоже задаются через скипты.
Проще всего запихнуть все инициализации в "Workbook_Open" и там загружать весь Cl-код и компилировать.
LevOrdabesov
11.03.2022 14:08+1Короткая, конечно, статья, но однозначно плюс. Это не перевод же?
Речь о любой версии Excel?
Инсталлятор работает по принципу «раз запустил и оно подключено»?Krasnoarmeec Автор
11.03.2022 18:16+1Нет, это не перевод, автор я. А статья получилась такой короткой потому что писать особо и не о чем. Типа подключил Эксель через библиотеку (ClooWrepperVBA) к OpenCl-библиотеке через другую библиотеку (Cloo). Ура, заработало! Теперь мой VBA на CPU в 8 раз быстрее ваших программ на C#! Вот вам и пересказ статьи в трёх предложениях :-)
В принципе должно работать на любой версии Экселя. У меня 2007. Тестировал также и на Экселе x64 (2019). Конфигурация распечаталась. Дальше не тестировал.
Да, инсталлятор для того и был думан, чтобы запустить и всё работало. Приложенный архив содержит всё тоже самое, просто для регистрации библиотеки надо запустить приложенный bat-файл.
maikuss
11.03.2022 14:46Если такие результаты достигнуты, это надо продавать за деньги, а не раздавать всем подряд даром.
Krasnoarmeec Автор
11.03.2022 18:33+5А оно мне надо?
Доходов практически не принесёт (нишевый продукт, нужен очень немногим), а вот расходов будет много: сайт (это ведь не Андроид, под Windows нет ГуглПлея), всякое СЕО, ответы на письма...
К тому же тут уже вопрос морали: Cloo - библиотека открытая, а я всего лишь сделал доступными её основные функции.
На самом деле, я свои доходы уже получил:
Сделал нужную мне вещь.
Рассказал о ней сообществу.
Сам узнал немного нового (OpenCl, как работать с двумя аккаунтами GitHub).
Ну и развлёк господ технарей в пятницу.
KislyFan
11.03.2022 21:21+1Это не просто нишевая штука, а какая-то очень нишевая) Тоесть реально пригодится, когда переписывание существующего VBA не рентабельно.. ну и не стоит забывать, что в случае чего стоимость поддержки тоже увеличивается.
maikuss
12.03.2022 20:41+1Мне бы на моей прошлой работе это решение очень бы пригодилось. У меня там столько вcего было на Excel VBA. Медленный пересчет многое усложнял. В общем, мы тогда бы купили с удовольствием :) Но ваш подход к вопросу, как говорится, highly appreciated
Marlene14
12.03.2022 09:39+1По такому пути некоторое время шёл Майкрософт, так делать не надо, автору статьи респект
Intelligent
11.03.2022 23:50+1Access наверняка тоже ускорится?
Krasnoarmeec Автор
12.03.2022 00:49В принципе да. Используется COM-интерфейс и ему всё равно из чего его вызывают. Вон, в конце заметки даже из VBscript удалось вызвать.
В моей версии офиса Access не поставлялся, так что проверить не смогу.
Henderson
12.03.2022 16:16+1Вот бы мне все это реализовать, с чего начать, коллеги))
Krasnoarmeec Автор
12.03.2022 16:23В смысле "все это реализовать"?
Берите и пробуйте. Код вроде как рабочий (пока никто не жаловался что что-то не работает).
Коротенький туториал по OpenCl лежит здесь.
Если будут вопросы, обращайтесь. Можно в личку. Ответить в тот же день не обещаю, но постараюсь.
navferty
13.03.2022 15:19+1Добавлю свои 5 копеек. Иногда тормоза в макросах могут быть не из-за того что тяжёлые вычисления очень долго выполняются, а из-за того что в ходе этих вычислений Вы многократно читаете или записываете данные в ячейки на листе. В ходе таких вызовов происходит дорогостоящая операция обращения к объектной модели книги или листа. Вместо этого можно однократно прочитать весь диапазон значений в двумерный массив, также можно и записать из такого массива значения обратно на лист после всех преобразований. Тогда вычисления будут происходить только в оперативной памяти самого макроса. Под спойлером пример такой оптимизации
Пример оптимизации чтения с листа в цикле
Krasnoarmeec Автор
13.03.2022 15:51Таки да!
Более того, само чтение из ячеек занимает больше времени, чем чтение из файла!
Ваш способ, через UsedRange, не пробовал (знаю, что Range можно сразу копировать в массив, но пользуюсь этим редко), но чтение из csv файла с последующим парсингом в 4(!) раза (проверял лично) быстрее чтения из ячеек.
Пример:
Пример:
Open strFilename For Input As #iFile strFileContent = Input(LOF(iFile), iFile) Close #iFile Dim tmpStrings$() tmpStrings = Split(strFileContent, vbCrLf) For i = 1 To nAssets tmpDoubles = Split(tmpStrings(i), ";") Next i
13werwolf13
так
стоп
то есть эксель из коробки не ускоряется gpu? тогда на кой чёрт он иногда сильно загружает видюху даже при работе с простенькими табличками? или некрософт туды майнер вшила?
MAXXL
Не знаю чем он загружает видюху, но в Офис365 у Экселя нужно отключать аппаратное ускорение, иначе начинаются жуткие тормоза.
Muzzy0
Аппаратное торможение ????
Sdvnkhp
При работе с VBA в Excel отключайте обновление экрана.
Krasnoarmeec Автор
Как уже ответил @MAXXL есть опция отключения аппаратного ускорения. И для ускорения Экселя советуют её отключать. В общем, у меня она всегда отключена.
Чтобы дополнительно ускорить VBA лучше ещё отключить (дополнительно к совету @Sdvnkhp) пересчёт формул и отключить ивенты:
13werwolf13
Да мне собственно мало интересно, у меня Либра хотя бы потому что мс офиса нет на линь, а начальнику у которого была проблема я поставил freeoffice и Wps на выбор, не помню что он выбрал но назад на мс не хотел после этого.
Delion
Чего-то я не понял.
Почему включённое ускорение замедляет, а выключенное ускоряет?
Krasnoarmeec Автор
Это Майкрософт. Мы тоже не понимаем.