Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 30.09.2017, 20:42   #1
Shuma1974
 
Регистрация: 01.05.2016
Сообщений: 6
По умолчанию Оптимизация кода на скорость исполнения (обращения к массивам данных)

Здравствуйте, уважаемые форумчане,
прошу совета в таком вопросе:
Есть база данных в виде многомерного массива: Public MY_ARRAY(N,2,32,32) as Integer. При запуске программы он заполняется с ячеек листа, и потом программа обращается к нему. И вот столкнулся с тем, что при увеличении числа N производительность программы падает. Обращения к этому массиву, конечно, частые, но их частота не зависит от объема массива. При этом обращения идут сразу к конкретным элементам массива, никакого поиска и перебора элементов в нем не производится (т.е. сама процедура обращения к массиву не должна по скорости зависеть от размера массива). Но по факту зависит (причем прилично, съедая до 50% скорости работы программы).
Я подозреваю, что дело может быть в переполнении кеша, в который этот массив загружается целиком каждый раз, когда я к нему обращаюсь. Но вобщем хотелось бы услышать мнение специалистов. Заранее спасибо.
Shuma1974 вне форума Ответить с цитированием
Старый 30.09.2017, 20:47   #2
Watcher_1
Форумчанин
 
Аватар для Watcher_1
 
Регистрация: 22.06.2011
Сообщений: 325
По умолчанию

1) какой кол-во элементов в массиве?
2) хотелось бы посмотреть на саму реализацию, так тяжело что0то сказать
Заказать макрос можно на сайте http://excel4you.ru/
Watcher_1 вне форума Ответить с цитированием
Старый 30.09.2017, 22:00   #3
Shuma1974
 
Регистрация: 01.05.2016
Сообщений: 6
По умолчанию

1) какой кол-во элементов в массиве?

- всего 70 (пробовал уменьшить до 20 - в целом скорость исполнения программы выросла процентов на 30)

2) хотелось бы посмотреть на саму реализацию, так тяжело что-то сказать

там в программе очень много строк, поэтому я приведу пару коротких выдержек, которые касаются этого массива:

Код:
Public MY_ARRAY() as Integer
Public i1 as Integer, i2 as Integer, i3 as Integer, i4 as Integer, i5 as Integer, i6 as Integer
Public Tmp_Var as Integer, Active_Array_Frame as Integer
...
Sub 'инициализируем и загружаем
 ReDim MY_ARRAY(Number, 2, 16, 32) ' 16 и 32 - это переменные, тут я их просто как константы представил
i4 = 0
i5 = 0
 For i1 = 1 To Number
  For i2 = 1 To 32
   For i3 = 1 To 16
     Tmp_Var = Worksheets(Array_Page1).Cells(i4 * 32 + i3, i5 * 16 + i2).Value
     If Tmp_Var <> 0 Then MY_ARRAY(i1, 1, i2, i3) = Tmp_Var, MY_ARRAY(i1, 2, i2, i3) = Worksheets(Array_Page2).Cells(i4 * 32 + i3, i5 * 16 + i2).Value
   Next i3
  Next i2
  i5 = i5 + 1
  If i5 * 16 + 1 > 256 Then i5 = 0: i4 = i4 + 1
Next i1
end sub
...
sub2
' Active_Array_Frame - индексная переменная, значение которой получается из чтения другого массива, размер которого не зависит от размера MY_ARRAY 
 For i1 = 1 to 16 
  for i2 = 1 to 32
   if MY_ARRAY(Active_Array_Frame, 2, i1, i2) > 0 then Tmp_Var = RESULT_ARRAY(MY_ARRAY(Active_Array_Frame, 1, i1, i2),MY_ARRAY(Active_Array_Frame, 2, i1, i2))' RESULT_ARRAY - 'это 2-мерная матрица, размер которой фиксирован по диапазону допустимых значений двух ветвей второго порядка массива MY_ARRAY (1-7 и 1-11 соответственно) и тоже не зависит от размера MY_ARRAY
  next i2 
 next i1
end sub2
Пожалуйста, оформляйте Ваш код согласно правилам.

Последний раз редактировалось Вадим Мошев; 17.10.2018 в 16:29.
Shuma1974 вне форума Ответить с цитированием
Старый 17.10.2018, 15:59   #4
Shuma74
Новичок
Джуниор
 
Регистрация: 17.10.2018
Сообщений: 2
По умолчанию

