Страницы: 1
RSS
Проблема производительности макроса после SQl запроса
 
Добрый день! Написал макрос (код в приложении, сам файл выкладывать не хотелось бы из-за большого объема и персональной информации, да и суть вопроса не в этом), смысл которого достаточно прост: в столбце В даты записаны как "31.01.2018  9:58:42" (в таком виде выгружаются из БД), я хочу оставить в значениях только дату. Все работает замечательно, за пару секунд обрабатывается весь массив, как и ожидалось. В книге есть odbc подключение, которое обновляет значения на листе при refresh all. Если после выполнения sql запроса я запускаю макрос на листе, в котором производится операция, то все отрабатывает в секунды. Но стоит мне попробовать воспроизвести макрос с другого листа, и выполнение простейшей операции замедляется многократно. Никаких ошибок и вылетов не происходит, данную закономерность я заметил далеко не сразу, и пробовал сам разобраться: отключить все, что может замедлять в Application, пробовал поставить sheet.select или activate в начале макроса - толку ноль. Если после выполнения запроса я вручную активирую лист, потом перехожу на любой другой и уже с него запускаю макрос, то проблем нет, если без активации - получаю существенное замедление. Я знаю, что проблема высосана из пальца, и ничто не мешает мне запускать макрос с того же листа. Можно добиться того же результата с помощью формул, ну или поизварщаться с sql и решить проблему на корню. Но в будущем меня ждет больше работы по взаимодействию макросов и sql, поэтому хотелось бы понять конкретно что может так замедлять процесс. Буду благодарен за ваши подсказки и советы
P.S. на роль крутого программиста вообще не претендую, учусь по ходу дела)
 
а так попробуйте.
Код
Sub DeleteTimeFromFact()
'   ----------------------------------------
   Dim arr(),i&,LastRow&
   Dim  RepMonth As Range
   Dim sht As Worksheet
'   ----------------------------------------
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

   Set sht = ThisWorkbook.Worksheets("OPEX_fact")
   With sht
      LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
      arr = .Range("b2").Resize(LastRow,1).Value
      For i = 1 to  UBound(arr)
         arr(i,1) = DateSerial(Year(arr(i,1)), Month(arr(i,1)), Day(arr(i,1)))
      Next i
      .Range("b2").Resize(LastRow,1).Value = arr
   End with
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
End Sub


Изменил код, первый вариант с косяками (в редакторе писать код не привычно)
Изменено: Nordheim - 19.11.2018 11:11:30
"Все гениальное просто, а все простое гениально!!!"
 
Ругается на строку
Код
arr(i, 1) = DateSerial(Year(cell), Month(cell), Day(cell))

Цитата
Subscript out of range
 
Nordheim, опечатка:
Код
LastRow = .Range("B" & .Rows.Cpunt).End(xlUp).Row
Cpunt замените на Count.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Опечатку заметил, ошибка возникает дальше при попытке запуска с другого листа ( на листе OPEX_fact отрабатывает по красоте)
Код
arr = Range("b2").Resize(LastRow, 1).Value

Цитата
Type mismatch
 
Цитата
JayBhagavan написал:
Nordheim , опечатка:
Спасибо, зашел как раз поправить  :D
"Все гениальное просто, а все простое гениально!!!"
 
Цитата
LackOfImagination написал: arr = Range("b2").Resize(LastRow, 1).Value
Просто перед Range точка пропущена.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
LackOfImagination написал:
arr = Range("b2").Resize(LastRow, 1).Value
А зачем Вы, простите, код Nordheim'а-то поменяли? У него
Код
arr = .Range("b2").Resize(LastRow,1).Value
Естественно, с неизвестно каким листом он работать не будет.
 
Цитата
LackOfImagination написал:
arr(i, 1) = DateSerial(Year(cell), Month(cell), Day(cell))
Кто ругается? В коде Nordheim'а ни этой строки, ни переменной cell вообще нету...
 
Цитата
LackOfImagination написал:
или поизварщаться с sql
Какие извращения, просто INT() дописать, это самый естественный способ.
 
