В предисловии к этому диалогу я хочу сказать, что пал жертвой своего заблуждения. Причем достаточно не обоснованного. Но... из песни слов не выкинешь :(
am 11.12.2001
ТЕСТИРОВАНИЕ: Продукт: Access 97 Как проводилось: В цикле производились вызовы DSum и TSum (аналог Dsum, но через DAO.Recordset, прилагается). Результаты: 1. На прилинкованной через Ms SQL Server ODBC Driver таблице, содержащей 180000 записей: DSum МЕДЛЕННЕЙ примерно в 2 раза! 2. На прилинкованной таблице MsAccess, содержащей 10000 записей (какая попалась, больше было лень искать :))): DSum МЕДЛЕННЕЙ примерно в 1.5 раза! 3. На локальной таблице MsAccess(идентичной п.2) Обе фукнции показывают примерно одинаковый результат.
Вот такие вот результаты.
Код:
'************************************************ ' MsgBox parameters Const MB_OK = 0 ' OK button only Const MB_OKCANCEL = 1 ' OK And Cancel buttons Const MB_ABORTRETRYIGNORE = 2 ' Abort, Retry, And Ignore buttons Const MB_YESNOCANCEL = 3 ' Yes, No, And Cancel buttons Const MB_YESNO = 4 ' Yes And No buttons Const MB_RETRYCANCEL = 5 ' Retry And Cancel buttons
Function tSum(pstrField As String, pstrTable As String, pstrCriteria As String) As Double On Error GoTo tSum_Err ' Function tSum ' Purpose: Replace DSum, which is slow on attached tables ' Created: 1 Feb 1996 T.Best
Dim dbCurrent As Database Dim rstLookup As Recordset Dim dblValue As Double
Set dbCurrent = DBEngine(0)(0) If pstrCriteria = "" Then Set rstLookup = dbCurrent.OpenRecordset("Select Sum([" & pstrField & "]) From [" & pstrTable & "]", DB_OPEN_SNAPSHOT) Else Set rstLookup = dbCurrent.OpenRecordset("Select Sum([" & pstrField & "]) From [" & pstrTable & "] Where " & pstrCriteria, DB_OPEN_SNAPSHOT) End If If Not rstLookup.BOF Then rstLookup.MoveFirst dblValue = Nz(rstLookup(0), 0) Else dblValue = 0 End If rstLookup.Close tSum = dblValue
tSum_Exit: On Error Resume Next rstLookup.Close Exit Function
tSum_Err: Select Case err Case Else End Select
' Retry/Abort/Ignore Select Case MsgBox(err.Description, MB_ABORTRETRYIGNORE Or MB_ICONEXCLAMATION, "Error " & err) Case IDABORT Resume tSum_Exit Case IDRETRY Resume Case IDIGNORE Resume Next End Select Exit Function End Function
'TSUM - код не мой, давнишний, переделывать не стал - не суть! 'далее сам тест: '*************************************************** 'Назначение: 'Входы: 'Выходы: 'am v1.0.0_011211_18:20:18 'http://am.rusimport.ru 'mailto:
Этот e-mail защищен от спам-ботов. Для его просмотра в вашем браузере должна быть включена поддержка Java-script
'*************************************************** Public Function Test() As Boolean On Error GoTo Err_ Dim dt As Date Dim dt2 As Date Dim i As Integer Dim dblSum As Double Const iMax = 100 dt = Now() For i = 1 To iMax 'dblSum = Nz(dSum("[Итого руб]", "Invgo", "Склад=" & i), 0) 'dblSum = Nz(dSum("[Unique]", "EventLog1"), 0) dblSum = Nz(dSum("[Unique]", "EventLog11"), 0) Next i dt2 = Now() For i = 1 To iMax 'dblSum = Nz(tSum("Итого руб", "Invgo", "Склад=" & i), 0) 'dblSum = Nz(tSum("Unique", "EventLog1", ""), 0) dblSum = Nz(tSum("Unique", "EventLog11", ""), 0) Next i
АМ: На первый взгляд мне показалось Ваше тестирование не очень корректным ... Я прошу извинить меня за эту поспешность.
А теперь суть. Вопрос ставился о сравнении скорости рекордсета и (внимание! как оказалось) статистических функциям по подмножеству, но не о статистических функциях SQL(!). То есть, статистических функций, которые вызываются прямо из VB.
Я в процессе этого диалога, впервые для себя подробно с этим разобрался
Справка: Статистические функции возвращают информацию о наборах (подмножестве) записей. Например, с помощью статистических функций подсчитывают число записей в конкретном наборе или определят среднее по значениям конкретного поля. Существуют статистические функции двух типов, статистические функции по подмножеству и статистические функции SQL, выполняющие одни и те же действия, но использующиеся в различных ситуациях. Статистические функции SQL могут быть включены в инструкции SQL, но не вызываются в инструкциях Visual Basic. Статистические функции по подмножеству, допускают вызов непосредственно в инструкциях Visual Basic. Кроме того, они могут быть включены в инструкции SQL, однако, использование статистических функций SQL обычно оказывается более эффективным.
>>и далее:
Примечание. В выражениях для вычисляемых полей в итоговых запросах используют как функцию DSum, так и функцию Sum. При использовании функции DSum суммирование выполняется до группировки данных. При использовании функции Sum, сначала выполняется группировка данных, а потом их суммирование.
Вот простенькая конструкция, которая не затрагивает статистические функции SQL и при этом показывает преимущество cтатистических функций по подмножеству перед использованием рекордсета:
Function aaa() Dim a$, b$ Dim aa&, bb& Dim rs As Recordset bb = timeGetTime Set rs = CurrentDb.OpenRecordset("SELECT artArt.UID From artArt Where ArtArt.Uid='020200000853'") b = rs.Fields(0) rs.Close aa = timeGetTime a = DLookup("uid", "artArt", "[UID]='020200000853'")
MsgBox "Рекордсет: " & aa - bb & vbCr & _ "Агрегат.ф-ции : " & timeGetTime - aa
Разница по времени составляет 15%. Но это с кэшем, при многократном повторении функции. При однократном ее выполнении разница более чем в 10 раз! Аксесс 97 , таблица на 30 000 записей
С другой стороны, глубокоуважаемый мной АМ показал, что нельзя путать (теперь я это знаю!) божий дар с яичницей :))) И переделаю некоторые процедурки на новый лад.
am 14.12.2001
Либо я ничего не понимаю - либо одно из двух :) Проводил твой тест - и даже в нем я не увидел преимущества dLookup! А если открывать рекордсет не dbOpenDynaset (что является недопустимой роскошью при измерении скорости работы :) а dbOpenSnapshot - преимущество его становится явным. При единичных запусках (как в твоем тесте - один раз вызываем каждую) только в 1 случае из десяти рекордсет оказывается медленнее... Если вызывать в цикле (с разными параметрами, чтобы хоть немного избежать эффекта кеширования) - то соотношение времен выполнения Аггр/Recordset колеблется от 1.1 до 2.7... И только один раз из примерно 40 запусков соотношение было 0.73 - но исключения как известно только подтверждают правила :) (Повторюсь - тестирование на прилинкованной таблице с MsSQL Serverс 180000 записей. База рабочая, поэтому вполне вероятны задержки ответа сервера). Единственное преимущество аггрегатная функция показала на ЛОКАЛЬНОЙ таблице Access - там соотношение было от 0.90 до 0.99, но этот случай не интересен. Так что я остаюсь пока при своём мнении, что аггрегатные функции - тормоз :) Кстати, посмотрев профайлером что же делают эти два вызова (см. свой пример) - на сервер посылается АБСОЛЮТНО одинаковый запрос. Через один и тот же connection. Торможение видимо обуславливается внутренними причинами Access - цена в самой подготовке запроса чтоль...
ЗЫ: Кстати в хелпе не нашел указание на разницу "Статистические функции SQL и статистическим функциям по подмножеству". Каковы источники это информации? Я считал что независимо от того, используешь ты DSum в коде VBA или в запросе - вызывается одна и та же функция. (речь про Acc97)
http://am.rusimport.ru
GardenStone 14.12.2001
:)) Очевидно, на моем домашнем компьютере 166МГц величина 0,99 становится более существенной. И, к сожалению, в моей коммуналке сети нет :((
am 14.12.2001
Кстати, как не пародоксально это звучит - я иногда ИСПОЛЬЗУЮ D* функции... Просто надо чётко для себя представлять где это хорошо, а где плохо (я не раз уже высказывал данную мысль - и ещё раз её повторяю....)
И, конечно, Сергей Вакшуль привел хороший логический довод: Даже если не делать эксперементов, а порассуждать взагалi(есть такое слово:), то как может функция VB работать быстрее специализированного DAO или ADO? DLookup и ей подобные функции, я так понимаю, это какие-то надстройки над нормальными объектами доступа к данным, а раз надстройки, то и работают они как надстройки, т.е. дольше. Просмотров: 9419