Microsoft Access 掲示板

Microsoft Access 掲示板

イベント型データの並び替え / 17

24 コメント
views
4 フォロー
17
hatena 2021/06/22 (火) 13:45:49

mayuさん、ベンチマーク実験ありがとうございました。非常に示唆に富む興味深い実験ですね。
あと、PC性能すごいですね。当方の環境でテストしたら数倍時間かかりました(;^_^A

100万件のデータとなると連番を入力しておいても結構時間がかかるようです。
そこでテーブルに連番を入力するのではなく StartTime フィールドを追加して、"END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。

表１に日付/時刻型のフィールドを追加して、名前を StartTime とします
下記のVBAを実行します。

Public Sub SetStarTime()
    Const strSQL = _
          "SELECT * FROM 表1 ORDER BY EVENT, E_Time, E_Cond DESC;"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Do Until rs.EOF
        Dim stime As Date
        If rs!E_Cond = "START" Then
            stime = rs!E_Time
        Else
            rs.Edit
            rs!StartTime = stime
            rs.Update
        End If
        rs.MoveNext
    Loop

    rs.Close

    MsgBox "StarTime入力完了"
End Sub

クエリは下記のようにシンプルになります。

SELECT
    EVENT,
    StartTime,
    E_Time,
    DateDiff('s', StartTime, E_Time) AS DURATION
FROM
    表1
WHERE
    E_Cond = "END";

E_Condにインデックスを貼っておいたらほぼ一瞬で表示されました。

通報 ...
  • 19
    mayu 2021/06/22 (火) 17:15:38 ef559@a99f1 >> 17

    StartTime フィールドを追加して、
    "END"レコードの方に対応する"START"の時刻を格納するという方法にしてみました。

    発想の柔軟さ、パフォーマンスへの配慮、さすがhatenaさんです。

    標準SQLのウィンドウ関数 LAG が利用可能な SQLServer だと
    以下のSQLで hatenaさんと同等のロジックになりそうですね。

    SELECT EVENT
     , starttime
     , endtime
     , datediff( second, starttime, endtime ) DURATION
FROM
(
    SELECT EVENT
         , E_Time endtime
         , E_Cond
         -- OVER句における E_Cond DESC の指定は不要かも
         , LAG( E_Time, 1 )
               OVER ( PARTITION BY EVENT
                      ORDER BY     E_Time, E_Cond DESC ) starttime
    FROM 表1
) 
VBA_PART
WHERE E_Cond = 'END' ;
  • 20
    hiroton 2021/06/22 (火) 17:20:25 6d871@f966d >> 17

    些細なことですけどENDをチェックする形にするとENDがあればSTARTがあることが保証される（はず）なのでIf判定がおよそ半分減らせますね（処理時間の差がわからない程度の差ですが）

    VBAで連番振ってSQLって方式はどうせVBA使うならこれやればいいじゃないなのでACCESS上の話である以上ナンセンスなんですよね

    実は対抗としては>> 14でりんごさんが試されている通り元のテーブルに手を入れてあげればn²オーダーに見えるクエリでも爆速で動くという手法だと思います。主キーインデックスのないテーブルに主キーインデックスを設定してスカラサブクエリで表示するという手法でもVBAだけと遜色ない速度がでます

    結局のところテーブル設計をしっかりやりましょうという話です

  • 23
    hiroton 2021/06/26 (土) 22:30:33 9ff1a@d02b8 >> 17

    改めて考えてみたんですがSTARTの判定はよくないですね
    ENDのレコードの存在が保証されない場合データが漏れます
    ENDのレコードの存在が保証されているなら奇数偶数でSTART/ENDがきれいに出現するのでIF判定が必要ないです