Microsoft Access 掲示板

100万件のデータに対して、クエリ( SQL )の実行を10回繰り返し
最終行のデータが表示されるまでの平均所要時間を計測してみました。
( EVENT, E_Time, E_Cond, Seq 各列にインデックスや主キーの設定は無し )

< ベンチマーク実施環境 >
バージョン:　 Access 2019 (32bit)
OS:　　　　　Windows 10 Pro 64bit
CPU:　　　　 Intel Core i9-8950HK 2.90GHz
メモリ：　　  64 GB
ディスク：　  Samsung SSD 970 Pro
　
< 実行結果 >

    Const TOTAL_REC    As Long = 1000000
    Const EVENT_CHAR   As Long = 3
    Const MAX_DURATION As Long = 120
    Const TABLE_NAME   As String = "表1"

    With CurrentDb
        If (DCount("*", "MSysObjects", "[Name] = '" & TABLE_NAME & "'") = 0) Then
            Dim strDDL As String
            strDDL = "CREATE TABLE " & TABLENAME & "     " & vbNewLine
                   & "(                                   " & vbNewLine
                   & "      EVENT   VARCHAR(50)  NOT NULL " & vbNewLine
                   & "    , ETime  DATETIME     NOT NULL " & vbNewLine
                   & "    , ECond  VARCHAR(20)  NOT NULL " & vbNewLine
                   & "    , Seq     INT                   " & vbNewLine _
                   & ");"
            .Execute Query:=strDDL, Options:=dbFailOnError
            Application.RefreshDatabaseWindow
        End If
        .Execute Query:="DELETE FROM " & TABLE_NAME & ";", Options:=dbFailOnError

        Dim ts As Date
        ts = DateSerial(Year(Date), 1, 1) + TimeSerial(0, 0, 1)

        Dim dic As Object
        Set dic = CreateObject("Scripting.Dictionary")

        With .OpenRecordset(TABLE_NAME, dbOpenTable)
            Dim i      As Long
            Dim j      As Long
            Dim e_name As String

            e_name = Space$(EVENT_CHAR)

            For i = 1 To TOTAL_REC
                If (i Mod 2 = 1) Then
                    Randomize
                    For j = 1 To EVENT_CHAR
                        Mid(e_name, j, 1) = Chr$(Int((90 - 65 + 1) Rnd) + 65)
                    Next j
                    dic(e_name) = dic(e_name) + 1
                End If
                ts = DateAdd("s", Int(MAX_DURATION Rnd + 1), ts)

                .AddNew
                .Fields("EVENT").Value = e_name
                .Fields("E_Time").Value = ts
                .Fields("E_Cond").Value = IIf(i Mod 2 = 1, "START", "END")
                .Fields("Seq").Value = CLng(dic(e_name))
                .Update
            Next i
            .Close
        End With

        Set dic = Nothing
        .Close
    End With
    MsgBox Prompt:="データ作成完了", Buttons:=vbInformation, Title:="実行結果"
End Sub