Извиняюсь, точку убрал т.к. думал, что это вызывает ошибку, такая форма записи мне непривычна. Спасибо! Теперь все быстро и корректно запускается с любого листа.
 
Цитата
LackOfImagination написал:
все отрабатывает в секунды
А должно - в доли секунды
Код
Sub DeleteTimeFromFact()
  With ThisWorkbook.Worksheets("OPEX_fact")
    With .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
      .Value = Evaluate("INDEX(INT(" & .Address(, , Application.ReferenceStyle, True) & "),)")
    End With
  End With
End Sub
 
Супер! спасибо
 
LackOfImagination,  а что мешает еще в запросе избавится от времени и оставить только дату?
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
а что мешает еще в запросе избавится от времени и оставить только дату?
А в этом случае не нужно перечислять все столбцы вместо написания символа "*"? Вопрос задал, для общего развития  :D
"Все гениальное просто, а все простое гениально!!!"
 
Nordheim,  Надо :-) И это тяжкий труд :-). Правда я предпочитаю даже в WMI запросе указать то, что нужно и не тянуть все подряд, хотя все примеры как раз на select *.
По вопросам из тем форума, личку не читаю.
 
Цитата
Nordheim написал:
А в этом случае не нужно перечислять все столбцы вместо написания символа "*"?
В "Анекдот месяца" :D  
Владимир
 
Цитата
sokol92 написал:
В "Анекдот месяца"  
Я бы конечно посмеялся, но когда работаешь  с кучей таблиц в которых от 20 до 55 столбцов, мне лично лень писать все столбцы, да еще с применением формул.
"Все гениальное просто, а все простое гениально!!!"
 
sokol92, Nordheim,  Мальчики, не ссорьтесь :-)
Конечно если полей много, и почти все нужны, то * спасет, но надо держать баланс между простотой записи, и результативностью. Может же получится что разово записать длинный SELECT сложно , но потом оптимально данные будут бродить от источника. Лично я, как админ? которому дорог каждый IP пакет, за длинный код и короткий результат :-)
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Конечно если полей много, и почти все нужны,
Да дело в том что не нужны, но сама таблица может измениться, вроде название одно а столбики добавились, поэтому я и исключил вариант переработки самого запроса , и переделал макросом, получилось конечно не как макрос от Казанский, но думаю что немного ускорил работу исходного кода.  :D .
Цитата
БМВ написал:
Мальчики, не ссорьтесь :-)
Да и ссориться нет повода, я просто озвучил то, что мне бы не захотелось писать лишнее. Возможно это со стороны и смешно, но каждый судит по своим реалиям.
Изменено: Nordheim - 19.11.2018 20:54:22
"Все гениальное просто, а все простое гениально!!!"
 
Цитата
Nordheim написал:
но сама таблица может измениться, вроде название одно а столбики добавились
а вот тут я скорее за контролируемый Select. Мне б хотелось получить  тот набор данных который планировал и именно в той последовательности что  (хотя можно и поля выгрузить и с ними работать, но потом искать что где ...) , а не тот что окажется.
По вопросам из тем форума, личку не читаю.
 
Это будет очень долго и муторно проще получить всю таблицу а лишнее удалить чем ваять код, таблица может меняться раз в 2-3 недели. и каждый раз лезть в код и менять что-что-то не каждому захочется.
"Все гениальное просто, а все простое гениально!!!"
 
Цитата
Nordheim написал:
Это будет очень долго и муторно проще получить всю таблицу а лишнее удалить чем ваять код,
Иногда мне кажется что программисты 1c именно так и делают.
Nordheim, разные бывают варианты, но прикиньте запрос в LDAP Microsoft AD. Количество стандартных полей(атрибутов) немалая и есть возможность править схему и добавлять свои.  …. ну вы поняли.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
есть возможность править схему и добавлять свои
Согласен, но думаю, что это в основном  под силу программисту, а не аналитику с примитивным знанием основ SQL  :D
"Все гениальное просто, а все простое гениально!!!"
Страницы: 1
Наверх