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貼り付けて、そこから取得するようにしましたが

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/08/31 14:21:15
  • 終了:2013/09/01 00:58:37

ベストアンサー

id:cx20 No.2

cx20回答回数603ベストアンサー獲得回数1072013/08/31 16:15:26

ポイント100pt

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 & "..."
他2件のコメントを見る
id:yayayai

おおー(^^)
ありがとうございますっ!
おかげさまで、カーソルを変えたら改善されました!
また、深いところも教えていただきありがとうございます。
サンプルコードも読みやすくて勉強になりました~

2013/09/01 00:58:11
id:cx20

蛇足ですが、ADO のレコードセットの内容を Excel のシートに転記したい場合、「CopyFromRecordset」メソッドが高速なのでお勧めです。

ActiveSheet.Range("A1").CopyFromRecordset rs

<参考情報>
■ オートメーションを使用して ADO レコードセットのデータを Excel に転送する方法
http://support.microsoft.com/kb/246335/ja

2013/09/01 11:36:43

その他の回答(1件)

id:dawakaki No.1

だわかき回答回数797ベストアンサー獲得回数1222013/08/31 15:59:29

ポイント10pt

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

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

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

id:cx20 No.2

cx20回答回数603ベストアンサー獲得回数1072013/08/31 16:15:26ここでベストアンサー

ポイント100pt

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 & "..."
他2件のコメントを見る
id:yayayai

おおー(^^)
ありがとうございますっ!
おかげさまで、カーソルを変えたら改善されました!
また、深いところも教えていただきありがとうございます。
サンプルコードも読みやすくて勉強になりました~

2013/09/01 00:58:11
id:cx20

蛇足ですが、ADO のレコードセットの内容を Excel のシートに転記したい場合、「CopyFromRecordset」メソッドが高速なのでお勧めです。

ActiveSheet.Range("A1").CopyFromRecordset rs

<参考情報>
■ オートメーションを使用して ADO レコードセットのデータを Excel に転送する方法
http://support.microsoft.com/kb/246335/ja

2013/09/01 11:36:43

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません