インデックスを試す(ExcelでRDBその27)

ExcelRDB(リレーショナルデータベース)を操作してみようという連載のその27です。前回の記事はこちらです。

hirocom777.hatenadiary.org

インデックスを試してみる

前回はクラスモジュールの修正点についてご紹介しました。

今回はインデックスを試してみたいと思います。

インデックスとは何か

話がちょっとそれるのですが、僕が所属しているノンプロ研で以下の本の輪読会をしています。

その中でインデックスの部分を僕が担当しました。でも、インデックスを今まで使ったことがないんですよ。というのも僕が普段データベースとして使っているファイルは、ExcelファイルやCSVファイルなのでインデックスの機能サポートしていないのです。でも、Accessファイルはサポートしています。なので試してみようと思うのです。

尚、事前にVBEのメニューでツール⇒参照設定で参照設定画面を開いてMicrosoft ActiveX Data Objects 2.8 Libraryと、Microsoft ADO Ext.6.0 for DDL and Securityにチェックを入れてください。

テスト用ファイルを作る

それではファイルTest.accdbを作りましょう。コードは以下になります。コードが書かれたExcelファイルと同じ場所に作ります。

Sub MakeSampleFile()
Dim fileName As String
Dim catalogObject As New ADOX.Catalog
Dim tableObject As New ADOX.Table
Dim connectString As String
    
  'Accessファイルを作成
  fileName = ThisWorkbook.Path & "\Test.accdb"
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  catalogObject.Create connectString

  'テーブルを設定
  tableObject.Name = "Table_1"
  'フィールド作成
  tableObject.Columns.Append "ID", adInteger
  tableObject.Columns.Append "数値", adDouble
  
  'テーブル追加
  catalogObject.Tables.Append tableObject
  'クローズ
  catalogObject.ActiveConnection.Close
End Sub

「Table_1」というテーブルを作って、整数型の「ID」と浮動小数点型の「数値」という2つカラムを作りました。次に以下のテスト用のデータを登録します。上記の書籍では10000件以上のデータでインデックスの効果が出てくると紹介されています。ここでは100000件のダミーデータを登録します(「ID」には1~1000000までの通し番号、「数値」には0~1のランダムな小数点値をセットします)。

完了までにかなりの時間をとりますので気を付けてください。

Sub SetData()
Dim adoCn As New ADODB.Connection
Dim adoRs As New ADODB.Recordset
Dim i As Long
 ’ファイルと接続
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Open ThisWorkbook.Path & "\Test.accdb"
  adoRs.CursorLocation = adUseClient
  adoRs.Open "Table_1", adoCn, adOpenKeyset, adLockOptimistic

 'データを追加
  For i = 1 To 100000
    adoRs.AddNew Array("ID", "数値"), Array(i, Rnd)
  Next i
  adoRs.Update
  
  adoRs.Clone
  adoCn.Close
End Sub

これでサンプルファイルができ上りました。

インデックスの設定

次にインデックスを設定します。インデックスの設定にはSQL文のCREATE INDEXを使います。書式は以下になります。

CREAT INDEX インデックス名 ON テーブル名(カラム)

実際には以下のコードを実行します。「数値」のカラムに「IndexTest」の名前でインデックスを設定します。

Sub MakeIndex()
Dim adoCn As New ADODB.Connection
 ’ファイルと接続
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Open ThisWorkbook.Path & "\Test.accdb"
  'インデックス作成
  adoCn.Execute "CREATE INDEX IndexTest ON Table_1 (数値);"
  'クローズ
  adoCn.Close
End Sub

実行してみると音もなく終わるのですが、ファイルサイズは1848KBから3000KBに増えました。増えた分がインデックスなんだと思います。

ちなみにインデックスを削除するにはDROP INDEXを実行します。書式は以下になります。

DROP INDEX インデックス名 ON テーブル名

上のコードのンデックス作成の部分を以下の様に書き換えれば実行できます。

  'インデックス削除
  adoCn.Execute "DROP INDEX IndexTest ON Table_1"

こちらも実行すると音もなく終わるのですが、ファイルサイズは減りませんでした。(削除は実行されているようです)

効果を試してみる

それでは効果を試してみましょう。インデックスを設定しているファイルと、指定ないファイルで以下のコードを実行して差を見たいと思います。上記の書籍ではインデックスのカラムを使用した絞り込みやソートに時間がかかるとのことでした。「数値」の値が0.5より大きいレコードをソートして抽出しています。このコードをシートモジュールに記述して実行すると、シートにデータが表示されます。

Public Sub AccessTest()
Dim adoCn As New ADODB.Connection
Dim adoRs As New ADODB.Recordset
Dim i As Long
  
  Me.Cells.Clear
  'ファイルと接続
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Open ThisWorkbook.Path & "\Test_1.accdb"
  
  'レコードセットを開く
  adoRs.CursorLocation = adUseClient
  adoRs.Open "SELECT * FROM Table_1 WHERE 0.5 < 数値 ORDER BY 数値", adoCn
  
  'フィールドリストを表示
  For i = 0 To adoRs.Fields.Count - 1
    Me.Range("A1").Offset(0, i).Value = adoRs.Fields(i).Name
  Next

  'データを表示
  Me.Range("A2").CopyFromRecordset adoRs
  adoRs.Close
  adoCn.Close
End Sub

で、結果なんですが・・・

実行時間に殆んど差が見られませんでした。

残念!!今はPCの性能も向上しているので、この程度では差が出ないのかもしれません。ファイルサイズが大きくなることを考えると、インデックスの設定は効果を考えたうえで慎重に行った方がよさそうですね。

以上で終わりです!!

でも、今までこの連載を通してインデックスの設定を含めて色々なことが試せるようになりました。大きな学びになって良かったと思います!!この連載は、これで終了となります。ありがとうございました!!

ExcelVBAでAccessファイルを操作する連載はコチラから