Recordsetをエクセルに貼り付ける処理を高速化したい(VBA)

Question

実現したいこと

VBAを使用し、DBからデータを取得して、エクセルに貼り付けたいです。
RecordSetからエクセルに貼り付ける処理に時間がかかりすぎて困っています。

どのようにしたら高速化できますか。

前提

DB : SQLserver
データ数：15列3000行程度
今の処理時間：90秒
目標の処理時間：10秒

SQL文の処理時間は問題ないです。
再計算や描画はオフにしてます。

試したこと

Dim SQL As String
Dim rst As Recordset

Set rst = DB.getRecordSet(SQL)
Call Range("A1").CopyFromRecordset(rst)

Public Function getRecordSet( _
                    OpenSQL As String, _
                    Optional CursorType As ADODB.CursorTypeEnum = ADODB.CursorTypeEnum.adOpenKeyset, _
                    Optional LockType As ADODB.LockTypeEnum = ADODB.LockTypeEnum.adLockReadOnly _
                ) As ADODB.Recordset
        Set getRecordSet = New ADODB.Recordset
        getRecordSet.Open OpenSQL, conn, CursorType, LockType
End Function

Comments

[hatena19]

「RecordSetからエクセルに貼り付ける処理に時間がかかりすぎ」
「SQL文の処理時間は問題ないです。」という根拠は?
15列3000行程度でCopyFromRecordsetにそんなに時間がかかるとは思えないです。

Set rst = DB.getRecordSet(SQL)
と
Call Range("A1").CopyFromRecordset(rst)
のそれぞれの処理時間は計測しましたか。

計測方法は下記を参考に。
https://www.limecode.jp/entry/speed/timer-measure-processtime

[shiki_968]

回答ありがとうございます。計測すると
Set rst = DB.getRecordSet(SQL)　　　　　　　0.5秒
Call Range("A1").CopyFromRecordset(rst)　　 72秒

という結果になりました。

[hatena19]

データのシートへの貼り付け自体はそれほど時間がかかるとは考えにくいです。

RecordSet.Open だけでは、まだレコードセットにデータを読み込んでいません。
CopyFromRecordset するときにレコードセットにデータを読み込んで、貼り付けている推測されます。
そのレコードセットにデータを読み込むのに時間がかかっているのかな、という気がします
SQLではなく、15列3000行程度のテーブルを作成してそれを読み込んで貼り付けた場合の時間も計測してみてください。

[shiki_968]

計測しました。
RecordSet　→　テーブル　0.1秒
テーブル　→　RecordSet　0.6秒
ご指摘どおり、データの貼り付け自体は時間はかかりません。

[hatena19]

SQL、テーブルの設計に原因があるということになりますね。
その設計を見直しましょう。　

[shiki_968]

SQL自体はDB管理ツールで問題ないことを確認しています。
他を調べた結果、adOpenKeysetが原因でした。

adOpenKeysetを使うと、キーがレコード内を移動するために処理が遅くなることがわかりました。
今回はテーブルをそのまま貼り付けるだけなので、adOpenKeysetは不要なため不使用にしたところ、高速化が実現できました。

ご相談にのっていただきありがとうございました。

Answer 1

adOpenKeysetを使うと、レコード内を移動できるようになるが、VBAでエクセルに貼り付けると時間がかかる。
adOpenForwardOnlyを使うと高速に貼り付けが可能になる。
↓参考サイト
http://www7b.biglobe.ne.jp/~cbcnet/ADO/recordset.html

Comments

[hatena19]

どのくらい改善できたのでしょうか。
90秒から目標の10秒以内に短縮できたということでしょうか。
adOpenForwardOnlyが高速なのはわかりますが、それほど違いがあるとは。

[shiki_968]

全体としては4秒、問題にしていたrecordsetの処理は1秒になりました。