ExcelからVBAでSQLiteDBに接続して約500万ﾚｺｰﾄﾞあるﾃｰﾌﾞﾙから5件取得しようとしています｡それで､ある程度複雑なSQLなのですが､TkSQLiteというﾂｰﾙから実行す…

人力検索はてな

ﾓﾊﾞｲﾙ版を表示しています｡PC版はこちら

ExcelからVBAでSQLiteDBに接続して約500万ﾚｺｰﾄﾞあるﾃｰﾌﾞﾙから5件取得しようとしています｡
それで､ある程度複雑なSQLなのですが､TkSQLiteというﾂｰﾙから実行すると
1秒以内にﾚｽﾎﾟﾝｽがかえってきます｡
ところが､肝心のExcelから取得した場合は､おそろしく遅いです｡(数分です)
以下VBｿｰｽですが､ｿｰｽがまずいですか｡
TkSQLiteとExcelで実行速度がなぜ変わってしまうのでしょうか｡｡
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbCol As ADODB.Field

Set cn = New ADODB.Connection
cn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & DB_PATH
cn.Open
Set rs = cn.Execute(prmSql)

それともやはり､なげるSQLが長いからでしょうか｡
vba内に､SQL記述した際､行続文字が多すぎるというﾒｯｾｰｼﾞがでたので､ｴｸｾﾙｼｰﾄにSQL貼り付けて､そこから取得するようにしましたが

●質問者: FujiiRock
●ｶﾃｺﾞﾘ:ｺﾝﾋﾟｭｰﾀ
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

▽1 ● だわかき
●10ﾎﾟｲﾝﾄ

SQLite ODBC Driverが高速化されていないためです｡

場合によってはSQLite to Excelを使った方が早いかもしれません｡

SQLite to Excel
http://www.vector.co.jp/soft/winnt/business/se481602.html

▽2 ● cx20
●100ﾎﾟｲﾝﾄ

ﾍﾞｽﾄｱﾝｻｰ

ADO は CursorLocation / CursorType / LockType の3つのﾌﾟﾛﾊﾟﾃｨの組み合わせによって挙動が変わってきます｡
ﾃﾞｰﾀﾍﾞｰｽｴﾝｼﾞﾝやﾄﾞﾗｲﾊﾞによって､ｻﾎﾟｰﾄされていないｵﾌﾟｼｮﾝがあったりする為､推奨する設定を提示することができないのですが､組み合わせを変えて試してみてはいかがでしょうか｡

以下､参考情報です｡

＜参考情報＞
■ Recordset ｵﾌﾞｼﾞｪｸﾄの基本
http://msdn.microsoft.com/ja-jp/library/cc376797.aspx

■ ADO ｶｰｿﾙを極める
http://www.shoeisha.com/mag/windev/pdf/870507/windev0507_156_SQLServer.pdf

■ ADO 時代の非接続型ﾃﾞｰﾀｱｸｾｽ - とあるｺﾝｻﾙﾀﾝﾄのつぶやき - Site Home - MSDN Blogs
http://blogs.msdn.com/b/nakama/archive/2008/10/16/ado.aspx

また､｢行継続文字( _) を使いすぎています｡｣というｴﾗｰが出る場合は､
以下のようにｺｰﾄﾞを修正してみてください｡

' ｽﾃｰﾄﾒﾝﾄを｢_｣により連結したｹｰｽ(連結数に制限がある)
Dim strSQL
strSQL = "SELECT ..." & _
 "..." & _
 "..." & _
 "..." & _
 "..." & _
 "..."

' ｽﾃｰﾄﾒﾝﾄを複数行に分割したｹｰｽ(連結数に制限が無い)
Dim strSQL
strSQL = "SELECT ..."
strSQL = strSQL & "..."
strSQL = strSQL & "..."
strSQL = strSQL & "..."
strSQL = strSQL & "..."
strSQL = strSQL & "..."

cx20さんのｺﾒﾝﾄ
以下の環境にて ADO を用いた SELECT 時間(ﾃﾞｰﾀ件数:500万件)を計測してみました｡ |*環境|*ﾊﾞｰｼﾞｮﾝ| |OS|Windows 7| |SQLite|SQLite version 3.8.0.1| |Driver|SQLite3 ODBC Driver version 0.994.00.00| ﾃｰﾌﾞﾙ定義 |*No|*列名|*型|*ｷｰ| |1|field1|INTEGER|PRIMARY KEY| |2|field2|TEXT| | 実行SQL >|sql| SELECT * FROM tb_test; ||< ﾃｽﾄ結果 |*No|*CursorLocation|*CursorType|*LockType|*時間(秒)| |1|Server|ForwardOnly|ReadOnly|10| |2|Server|Keyset |ReadOnly|10| |3|Server|Dynamic |ReadOnly|10| |4|Server|Static |ReadOnly|10| |5|Client|ForwardOnly|ReadOnly|49| |6|Client|Keyset |ReadOnly|50| |7|Client|Dynamic |ReadOnly|50| |8|Client|Static |ReadOnly|50| SQL が簡単な為か､とりあえず､数分かかるということはありませんでした｡ CursorLocation = adUseClient としたときの処理が遅いのは､500万件をｸﾗｲｱﾝﾄｶｰｿﾙとしてﾒﾓﾘ上にﾛｰﾄﾞしている為と考えられます｡もし､CursorLocation に adUseClient を利用されているのであれば､adUseServer に変更してみてください｡また､TkSQLite が早い理由ですが､ADO に比べて､中間層が少ないのが起因しているのではないかと思います｡ >|| ＜ADO 経由の DB 接続ｲﾒｰｼﾞ＞ [ADO] | [Microsoft OLE DB Provider for ODBC] | [SQLite3 ODBC Driver] | [SQLite3] ||<

