ExcelでRDB(リレーショナルデータベース)を操作してみようという連載のその27です。前回の記事はこちらです。
インデックスを試してみる
前回はクラスモジュールの修正点についてご紹介しました。
今回はインデックスを試してみたいと思います。
インデックスとは何か
話がちょっとそれるのですが、僕が所属しているノンプロ研で以下の本の輪読会をしています。
その中でインデックスの部分を僕が担当しました。でも、インデックスを今まで使ったことがないんですよ。というのも僕が普段データベースとして使っているファイルは、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の性能も向上しているので、この程度では差が出ないのかもしれません。ファイルサイズが大きくなることを考えると、インデックスの設定は効果を考えたうえで慎重に行った方がよさそうですね。
以上で終わりです!!
でも、今までこの連載を通してインデックスの設定を含めて色々なことが試せるようになりました。大きな学びになって良かったと思います!!この連載は、これで終了となります。ありがとうございました!!