Как повысить скорость обработки обновления значения списка SharePoint из VBA (ADODB)

Есть ли какой-либо способ повысить скорость обработки обновления значения списка 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

«все это не работает». Не работаете над повышением скорости обработки или не работаете в том смысле, что получаете ошибки?

Tim Williams 09.06.2024 19:27

Пробовали ли вы выполнить запрос на обновление, например update table_name set field_name = 'blah' where field_name = 'whatever'?

Tim Williams 09.06.2024 19:29

Спасибо, Тим Уильямс. «Все это не работает» означает «Все это не работает для повышения скорости обработки». Я изменил свой вопрос выше. Я еще не пробовал «выполнить запрос на обновление». Я хочу попробовать это, не могли бы вы рассказать об этом подробнее?

KK0414 10.06.2024 15:23

что-то вроде Sql = "update [" & ListName & "] set [Target_col] = 'Blah' where [Title] in('1','2','3','4','5') позволит вам обновлять несколько записей одновременно - вы можете программно создать список «входящих», включающий, возможно, (например) 50 элементов для каждого вызова.

Tim Williams 12.06.2024 20:00
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
4
100
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вот пример запуска обновления списка в 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 минута, но это не точно, извините)

KK0414 12.06.2024 06:20

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

Tim Williams 12.06.2024 07:53

Я обновил свои вопросы, как указано выше. Именно это я и сделал. Кажется, я обновлял только одну запись за раз. Не могли бы вы показать мне рекомендуемый вами код?

KK0414 12.06.2024 15:38

Большое спасибо ! Результат пакетного обновления составляет ок. 13 секунд / 400 элементов (в 3 раза быстрее, чем раньше). Теперь я пытаюсь выполнить обновление нескольких значений для каждого заголовка. Я искал это в Google и нашел следующий SQL, но получил сообщение об ошибке (добавьте точку с запятой в конце). 'ВСТАВИТЬ В [Таблицу] (Заголовок, Столбец_1) ЗНАЧЕНИЯ (1,1),(2,2),(3,3)... ПРИ ОБНОВЛЕНИИ ДУБЛИКАЦИОННОГО КЛЮЧА Col_1 = VALUES(Col_1);' Я уже добавил точку с запятой в конце, но получил то же сообщение об ошибке... Возможно, это не по теме, но не могли бы вы дать мне несколько советов по этому поводу?

KK0414 13.06.2024 05:53

Извините, я не могу помочь с этим вопросом - возможно, опубликуйте его как новый вопрос.

Tim Williams 13.06.2024 17:32

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

KK0414 15.06.2024 09:49

Другие вопросы по теме