Страницы: 1
RSS
Оптимизация макроса SQL запроса и обработки полученных данных
 
Добрый день. есть такого вида макрос, вытаскивает из базы SQL данные в табличном виде данные и производит некоторые преобразования:
Есть ли возможность ускорить работу. таблица вываливается большая и поэтому постобработка проходит очень долго (вторая часть макроса).
Код
Sub ****Connection()
Dim i As Integer
Dim ****_Connection As ADODB.Connection
Dim SPSKV As String
Set ****_Connection = CreateObject("ADODB.Connection")
SPSKV = "select idskv from SPISSKV" ****_Connection.Provider = "SQLOLEDB" ****_Connection.ConnectionString= "Server=****; Database=****; User ID=****; pwd=******"
****_Connection.Mode= adModeRead****_Connection.Open
'заполнение шапки таблицы c удалением пробелов перед номерами 

Set dbResults = ****_Connection.Execute("SELECT spisskv.nskv AS 'отверстие',spisskv.kust AS 'коробка',spisskv.idmest AS 'участок',BGDR.DATA AS 'Дата', BGDR.IDPLAST AS 'сторона', BGDR.VID_ISL AS 'Вид',BGDR.NN_ZAM AS 'Количество',BGDR.PBUF_STAT AS 'стат', BGDR.PZATR_STAT AS 'затр', BGDR.H_ZAM AS 'мера',BGDR.HA_ZAM AS 'а.о.зам',BGDR.T_ZAM AS 'тат', BGDR.RO_SR AS 'ось', BGDR.PPL_KRP AS 'рф', BGDR.PPL_VNK AS 'НК', BGDR.T_PERF AS 'Тф',BGDR.T_VNK AS 'Тив', BGDR.PRAV AS 'PRAV' FROM BGDR JOIN spisskv on
BGDR.idskv=spisskv.idskv")

For i = 4 To dbResults.Fields.Count - 1
Cells(7, i + 1).Value = dbResults.Fields(i).Name

Next i Cells(7,2).CopyFromRecordset dbResults

****_Connection.Close

'изменение формата на знач
Columns("B:B").Select
Selection.Replace " ",""
Columns("C:C").Select
Selection.Replace " ",""

'обновление
Columns("D:D").Select

Selection.Replace What:="20",
Replacement:="Ю", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Columns("F:F").Select
Selection.Replace What:="21",
Replacement:="П", LookAt:=xlPart, _
SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("F:F").Select
Selection.Replace What:="281",
Replacement:="Т", LookAt:=xlPart, _
SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="701",
Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


 
Самым быстрым способом будет написать хранимую процедуру, и ее дергать и получать уже полностью готовый набор данных.
более медленным способом реализовать это все в запросе.
Спасибо
 
Доброе время суток
Дмитрий, а чем, в данном случае, хранимая процедура поможет в плане увеличения скорости? Замены можно и в запросе прописать. Чем выгоднее в этом случае хранимая процедура?
 
MEP, привет. Может я слоупок, но можно, плиз, поподробнее, что именно долго работает?
Учусь программировать :)
 
Цитата
Андрей VG написал: Дмитрий, а чем, в данном случае, хранимая процедура поможет в плане увеличения скорости?
1. Хранимая процедура это откомпилированный исполняемый код.
2. Передача 100500 строк запроса по сети медленней, чем  передать по сети имя процедуры и ее параметры.
3. Имхо мне с процедурами работать гораздо удобнее.
----
Анализируя данный пример, можно предположить что это некий отчет который находиться у нескольких пользователей.
При изменении неких критериев в БД все клиентские приложения необходимо заменить (доработать), в случае использования ХП достаточно исправить процедуру в БД.
--------
Надеюсь объяснил понятно :))
С уважением, Дмитрий.
Спасибо
 
Цитата
Smiley написал:  MEP  , привет. Может я слоупок, но можно, плиз, поподробнее, что именно долго работает?
как выше написали, действительно выгружается 100500 строк и потом еще во многих столбцах происходит замена значений на удобные конечному пользователю.
эти действия приходится делать каждый день.
 
Попробуйте замену делать в самом запросе через функцию Replace.
select replace(BGDR.DATA,"107","1")
Изменено: PowerBoy - 30.04.2015 08:24:59
Excel + SQL = Activetables
 
Поддерживаю идею PowerBoy
Изменено: Smiley - 30.04.2015 09:27:40
Учусь программировать :)
 
также согласен с тем что в данной ситуации проще и удобнее будет отредактировать sql запрос и грузить уже готовую информацию без необходимости обработки
 
Цитата
ujin написал: также согласен
Я не высказывался по поводу, что быстрее сделать. Я объяснил в чем преимущество SP от SQL запроса, и почему она выиграет по скорости.
Если Вы хотите услышать ответ на вопрос, что проще и быстрее сделать?
Я отвечу: "Переписать SQL запрос".
Спасибо
 
Доброе время суток
Дмитрий, спасибо большое за объяснения. Хотя, учитывая 100500 возвращаемых записей, не думаю, что хранимая процедура будет иметь заметное преимущество, скорее тогда "материализованное" представление - как учили на курсах. В голове пока всё не сложилось - вот и пытаюсь у знающих людей выяснить необходимые тонкости.
 
Цитата
PowerBoy написал: Попробуйте замену делать в самом запросе через функцию Replace.
Спасибо за подсказку. Вы имеете ввиду в 10 строку кода вставить? попробовал по всякому, можно кусочек на моем примере?
 
В случае если несколько замен будет на 1 параметр, просто дублировать и все? вроде получилось с 1 заменой.
спасибо за помощь
 
в продолжение темы вопрос еще раз по существу задам, т.к. не нашел в рунете и на форуме ответ. можно ли использовать в моем случае в SQL запросе условие If(replace;if(replace; ))) (и как правильно прописать, сам пробовал, не получилось).
такая ситуация складывается что нужно несколько реплэйсов на один выгружаемый столбец. или надо идти старым путем?
Изменено: MEP - 14.05.2015 09:01:42
 
ну в данном случае в запросе надо использовать не if, а case:
Код
case when ... then ...
    when ... then ...
    ...
end
 
Извиняюсь за оффтоп, но-помоему, получилась тема на знание SQL-запросов, а не Excel :)
Изменено: Smiley - 14.05.2015 10:00:19
Учусь программировать :)
 
Цитата
webley написал: ...надо использовать не if, а case
не могли бы вы на моем примере эту вставку показать. с одним из вариантов. как не кручу, не получается впихнуть.

P.S. Скоро дойдем до того что будем 3Д моделирование в экселе обуждать))
 
с Цитированием похоже опять проблемы. только технического характера. я просто нажал "цитировать"
 
Т.е. Вы считаете, что само должно догадаться, что именно Вы хотите цитировать? И техника виновата, что Вы ей не указали часть сообщеня?
Нужно не просто жать кнопку, а выделять  часть, которую хотите вставить в цитату.
Или копировать часть сообщения и оформить цитатой (кнопка на панели ’’)
Страницы: 1
Наверх