EXCELをDB的に使ってみたが

遅かったlol
2枚のシートを2テーブルのDBに見立てて、
1方に親レコード、もう1方に子レコードを蓄えてるデータがあって、
そのデータから子を持つ親の一覧を取得〜みたいな処理を組んだとき、

中間データ持つのがめんどうだったので、
親の数だけ毎回子シートを直接検索するという漢仕様でGOしてみたところ、
1000×1000くらいのデータ量で実行したら固まったlol

たかだかこの程度の量で固まるわけない、
ここを直すと大手術だから触りたくないどこか他がおかしいに違いない、と探してみたが、
やはり検索のところが重かった。

なんだよーオンメモリじゃないの〜とセルフへこんでみたわけですが、
じゃあどうすればいいのっていうのを知るために、
いろんな実装で測定してみたところ、

ダイレクト操作>>>>>>>>>>>>>>>>>>>>コレクション>>>>配列、構造体

おおむねこんな感じでした。
500×500件で、ダイレクト15秒、コレクション4.5秒、配列と構造体は0.06秒（！）くらい。
VBAのコレクションは性能低いので納得ですが、まさかダイレクトがここまで遅いとは。
というか固まってしまうので越えられない壁的な何かでした。
直すか・・・


一応ソースも貼り付けてみる。
面白いのは、Cells(r,c)の.textを外すと時間が半分くらいになること。
明示的に指定するほうが早いと思ってたけど、そうでもなかったらしい。

Const ROWCNT = 500

Type dataRow
  data As Variant
  row As Long
End Type

'構造体
Sub test_type()
  Dim t
  t = Timer

  Dim sheet1 As Object
  Set sheet1 = ThisWorkbook.Worksheets("Sheet1")

  Dim sheet2 As Object
  Set sheet2 = ThisWorkbook.Worksheets("Sheet2")
  
  Dim s1() As dataRow, s2() As dataRow
  ReDim s1(1 To ROWCNT)
  ReDim s2(1 To ROWCNT)
  
  Dim i As Long, j As Long, cnt As Long

  For i = 1 To ROWCNT
    s1(i).data = sheet1.Cells(i, 1)
    s1(i).row = i
  Next
  For i = 1 To ROWCNT
    s2(i).data = sheet2.Cells(i, 1)
    s2(i).row = i
  Next

  For i = 1 To ROWCNT
    For j = 1 To ROWCNT
      If s1(i).data = s2(j).data Then
        cnt = cnt + 1
      End If
    Next
  Next
  Set sheet1 = Nothing
  Set sheet2 = Nothing
  MsgBox Timer - t & "@" & cnt
End Sub

'ダイレクト操作
Sub test_direct()
  (中略)
  Dim i As Long, j As Long, cnt As Long
  For i = 1 To ROWCNT
    For j = 1 To ROWCNT
      If sheet1.Cells(i, 1).Text = sheet2.Cells(j, 1).Text Then
'      If sheet1.Cells(i, 1) = sheet2.Cells(j, 1) Then
        cnt = cnt + 1
      End If
    Next
  Next
  Set sheet1 = Nothing
  Set sheet2 = Nothing
  MsgBox Timer - t & "@" & cnt
End Sub

'配列
Sub test_array()
  (中略)
  Dim s1(), s2()
  ReDim s1(1 To ROWCNT)
  ReDim s2(1 To ROWCNT)

  Dim i As Long, j As Long, cnt As Long

  For i = 1 To ROWCNT
    s1(i) = sheet1.Cells(i, 1)
  Next
  For i = 1 To ROWCNT
    s2(i) = sheet2.Cells(i, 1)
  Next

  For i = 1 To ROWCNT
    For j = 1 To ROWCNT
      If s1(i) = s2(j) Then
        cnt = cnt + 1
      End If
    Next
  Next
  Set sheet1 = Nothing
  Set sheet2 = Nothing
  MsgBox Timer - t & "@" & cnt
End Sub

'コレクション
Sub test_collection()
  (中略)
  Dim s1 As New Collection, s2 As New Collection
  Dim i As Long, j As Long, cnt As Long

  For i = 1 To ROWCNT
    s1.Add sheet1.Cells(i, 1)
  Next
  For i = 1 To ROWCNT
    s2.Add sheet2.Cells(i, 1)
  Next

  For i = 1 To ROWCNT
    For j = 1 To ROWCNT
      If s1(i) = s2(j) Then
        cnt = cnt + 1
      End If
    Next
  Next
  Set sheet1 = Nothing
  Set sheet2 = Nothing
  MsgBox Timer - t & "@" & cnt
End Sub

なーにも〜やる気がし〜ない〜