« VB MySQLのデータをCSVファイルに出力するツール | トップページ | 「不労と不老」エクサスケールの衝撃 »

2015年4月15日 (水)

Oracle VBA/VB oo4o,ODBC,ADO 速度比較

会社で大きなシステム変更がありシステム屋さん達が多忙のため何故だか別業務の私に簡単なデータベース読み出しソフトの制作依頼が来ました。

会社で採用されているデータベースは「オラクル」で,その読み出し用プログラムのコードは主にExcel VBAかVisualBasic6.0,データベース接続クライアントに「oo4o」を使用した10年以上前から変わらぬ構造のものです。

今回依頼されたものは,読み出すデータが異なるだけでコードの基本構造は同じで良かったため,当初,以前からあるVBAのコードをちょっと修正してCSVファイルを出力するプログラムを作りました。

しかし,Excelを自動実行するとオブジェクトが残ってメモリーのゴミになるとかの弊害が自身の経験であったため,仕方無いと思いつつVisualBasic2013+ODBCでコードを丸々作り直しました。

これで完成と思って,何気に読み出し時間を比較してみたところVBAよりもVBの方が倍くらい早い感じ。元々VBAが遅いのか,それともoo4oが遅いのかと思いつつVBAのコードに手を付ける気力は無くて取り敢えず任務完了となりました。

しかし,そういえば自宅で使用しているMySQLはVBAはODBC,VBは「ADO」を使用していることを思い出し,ネット検索してみたところ,オラクルも.NET対応のADOがあるらしいと判明。
Oracle Universal Installerを起動しインストールされているソフトを確認したところ「Oracle Data Provider for .NET」がインストールされていました。
オラクルのホームページの解説では「Oracle Data Provider for .NET(ODP.NET)では、Oracleデータベースに対するADO.NETデータ・アクセスの最適化を実現します。」と記載されており期待が持てます。

で,さっそくADOでもプログラムを記述,ついでにVB+oo4oでも記述し,それぞれ掛かった時間を比較してみました。

結果
ネットワーク上のオラクルデータベースから8000行,19列のデータを読みだした平均時間
VBA oo4o 4.8秒
VB  oo4o 9.7秒
VB  ODBC 2.4秒 VBA比2.0倍高速
VB  ADO  1.4秒 VBA比3.4倍高速

なんとVB+ODBCはVBA比で2倍高速であることが分かりました。ADOであればさらに高速です。但し,何故だかVB+oo4oは超遅いです(多分oo4o関連のパラメータ選択が今いち)。
今回は数秒と短いですが,システム部門から提供されているVBAやソフトではデータ量によっては結果が出るまでたまに30分くらい延々と待たされることがあり業務ロスとなっています(しかも10年来この調子)。
これが,やりようによっては1/3の時間で済むかも知れません。

理想はVBまたはC#でコードを全面書き直しなんですが,ユーザーとしてデータベースの読み出しが遅くて困ることは時間比率では大したことがないので,多分これからもシステム屋の工数は割けないですね。他の優先事項が多すぎます。

なお,ADOが最善との結論になったのですが,社内の複数のPCを確認したところ,なんとインストールされているオラクルのクライアントバージョンが9iと11gの2種類があり,9iの方ではADOがインストールされいないらしく動作しませんでした。
そのため,私自身が制作する小物のツールはどのPCでも動きそうなVB+ODBCとなります。

それにしても,自宅パソコンなら管理者,使用者とも自分一人なので融通が効きますが,社内での歴史あるシステムを作り替えるとなると相当なエネルギーが必要なようですね。
データベースを利用するソフトに関しては改善が進まずこれから10年間もまた同じような感じでしょう。もやもやが取れません。

さて,どうしたものか…


VisualBasicのプログラムコードの一部
(検証したデータベースのクライアント ORACLE 11g)

Oracle oo4o 対応のコード
参照設定 com Oracle Inproc Server 5.0 Type Liberty
(.NET Framework 4対応)
----------------------------------------------------------
Dim oraSess As OracleInProcServer.OraSessionClass
Dim oraDb As OracleInProcServer.OraDatabase
Dim Rs As OracleInProcServer.OraDynaset
Dim cnt As String = userId & "/" & password
Dim objectList As New List(Of Object)

oraSess = New OracleInProcServer.OraSessionClass
oraDb = oraSess.OpenDatabase(database, cnt, OracleInProcServer.dbOption.ORADB_DEFAULT)
----------------------------------------------------------


Oracle ODBC Driver 対応のコード
----------------------------------------------------------
Imports System.Data.Odbc

Dim myCon As New OdbcConnection()
Dim myCommand As New OdbcCommand()
Dim myReader As OdbcDataReader

myCon.ConnectionString = _
"DSN=" & dsn & ";" & _
"DATABASE=" & database & ";" & _
"UID=" & userId & ";" & _
"PWD=" & password & ";"

myCon.Open()
----------------------------------------------------------

Oracle Data Provider for .NET 対応のコード
(.NET Framework 2.0対応)
----------------------------------------------------------
Imports System.Data.Common

Dim factory As DbProviderFactory = _
DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
Dim csbuilder As DbConnectionStringBuilder = _
factory.CreateConnectionStringBuilder
csbuilder("Data Source") = database
csbuilder("User ID") = userId
csbuilder("Password") = password

Dim conn As DbConnection = factory.CreateConnection()
conn.ConnectionString = csbuilder.ConnectionString
conn.Open()
----------------------------------------------------------

|

« VB MySQLのデータをCSVファイルに出力するツール | トップページ | 「不労と不老」エクサスケールの衝撃 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/206107/61444720

この記事へのトラックバック一覧です: Oracle VBA/VB oo4o,ODBC,ADO 速度比較:

« VB MySQLのデータをCSVファイルに出力するツール | トップページ | 「不労と不老」エクサスケールの衝撃 »