cx20さんのｺﾒﾝﾄ
参考までに計測に使用したｽｸﾘﾌﾟﾄ(VBScript)を添付します｡ >|vb| ' File : SQLiteTest.vbs ' Usage : CScript //Nologo : SQLiteTest.vbs [Enter] Option Explicit Const DB_PATH = "C:\SQLite\db_test_5m" ' CursorLocationEnum Const adUseNone = 1 Const adUseServer = 2 ' 既定 Const adUseClient = 3 ' CursorTypeEnum Const adOpenForwardOnly = 0 ' 既定 Const adOpenKeyset = 1 Const adOpenDynamic = 2 Const adOpenStatic = 3 Const adOpenUnspecified = -1 ' LockTypeEnum Const adLockReadOnly = 1 Const adLockPessimistic = 2 Const adLockOptimistic = 3 Const adLockBatchOptimistic = 4 Const adLockUnspecified = -1 ' DataTypeEnum Const adInteger = 3 Const adVarChar = 200 Const adFldIsNullable = 32 Dim g_rsTestCase Call Main() Sub Main() InitTestCase Dim rs Set rs = g_rsTestCase rs.MoveFirst While Not rs.BOF And Not rs.EOF ConnectTestByTestCaseRecordset rs rs.MoveNext Wend End Sub Sub InitTestCase() Set g_rsTestCase = CreateTestCaseRecordset() AddTestCase g_rsTestCase, 1, adUseServer, adOpenForwardOnly, adLockReadOnly, "Server,ForwardOnly,ReadOnly" AddTestCase g_rsTestCase, 2, adUseServer, adOpenKeyset, adLockReadOnly, "Server,Keyset ,ReadOnly" AddTestCase g_rsTestCase, 3, adUseServer, adOpenDynamic, adLockReadOnly, "Server,Dynamic ,ReadOnly" AddTestCase g_rsTestCase, 4, adUseServer, adOpenStatic, adLockReadOnly, "Server,Static ,ReadOnly" AddTestCase g_rsTestCase, 5, adUseClient, adOpenForwardOnly, adLockReadOnly, "Client,ForwardOnly,ReadOnly" AddTestCase g_rsTestCase, 6, adUseClient, adOpenKeyset, adLockReadOnly, "Client,Keyset ,ReadOnly" AddTestCase g_rsTestCase, 7, adUseClient, adOpenDynamic, adLockReadOnly, "Client,Dynamic ,ReadOnly" AddTestCase g_rsTestCase, 8, adUseClient, adOpenStatic, adLockReadOnly, "Client,Static ,ReadOnly" End Sub Function CreateTestCaseRecordset() Dim rs Set rs = CreateObject("ADODB.Recordset") rs.Fields.Append "CaseNo", adInteger rs.Fields.Append "CursorLocationEnum", adInteger rs.Fields.Append "CursorTypeEnum", adInteger rs.Fields.Append "LockTypeEnum", adInteger rs.Fields.Append "CaseName", adVarChar, 255, adFldIsNullable rs.Open Set CreateTestCaseRecordset = rs End Function Function AddTestCase( ByRef rs, nCaseNo, nCursorLocation, nCursorType, nLockType, strCaseName ) rs.AddNew rs("CaseNo") = nCaseNo rs("CursorLocationEnum") = nCursorLocation rs("CursorTypeEnum") = nCursorType rs("LockTypeEnum") = nLockType rs("CaseName") = strCaseName rs.Update End Function Sub ConnectTestByTestCaseRecordset( rs ) Dim nCaseNo Dim nCursorLocation Dim nCursorType Dim nLockType Dim strCaseName nCaseNo = rs("CaseNo") nCursorLocation = rs("CursorLocationEnum") nCursorType = rs("CursorTypeEnum") nLockType = rs("LockTypeEnum") strCaseName = rs("CaseName") Call ConnectTestByTestCase( nCaseNo, nCursorLocation, nCursorType, nLockType, strCaseName ) End Sub Sub ConnectTestByTestCase( nCaseNo, nCursorLocation, nCursorType, nLockType, strCaseName ) Dim cn Set cn = CreateObject("ADODB.Connection") cn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" & DB_PATH cn.Open cn.CursorLocation = nCursorLocation Dim strSQL strSQL = "SELECT * FROM tb_test;" Dim dtStart ' 開始時間 Dim dtEnd ' 終了時間 Dim dtDiff ' 処理時間 Dim rs Set rs = CreateObject("ADODB.Recordset") dtStart = Now ' recordset.Open Source, ActiveConnection, CursorType, LockType, option rs.Open strSQL, cn, nCursorType, nLockType dtEnd = Now dtDiff = DateDiff("s", dtStart, dtEnd ) ' 計測結果表示 WScript.Echo nCaseNo & "," & strCaseName & "," & dtDiff End Sub ||<

FujiiRockさんのｺﾒﾝﾄ
おおｰ(^^) ありがとうございますっ！おかげさまで､ｶｰｿﾙを変えたら改善されました！また､深いところも教えていただきありがとうございます｡ｻﾝﾌﾟﾙｺｰﾄﾞも読みやすくて勉強になりました?

cx20さんのｺﾒﾝﾄ
蛇足ですが､ADO のﾚｺｰﾄﾞｾｯﾄの内容を Excel のｼｰﾄに転記したい場合､｢CopyFromRecordset｣ﾒｿｯﾄﾞが高速なのでお勧めです｡ >|vb| ActiveSheet.Range("A1").CopyFromRecordset rs ||< >> ＜参考情報＞ ■ ｵｰﾄﾒｰｼｮﾝを使用して ADO ﾚｺｰﾄﾞｾｯﾄのﾃﾞｰﾀを Excel に転送する方法 http://support.microsoft.com/kb/246335/ja <<