С огромным опозданием, но все же отпишусь: проблема была решена. Оказалось следующее: информацию для заполнения массива я брал с листа Excel. Информацию на лист добавлял блоками одинакового размера, каждый из которых комментировал сразу в паре десятков ячеек (соответственно данным в ячейках). Как только убрал эти комментарии, сразу скорость исполнения вернулась к норме и уже практически не зависела от количества этих блоков и, соответственно, элементов массива. Думаю это может быть актуально для тех, кто любит пользоваться комментированием ячеек и при этом хочет быстрого исполнения макросов.
ПС. Кстати, вот что вышло из той программы: https://youtu.be/lI8BnWj85Us
Shuma74 вне форума Ответить с цитированием
Старый 24.08.2019, 23:42   #5
Shuma74
Новичок
Джуниор
 
Регистрация: 17.10.2018
Сообщений: 2
По умолчанию

Для того, чтобы окончательно закрыть эту тему - файл с программой тут:

https://davevsziggy.wordpress.com/%d...b%d0%be%d0%b3/

По существу вопроса оптимизации на скорость исполнения в VBA (это не все, что нужно знать, а только главное из того, с чем мне пришлось столкнуться при написании программы и ее "разгоне"):
1.Не комментируйте ячейки и не присваивайте им индивидуальные имена без крайней необходимости.
2.Не юзайте пользовательские классы (лучше прописать структуру данных и методы отдельно в VBA - выигрыш колоссальный).
3.Не забывайте отключать отрисовку экрана в местах, где она не нужна.
4.Не работайте с отдельными ячейками для загрузки/выгрузки данных - присваивайте массив переменной и работайте с ней, а еще лучше выгрузить содержимое ячеек в массив с простым типом данных (Int, long, double, string).
5.Чтобы знать форматирование ячеек - не обращайтесь к ним, храните изменения форматов в дублирующем буфере в переменной, обращайтесь к нему)
6.Не форматируйте много ячеек одним и тем же форматом поштучно - накладывайте форматирование сразу на весь массив ячеек.
7.Отключайте в начале программы DisplayAlerts, EnableEvents*
8.Ставьте в начале кода Application.Calculation = xlCalculationManual*
9.Отключайте в начале программы DisplayStatusBar*
10.В коде избегайте переменных типа Variant и Object где это возможно,
где возможно используйте Boolean
11.Где возможно - пользуйтесь Select case - end select для множественного выбора вместо if-elseif-end if
12.Где возможно - пользуйтесь With - end with для ускорения доступа к элементам структуры данных.
13.Сложение быстрее умножения (Х+Х быстрее чем Х*2), умножение быстрее деления (Х*0,5 быстрее чем Х/2) и возведения в степень (Х*Х быстрее чем Х^2), целочисленное деление \ быстрее дробного /.
14.Операции над целочисленным типом данных Int все еще немного быстрее, чем над числами с плавающей запятой (из которых Double немного быстрее, чем Single)
15.Не дробите критические места программы на под-под-под-подпроцедурные уровни. Каждый вызов подпрограммы требует времени.
16.Если у Вас огромный массив чисел в малом диапазоне значений (в пределах байта), над которым программа относительно нечасто производит вычисления - лучше и создать массив типа byte.
17. Если у вас много процедур/функций, к которым вы обращаетесь нечасто в ходе исполнения программы, а общее количество кода значительно - лучше вынести эти функции в отдельный модуль.
18. И не делайте файл слишком большим, если это возможно. Больше файл - медленнее счет.
19. Не делайте в файле слишком много листов.
20. Старайтесь обходиться минимумом (или вообще обходиться без) пользовательских форм.
__________________________
* - желательно сохранить предыдущее значение и восстановить его в конце Вашей программы.

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

Последний раз редактировалось Shuma74; 24.08.2019 в 23:55.
Shuma74 вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
расшифрование кода во время исполнения xqr Безопасность, Шифрование 9 12.05.2015 19:40
добавление данных строками... Listview (оптимизация кода) Fahman Общие вопросы Delphi 7 25.07.2014 18:01
скорость обращения к строке (массив) xrob Общие вопросы Delphi 2 30.08.2013 13:31
Скорость исполнения макроса в Excel-2010 намного ниже, чем в Excel-2003 Павел+ Microsoft Office Excel 5 29.12.2010 03:28
Оптимизация времени исполнения макроса basil0 Microsoft Office Excel 12 06.12.2010 10:20