Огромная разница в быстродействии recordset

[VVitafresh]

Один и тот же код:

Код: Выделить всё


SQL = "select * from atech"
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
   
rs.Open SQL, cn
Do While Not rs.EOF
        s = rs.Fields("item").Value
        rs.Fields("codenorm").Value = MyFunc(s)
        rs.Update
        rs.MoveNext
Loop


имеет катастрофически разную скорость выполнения при подключении к базе из VB и непосредственно из VBA в Access'e.
А именно: в VB 100 записей обрабатывает 1 мин., в Access'e 200 тыс. записей -- за 50 сек!!!

Вся разница заключается в способе подключения. В VB я подключаюсь так:

Код: Выделить всё


sCnDb = "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=" & sPth & ";Uid=Admin;Pwd=" & sPWD & ";"

    Set Cn = New ADODB.Connection
    Cn.CommandTimeout = 300
    Cn.ConnectionTimeout = 300
    Cn.CursorLocation = adUseClient
    Cn.Open sCnDb                 


В Access'e:

Код: Выделить всё


Set cn = Application.CurrentProject.Connection


Да и еще в VB коннекшн глобальный объект, в аксесе -- локальный.

В чем может быть причина? И, главное, как исправить ситуацию?

[blinow]

А попробуй в VB сделать асинхронный запрос.

[Andrey Fedorov]

Вся разница заключается в способе подключения.

Ну подключись в VB, для начала, так-же как в Access-e, т.е через OLEDB. Что-то навроде:

Код: Выделить всё

    Set g_cn = New ADODB.Connection
    g_cn.CursorLocation = adUseClient
    g_cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    g_cn.Open "Data Source=" & g_sAppPath & "MyBase.mdb"


[Konst_One]

Код: Выделить всё

ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"

[VVitafresh]

Konst_One, спасибо. Я уже нашел, куда прописывать пароль.

Только скорость в VB не поменялась. Даже еще медленнее стало работать

[Konst_One]

попробуй заюзать UpdateBatch, чтобы в цикле на каждую запись не делать обновление. открывать рекордсет тогда надо будет с флагом adLockBatchOptimistic

[Ennor]

Хм... А понизить требования к блокировкам - религия не позволяет?

Код: Выделить всё

.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly


Кейсет - та еще засада. Я не знаю, может, конкретно Аксесс именно его требует в обязательном порядке, но вообще-то это смерть для клиентского приложения, практически.

[VVitafresh]

Ennor

Код: Выделить всё

.LockType = adLockReadOnly


Не подходит, ведь я делаю rs.update

Проблему скорости я все же решил. Это оказалась такая "мелочь" как тип курсора. Как только я поставил

Код: Выделить всё

.CursorLocation = adUseServer

все зашуршало как следует.

Сейчас у меня другая проблема.

Код: Выделить всё


sSQL = "drop table oatech"
CnAccess.Execute sSQL

'Функция прилинковки таблицы
bResult = LinkTable(sPath1, scPwd1, "oatech", sPath2, scPwd2, sTableForLink)
If bResult = False Then Exit Sub

sSQL = "delete * from atech"
CnAccess.Execute sSQL   

sSQL = "INSERT INTO atech ( field1, field2,... ) " & _
        "SELECT field1, field2,... " & _
        "FROM oatech"
CnAccess.Execute sSQL


В результате этой последовательности на последней команде вылетает ошибка:

-2147217865 - The Microsoft Jet database engine cannot find the input table or query 'oatech'. Make sure it exists and that its name is spelled correctly.

Причем, если тот же код выполнять пошагово, то ошибка не возникает. Возможно, таблица oatech не успевает прилинковаться, а к ней уже происходит обращение... Другого объяснения пока в голову не приходит.
Есть какие-то мысли по этому поводу?

[VVitafresh]

Да, забыл сказать, что прилинковку таблицы я делаю в DAO:
http://bbs.vbstreets.ru/viewtopic.php?t=16212#126449
Может быть в этом проблема?
Как делать аналогичное действие через ADO я не знаю.

[Konst_One]

можно сделать некий селект из этой таблицы перед вставкой, например так:

Код: Выделить всё

sSQL = "delete * from atech"
CnAccess.Execute sSQL   

sSQL = "SELECT * from oatech"
CnAccess.Execute sSQL, adExecuteNoRecord   


sSQL = "INSERT INTO atech ( field1, field2,... ) " & _
        "SELECT field1, field2,... " & _
        "FROM oatech"
CnAccess.Execute sSQL

[VVitafresh]

Неа, фокус не прокатил.
Попробовал перед кодом паузу:

Код: Выделить всё

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sleep 3000


Таже ошибка... Выходит, дело не во времени, необходимом на линковку

Далее, пробую такой изврат:

Код: Выделить всё


On Error Goto ErrorHandler
...
CnAccess.Execute sSQL
...
ErrorHandler:
If i >= 1 Then Stop
Resume
i = i + 1


Так отрабатывает, код нормально выполняется. Но это ИМХО не правильно и некрасиво. Хотелось бы решить проблему по грамотному.
Жду предложений.

[alibek]

Попробуй транзакцию использовать.

[VVitafresh]

Попробуй транзакцию использовать.

alibek, расшифруй немного, как в данном случае мне использовать транзакцию.

[alibek]

Connection.BeginTrans
<SQL>
Connection.CommitTrans

[Ennor]

