SQLSERVERからデータを取得する方法

質問

SQLSERVERに商品テーブルを作り
ACCESSにローカルテーブル(下記の例では入力テーブル）を
作りました。

ACCESS　VBAで
ACCESSのローカルテーブルを1件ずつ読み
SQLSERVERの商品テーブルを検索したいのですが
下記の例　どちらが処理スピードが速いのですか？
なお下記以外にも処理スピードが速い方法が
あったら教えてください。
●例１
Dim db As DATABASE
Dim rs As Recordset
　　dim rt as Recordset
Set db = CurrentDb
Set rt = db.OpenRecordset("入力テーブル", dbOpenDynaset)
sql = "SELECT 商品名 FROM 商品テーブル "
sql = sql & "where 商品ID = '" & rt![商品ID] & "'"
Set rs = db.OpenRecordset(sql)
If rs.RecordCount = 0 Then
MsgBox "NG"
Else
MsgBox rs![商品名]
End If
●例２
Dim db As DATABASE
Dim rs As Recordset
　　dim rt as Recordset
Set db = CurrentDb
Set rt = db.OpenRecordset("入力テーブル", dbOpenDynaset)
Set rs = db.OpenRecordset("商品テーブル", dbOpenDynaset)
rs.FindFirst "商品CD = '" & rt![商品CD] & "'"
If rs.NoMatch Then
MsgBox "NG"
Else
MsgBox rs![商品名]
End If

よろしくお願いします。

回答No.3

venzou · ベストアンサー

>なお例１、例２　結局のところ
>どちらが高速なのですか？
>どちらが一般的なのですか？

一般論として・・・

SQL文で処理出来る内容は、VBAよりもSQL文の方が高速です。

VBAでのループ処理は結構重いので、速度を優先するなら、SQL文で処理出来るように、システムを設計して下さい。

例１、例２の比較についてですが、ケースバイケースだと思います。例では１レコードしか処理していませんが、実際は入力テーブルのレコード数分のループ処理になると思います。入力テーブルのレコード数や、商品テーブルのレコード数、PCの性能、サーバとクライアントの通信速度など、影響してきます。一概にどちらが良いとは言えないと思います。

例１は、頻繁にレコードセットを開く点でマイナスですが、商品テーブルの該当レコードのみ参照しているので、通信量は少なくなるかも。入力テーブルのレコード数が少ない場合は有利かも。（かなり、推測です）

例２は、レコードセットを開く回数は少ないですが、商品テーブル全体を参照しているので、通信量が多くなるかも。商品テーブルのレコード数が少ない場合は有利かも。（これも推測です）

処理の重さと、通信量の重さで、実際どちらが時間が掛かるかは、環境に依存します。

SQLServerのリンクテーブルが絡んでくるので、Accessの内部処理、ODBCドライバの処理、SQLServerの処理の詳細を理解していないと分からないと思います。

私もそれほど詳しいわけではないので、判断できません。

ちなみに、個人的には、例１と例２だと、例２の方が良いと思います。理由は、レコードセットを頻繁に開くのは無駄が多いと思うからです。

実際にどちらが速いか知りたいなら、実際の環境で、サンプルデータを作り、比較テストをして下さい。

（今回の件では、SQL文を使う方法をお勧めしますが・・・）

補足

ネットで探しても見つからないし
困っていました。
実際に色々試して見ます。
ご意見ありがとうございました。

回答No.2

venzou

＞実際にやりたいことは、　・・・以下省略

SQL文で処理出来るので、その方が高速です。

　strSQL = _
　　"UPDATE 商品テーブル INNER JOIN 入力テーブル " & _
　　"ON 商品テーブル.商品ID = 入力テーブル.商品ID " & _
　　"SET 商品テーブル.商品名 = 入力テーブル.商品名;"

　CurrentDb.Execute strSQL

更新したいフィールドを、SET以降に、 ,　区切りで並べて下さい。
詳しくは、UPDATE文について調べてみて下さい。

入力テーブルで、商品コードが一致しないもののリストは下記で調べる事が出来ます。

SELECT 入力テーブル.*
FROM 入力テーブル LEFT JOIN 商品テーブル
ON 入力テーブル.商品ID = 商品テーブル.商品ID
WHERE 商品テーブル.商品ID Is Null;

補足

サンプルプログラムも
教えていただきありがとうございました。
非常に助かります。

なお例１、例２　結局のところ
どちらが高速なのですか？
どちらが一般的なのですか？
どちらも結果は同じですが
使い分けが良くわかりません。

知っていれば教えてください。
よろしくお願いします。

回答No.1

venzou

検証はしていませんが、予めSQL文で、二つのテーブルを結合した方が速いと思います。

例１の方法だと、入力テーブル１レコード毎に、レコードセットを開き直すので、一番重い処理になると思います。

例２の方法でも、入力テーブル１レコード毎に、商品テーブルを検索する事になります。

予めテーブルを結合しておけば、検索の処理はデータベースエンジンが、全レコードまとめて処理するので、VBAでレコード毎に処理するより高速になると思います。

あと、レコードセットを更新する予定が無ければ、dbOpenForwardOnly で開く方が軽いです。

商品IDのフィールドに、インデックスを設定するのも忘れずに。

上記は推測です。環境やレコード数などによっても処理速度が変わってくると思うので、正確な情報が必要なら、実際の環境で比較テストをして下さい。

以下、サンプル
-------------------------------------------------------------
Private Sub test()
　Dim rs As DAO.Recordset
　Dim strSQL As String

　strSQL = _
　"SELECT 入力テーブル.*, 商品テーブル.商品ID AS 有無 " & _
　"FROM 入力テーブル LEFT JOIN 商品テーブル " & _
　"ON 入力テーブル.商品ID = 商品テーブル.商品ID;"

　Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
　Do Until rs.EOF
　　If IsNull(rs![有無]) Then
　　　MsgBox "NG"
　　Else
　　　MsgBox rs![商品名]
　　End If
　　rs.MoveNext
　Loop
End Sub

補足

ありがとうございます。
実際にやりたいことは、
入力テーブルに入れた商品コードと
商品テーブルの商品コードが一致した場合に限り
入力テーブルの値を
商品テーブルにセットし
商品テーブルを更新したいと思っています。

そのためdbOpenForwardOnly は
使用できません。

サンプルがプログラムが
あったら教えてください。
よろしくお願いします。