Access 2007 Улучшение запроса / VBA для группировки по связанному списку

Question

Я получаю несколько таблиц, в которых есть элементы, сгруппированные по связанным спискам, и у меня возникают проблемы с этим.

Функция работает так, как есть, но меня часто спрашивают, где находится ее макрос при запуске с планировщика задач или есть проблемы с памятью.

Я использую следующий код, чтобы узнать idGroup (переведено на английский), и мне интересно, есть ли способы улучшить его, особенно его скорость, потому что это занимает до часа для 30 000 строк и около 2500 групп... (Вот почему я использовал VBA, чтобы увидеть прогресс...)

'Simple example
'idGroup,id2,id1
'6338546,14322882,13608969
'6338546,13608969,13255363
'6338546,6338546,14322882
'6338546,11837926,11316332
'6338546,12297571,11837926
'6338546,13255363,12811071
'6338546,12811071,12297571
'6338546,7610194,7343817
'6338546,7935943,7610194
'6338546,8531387,7935943
'6338546,6944491,6611041
'6338546,7343817,6944491
'6338546,9968746,9632204
'6338546,10381694,9968746
'6338546,6611041,0
'6338546,8920224,8531387
'6338546,9632204,8920224
'6338546,11316332,10941093
'6338546,10941093,10381694


Public Function GetidGroup()
    'first id1 is always 0
    sql = "SELECT idGroup, id2, id1 FROM TABLE_WITH_LINKED_LIST WHERE id1='0' ORDER BY id2 DESC"
    Dim rs As Recordset
    Dim uidLikedList As String, id2 As String, id1 As String

    Set rs = CurrentDb.OpenRecordset(sql)
    Dim total As Long
    Dim idGroup As String
    Dim incrément As Long, progress As Double

    total = rs.RecordCount
    incrément = 1

    While Not rs.EOF
        progress = Math.Round(100 * incrément / total, 2)

        'Print in order to avoir freezing
        Debug.Print progress

        If rs.Fields("idGroup") = "" Then
            id2 = rs.Fields("id2")

            idGroup = precedentUid(id2)

            rs.Edit
            rs.Fields("idGroup") = idGroup
            rs.Update
        End If

        incrément = incrément + 1
        rs.MoveNext
    Wend

    rs.Close
    Set rs = Nothing
    GetidGroup = total
End Function

'Recursive function
'Deepest so far is about 62 calls
Public Function precedentUid(id2 As String) As String
    sql = "SELECT idGroup, id2 FROM TABLE_WITH_LINKED_LIST WHERE id1 = '" & id2 & "'"
    Dim rs As Recordset
    Dim precedentid2 As String
    Dim idGroup As String
    Dim ret As String

    Set rs = CurrentDb.OpenRecordset(sql)
    If rs.EOF Then
        rs.Close
        Set rs = Nothing
        precedentUid = id2
    Else
        'Some records have several references
        '56 impacted records  :
        'TODO : Give the min id2 to the group
        ret = "-1"
        While Not rs.EOF           
            If rs.Fields("idGroup") = "" Then
                precedentid2 = rs.Fields("id2")
                idGroup = precedentUid(precedentid2)

                If ret = "-1" Or CLng(ret) > CLng(idGroup) Then
                    ret = idGroup
                End If

                'Debug.Print id2 & " " & precedentid2 & " " & idGroup

                rs.Edit
                    rs.Fields("idGroup") = idGroup
                rs.Update
            End If
            rs.MoveNext
        Wend
        rs.Close
        Set rs = Nothing
        precedentUid = ret
    End If
End Function

Answer 1

Некоторые предложения:

1. Вы открываете большое количество наборов записей (для каждого вызова precedentUid). Вместо этого рассмотрите возможность использования одного набора записей, отсортированного по idGroup+id1 и поиск вверх или вниз для соответствующего значения.
2. Так как вы всегда ищете на idGroup+id1Я бы посоветовал сделать это первичным ключом. После этого вы сможете использовать Seek метод для более быстрого поиска.
3. Если у вас есть первичный ключ, вам не нужно редактировать отдельный набор записей, и он будет загружаться быстрее. Когда вы должны обновить idGroup, используйте оператор SQL вместе с CurrentDb.Execute,
4. Кэшировать результаты поиска idGroup в Dictionary (обратитесь к Microsoft Scripting Runtime в Инструменты -> Ссылки). Таким образом, вы не будете повторять поиск во время повторения.
5. Кажется, что ваши примерные данные представляют собой все числа, но вы извлекаете их из набора записей в виде строк. Основной тип данных должен быть Long, и не Text, Если у вас нет контроля над этим, я бы рассмотрел создание временной таблицы с соответствующими типами данных.