Есть ли какой-либо способ повысить скорость обработки обновления значения списка Sharepoint из VBA (ADODB)? Я определил часть кода, которая замедляет скорость обработки. В следующем примере кода это [rs.Fields.Item(Field_name).Value = «Update_value»]. Это занимает ок. 40 секунд, чтобы закончить 400 строк записи. (Слишком медленно) Но если я закомментирую приведенную выше часть, это займет ок. 1 секунда до завершения.
Sub Update_Sharepoint_Table()
SPO_url = "https://..."
Table_name = "SPO_List"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE = " _
& SPO_url & ";LIST = " & Table_name & ";"
Set rs = CreateObject("ADODB.Recordset")
With rs
.Source = Table_name
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Field_name = "Field_name"
rs.MoveFirst
Do until rs.EOF
sample = rs.Fields.Item(Field_name).Value
If sample = "Something" Then
rs.Fields.Item(Field_name).Value = "Update_value" ' If I comment-out this line, the processing speed is dramatically improved.
End If
rs.Update
rs.MoveNext
Loop
End Sub
Я пытался изменить CursorType (adOpenForwardOnly и т. д.), LockType (adLoockPessimistic и т. д.) и время rs.Update (пакетное обновление). Но все это не способствует повышению скорости обработки.
Получив совет от Тима Уильямса, я попробовал следующий код, но результат ок. 38 секунд, чтобы закончить 400 предметов.
Sub SPListUpdate()
Const ServerUrl As String = "https://contoso.sharepoint.com/sites/ABC/"
Const ListName As String = "{e5r6t7h8-3d0e-4890-8111-3531bde50f4k}" 'List GUID
Dim Conn As New ADODB.Connection
Dim Sql As String
Dim recsUpdated As Long
With Conn ' Open the connection
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE = " & ServerUrl & ";" & _
"LIST = " & ListName & ";"
.Open
End With
For i=1 to 400
Sql = "update [" & ListName & "] set [Target_col] = 'Blah' where [Title] = '" & i & "' "
Conn.Execute Sql, recsUpdated
Debug.Print recsUpdated & " record(s) updated"
Next
End Sub
Пробовали ли вы выполнить запрос на обновление, например update table_name set field_name = 'blah' where field_name = 'whatever'
?
Спасибо, Тим Уильямс. «Все это не работает» означает «Все это не работает для повышения скорости обработки». Я изменил свой вопрос выше. Я еще не пробовал «выполнить запрос на обновление». Я хочу попробовать это, не могли бы вы рассказать об этом подробнее?
что-то вроде Sql = "update [" & ListName & "] set [Target_col] = 'Blah' where [Title] in('1','2','3','4','5')
позволит вам обновлять несколько записей одновременно - вы можете программно создать список «входящих», включающий, возможно, (например) 50 элементов для каждого вызова.
Вот пример запуска обновления списка в SharePoint:
Sub SPListUpdate()
Const ServerUrl As String = "https://contoso.sharepoint.com/sites/ABC/"
Const ListName As String = "{e5r6t7h8-3d0e-4890-8111-3531bde50f4k}" 'List GUID
Dim Conn As New ADODB.Connection
Dim Sql As String
Dim recsUpdated As Long
With Conn ' Open the connection
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE = " & ServerUrl & ";" & _
"LIST = " & ListName & ";"
.Open
End With
Sql = "update [" & ListName & "] set [Title] = 'Title3' where [Title] = 'Title2' "
Conn.Execute Sql, recsUpdated
Debug.Print recsUpdated & " record(s) updated"
End Sub
Если вы хотите обновить несколько записей одновременно, вы можете использовать предложение «in» для отправки списка значений за один вызов SQL.
В моем тестовом списке есть только поле «Название» со значениями «Title_001», «Title_002» и т. д.
Const BATCH_COUNT As Long = 20
'...
Dim inList As String, sep As String, ttl As String, listCount As Long
'...
'...
For i = 1 To 50
listCount = listCount + 1
ttl = "Title_" & Format(i, "000")
inList = inList & sep & "'" & ttl & "'"
sep = "," 'set after first record
'execute an update? Reach the batch limit, or end of loop
If listCount = BATCH_COUNT Or (i = 50 And listCount >0) Then
Sql = " update [" & ListName & "] set [Title] = [Title] + '_updt' " & _
" where [Title] in (" & inList & ")"
Debug.Print Sql
Conn.Execute Sql, recsAffected
Debug.Print recsAffected & " record(s) updated"
inList = "" 'reset the list, separator, and count
sep = ""
listCount = 0
End If
Next
'...
SQL выглядит так:
update [{af83a2e4-3d0e-4890-8111-3531bde50e2e}]
set [Title] = [Title] + '_updt' where [Title] in ('Title_001','Title_002','Title_003','Title_004','Title_005',
'Title_006','Title_007','Title_008','Title_009','Title_010',
'Title_011','Title_012','Title_013','Title_014','Title_015',
'Title_016','Title_017','Title_018','Title_019','Title_020')
Спасибо, что предоставили мне пример кода. Я пробовал, но это не помогло улучшить скорость процесса. Результат следующий. - Обновление оригиналом (с использованием набора записей): прибл. 40 сек./400 раз. Обновление по команде запроса (без набора записей): прибл. 38 секунд / 400 элементов (Ранее я говорил, что на 100 элементов уходит примерно 1 минута, но это не точно, извините)
Я подумал, что вы можете сделать одно и то же обновление для всех затронутых записей - если вы обновляете только одну запись за раз, это не будет большим улучшением.
Я обновил свои вопросы, как указано выше. Именно это я и сделал. Кажется, я обновлял только одну запись за раз. Не могли бы вы показать мне рекомендуемый вами код?
Большое спасибо ! Результат пакетного обновления составляет ок. 13 секунд / 400 элементов (в 3 раза быстрее, чем раньше). Теперь я пытаюсь выполнить обновление нескольких значений для каждого заголовка. Я искал это в Google и нашел следующий SQL, но получил сообщение об ошибке (добавьте точку с запятой в конце). 'ВСТАВИТЬ В [Таблицу] (Заголовок, Столбец_1) ЗНАЧЕНИЯ (1,1),(2,2),(3,3)... ПРИ ОБНОВЛЕНИИ ДУБЛИКАЦИОННОГО КЛЮЧА Col_1 = VALUES(Col_1);' Я уже добавил точку с запятой в конце, но получил то же сообщение об ошибке... Возможно, это не по теме, но не могли бы вы дать мне несколько советов по этому поводу?
Извините, я не могу помочь с этим вопросом - возможно, опубликуйте его как новый вопрос.
Опубликованный новый вопрос находится здесь. ссылка
«все это не работает». Не работаете над повышением скорости обработки или не работаете в том смысле, что получаете ошибки?