如何缩短大数据库循环的运行时间?

Question

对于数据库中的每个ID，我必须对链接到它们的所有概念求和。例如，ID 2354 有 3 个概念，每个概念在不同的行上，我必须对 3 个概念的数量求和，将该总和粘贴到最初第一个概念的数量所在的单元格上，然后消除第二和第三个概念的整行。

我的宏已经这样做了，但是运行时间很长，而且我的数据库很大，大约 100,000 行，我需要一种方法来改善运行时间。这是我的代码：

Sub Macro1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Dim t As Long
Dim a As Integer
Dim i As Integer
lastt = Cells(Rows.Count, "A").End(xlUp).Row  vacía
For i = 1 To 30
If Cells(1, i).Text = "Report Legacy Key" Then
For t = 2 To lastt 
For a = 1 To 40
primera = Cells(t, i).Value
ultima = Cells(t + a, i).Value
repetidas = Range(Cells(t, i + 1), Cells(t + a, i + 1))
If primera = ultima Then
c = Application.WorksheetFunction.Sum(repetidas)
Cells(t, i + 1).Activate
ActiveCell.Value = c
Range(Cells(t + 1, "A"), Cells(t + a, "AB")).Select
Selection.Delete Shift:=xlUp
End If

Next a
If IsEmpty(Cells(t + 1, "A").Value) = True Then
Exit For
End If
Next t
End If
Next i
Application.ScreenUpdating = True
Range("M1").Select
Application.CutCopyMode = False
End Sub

此代码允许在大约 1000秒内完成 15 行的过程，考虑到必须完成大约 100,000 行，这非常慢。

Answer 1

您可以执行以下几项操作来缩短执行时间：

• 与其使用单元格，不如使用arrays甚至更好的是，Scripting.Dictionary.仅此选项就会加快很多速度。
• 有时我们倾向于直接在工作表上处理数据，但许多基本操作可以直接在SQL中完成，例如总和。
• 尝试避免冗余操作，例如选择Range，然后使用Selection。直接在Range上进行操作。

例如，您可以更改此设置：

Range(Cells(t + 1, "A"), Cells(t + a, "AB")).Select
Selection.Delete Shift:=xlUp

对此：

Range(Cells(t + 1, "A"), Cells(t + a, "AB")).Delete Shift:=xlUp

遵循此建议应该可以加快速度。

希望这有帮助。