Алибек, чесслово, попробуй как-нить поймать в профайлере то, что прилетает на сиквел при вызове .BeginTrans. Я даже представить боюсь, как это реализуется для Аксесса...

[VVitafresh]

Connection.BeginTrans
<SQL>
Connection.CommitTrans

Не, ну как пользоваться транзакциями в общем случае я знаю. Я не пойму, что это дает в моем случае.
Ведь:

Код: Выделить всё

CnAccess.Execute sSQL

все равно бьет ошибку и в конечном итоге нужно обработать Error и делать Resume. А если на Resume программа зациклится? Тогда надо предусмотреть выход из обработчика ошибок -- возвращаюсь к коду:

Код: Выделить всё

ErrorHandler:
If i > 1 Then Exit Sub
Resume
i = i + 1

Может быть есть все-таки мысли по поводу причин возникновения данной ошибки? Может есть другой способ прилинковать таблицу и это поможет?

[alibek]

Хотя...
Линкуешь ты ведь через DAO?
А коннект ADO.
Так что либо линкуй также через ADO (ADOX), либо реконнект делай.

[VVitafresh]

Наверное, действительно придется делать реконнект, т.к. линковать через ADO (ADOX) я не умею. Если у кого есть примерчик или ссылка на описание соответствующих методов по ADOX, буду очень благодарен.

[VVitafresh]

Прям наваждение какое-то Копался в MSDN насчет ADOX, создал новую ф-ию линковки таблицы через ADOX (чтоб исключить негативное влияние DAO):

Код: Выделить всё

Public Function LinkJetTable(sDestPath As String, sDestPWD As String, sLinkTblAs As String, sSourcePath As String, sSourcePWD As String, sLinkTbl As String) As Boolean
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    On Error Resume Next
    ' Open the catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & sDestPath & ";Jet OLEDB:Database Password=" & sDestPWD
    If Err.Number <> 0 Then Exit Function
    ' Set the name and target catalog for the table
    tbl.Name = sLinkTblAs
    Set tbl.ParentCatalog = cat
    ' Set the properties to create the link
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = sSourcePath
    tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=" & sSourcePWD
    tbl.Properties("Jet OLEDB:Remote Table Name") = sLinkTbl
    ' Append the table to the collection
    cat.Tables.Append tbl
    If Err.Number = 0 Then LinkJetTable = True
    Set cat = Nothing
End Function

а воз и ныне там... Все равно выбивает ошибку при обращении к прилинкованной таблице.
Выход только один: создавать еще одно соединение с базой и только тогда инсертить из прилинкованной таблицы в новую. Мда.

[Konst_One]

можно попробовать сделать так:

Код: Выделить всё

sSQL = "delete * from atech"
CnAccess.Execute sSQL   

CnAccess.Close
CnAccess.Open

sSQL = "INSERT INTO atech ( field1, field2,... ) " & _
        "SELECT field1, field2,... " & _
        "FROM oatech"
CnAccess.Execute sSQL

[VVitafresh]

Можно и так...
В общем, совместными усилиями разобрались, что после прилинковки без реконнекта не обойтись.

[alibek]

В общем, совместными усилиями разобрались, что после прилинковки без реконнекта не обойтись.

Ты что-то не так делаешь.
Попробуй в ADOX использовать не новое соединение, а текущее.
Set cat.ActiveConnection = cnAccess

[VVitafresh]

Может я и делаю что-то не так, но если я передаю в мою ф-ию LinkJetTable текущий глобальный коннекшн, созданный:

Код: Выделить всё

sCnDb = "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=" & sPth & ";Uid=Admin;Pwd=" & sPWD & ";"
    Set Cn = New ADODB.Connection
    Cn.CursorLocation = adUseServer
    Cn.Open sCnDb

то при линковке возникает ошибка:

3251 - Объект или поставщик не может выполнить требуемую операцию.

В общем-то это наверное логично, ведь в этом случае я использую разных поставщиков и прилинковка не происходит (как использовать Microsoft Access Driver для прилинковки я не нашел).
Но если я свое основное глобальное подключение созадаю:

Код: Выделить всё

OpenCnAccess.CursorLocation = adUseServer
OpenCnAccess.Provider = "Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=" & sPwd
OpenCnAccess.Open "Data Source=" & sPth

то происходит необъяснимое для меня. При выполнении кода:

Код: Выделить всё

SQL = "select * from atech"
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
   
rs.Open SQL, cnAccess
Do While Not rs.EOF
        s = rs.Fields("item").Value
        rs.Fields("codenorm").Value = MyFunc(s)
        rs.Update
        rs.MoveNext
Loop


база разростается с ~100MB до ~1000MB (в десять раз)
Этого я уже понять не могу При использовании первого варианта подключения такого не происходит. Кто-то может объяснить, что бы это значило?

P.S. Зато делать реконнект для исп. прилинкованной табл. действительно не нужно

[alibek]

Во-первых, зачем тебе Keyset?
Во-вторых, используй серверные курсоры, быстрее будет.

[VVitafresh]

Во-первых, зачем тебе Keyset?

А чем он вобщем то плох? Попробовал оставшиеся варианты: adOpenDynamic, adOpenForwardOnly, adOpenStatic -- база все равно точно так же растет.

Во-вторых, используй серверные курсоры, быстрее будет.

Не совсем понял. Разве когда я объявляю:

Код: Выделить всё

OpenCnAccess.CursorLocation = adUseServer

я не использую серверный курсор