Accessにおける複合主キーのWhere in

Question

Access2007で作成しているmdb(Access 2000データベース)を、Excel2013から、VBA(ActiveX Data Objects 6.0 ADODB)を使ってSQLで操作しています。
ここで次の2フィールドからなる複合キーを主キーとするテーブル「案件情報」から、この複合キーを使ってWhere inで値を取り出したいです。

工事番号
登録時刻

当初、次を試したのですが、Accessでは対応していないらしく、困っています。

select * 案件情報 where (工事番号,登録時刻) in (('値1','値2','値3'),('時刻1','時刻2','時刻3'))

自分で考えた方法1.
テーブル「案件情報」の設計を変えることは可能です（サロゲートキーにすることはできます）
ただし、I/F側（Excel側）にサロゲートキーの情報を保持することは、難しいです。
結局Excel側の2つの情報からAccess側のサロゲートキーを特定する方法が思いつかず、諦めました。

自分で考えた方法2.

select * 案件情報 where 工事番号+登録時刻 in ('値1時刻1','値2時刻2','値3時刻3')

これは、とりあえず所望の動作をするのですが、
主キーを演算してしまうとインデックスの恩恵を受けられないと思い、あまり好ましくなく思っています。
実際に恩恵がないのか（全検索になっているのか）については、調べる方法が解らなかったため、調べていないです。

Q1. 2つの情報を使って、インデックスの恩恵を受けられる形で、AccessでWhere inを実行、もしくは同じ結果を得る方法はありますか。
Q2. インデックスの恩恵を受けているか簡単に調べる方法はありますか。

どうぞよろしくお願いします。

---

案件情報は次のようなものです

工事番号	登録時刻	客先名	納期	品名	受注数量
aaaa	2019/01/01 10:00	○○株式会社	2019/02/01	ねじ	1
aaaa	2019/01/02 10:00	○○株式会社	2019/02/03	ねじ	1
aaaa	2019/01/03 10:00	○○株式会社	2019/03/01	ねじ	3
bbbb	2019/01/01 10:00	○○株式会社	2019/02/01	ナット	3
bbbb	2019/01/02 10:00	○○株式会社	2019/02/01	ナット	2
cccc	2019/01/02 10:00	□□株式会社	2019/02/01	ねじ	3

同じ工事番号で複数の登録時刻がある場合、最新の行のみ有効な値で、残りはログを残すためだけのデータです。

Answer 1

select * from (
select * from t where  a in ? 
) where b in ?

なイメージだったはずだけど？

Comments

[退会済みユーザー]

ふとおもったけど、2つのwhere アンドでつなげても同じだった気がするな

[tokita.]

回答ありがとうございます。ただ、この方法だと
工事番号 aaaa 登録日1/1
工事番号 aaaa 登録日1/2
工事番号 bbbb 登録日1/1
工事番号 bbbb 登録日1/2

というデータがあるときに、aaaaの1/1とbbbbの1/2がほしいという指定をしてしまうと
全部出てきてしまうので、使えないです。

Answer 2

環境がすぐ用意できないので試せていませんが、exists は使えないでしょうか？

select
    *
from
    案件情報
where
    exists (
        select
            *
        from (
            select '値1' as 工事番号,'時刻1' as 登録時刻 union all
            select '値2' as 工事番号,'時刻2' as 登録時刻 union all
            select '値3' as 工事番号,'時刻3' as 登録時刻
        ) as tmp
        where
            案件情報.工事番号 = tmp.工事番号
        and 案件情報.登録時刻 = tmp.登録時刻
    )

Comments

[tokita.]

回答ありがとうございます。
すごく面白かったです。

そのままだと動かなかったので
https://www.accessdbstudy.net/entry/20081116/p1
を参考に関係のない1行のテーブルをFROMに追加し、
日付を##で囲むようにしたところ動き、所望の結果が得られました。

by scanningでした。
残念。

Answer 3

Q1. 2つの情報を使って、インデックスの恩恵を受けられる形で、AccessでWhere inを実行、もしくは同じ結果を得る方法はありますか。

() IN () のような形式はAccessはサポートしませんので、エクセルのデータとjoinですね。
直接join出来ないのであれば、専用のテーブルを作りそこにデータを流し込んで、joinして使う。

Q2. インデックスの恩恵を受けているか簡単に調べる方法はありますか。

Accessにも実行計画を出力する機能はあります。ただあまり役立つような内容ではありません。
レジストリ操作が必要ですし、戻し忘れるとどんどんログが溜まってしまいますので注意が必要です。

MS Access の実行計画を参照する
ACCESS(MDB)の実行計画を見る方法

Comments

[tokita.]

ご回答ありがとうございます。

>エクセルのデータとjoin
この文がいまいち理解できなかったのですが、sqlで配列情報をサブクエリの結果のような形で渡す方法があるというとでしょうか？
もう少し詳細に情報頂けると助かります。

実行計画の情報提供ありがとうございます。

[sazi]

案件情報のレイアウトはどのようなものですか？
条件が列方向に展開されているなら、unionクエリーで行方向に展開して、それとjoinします。

[sazi]

案件情報からunionで抽出する形のクエリーを条件でも良いですが、性能を気にされているのであれば、一旦テーブルにデータを落とした方がインデックスも使えるので良いでしょうね。

Answer 4

Q1. 2つの情報を使って、インデックスの恩恵を受けられる形で、AccessでWhere inを実行、もしくは同じ結果を得る方法はありますか。

AccessではInで複数キーは無理ですので、下記のようにするしかないかと。

Where (工事番号="値1" AND 登録時刻="時刻1") OR (工事番号="値2" AND 登録時刻="時刻2") OR (工事番号="値3" AND 登録時刻="時刻3")

あるいは、抽出条件用のテーブルを作成して結合するのが条件の設定が楽になると思います。

Access側に下記のようなテーブルを作成しておいて、

T_Key (工事番号 , 登録時刻)

これにExcelのキーデータを出力して、

SELECT T_Data.*
FROM
T_Data INNER JOIN T_Key
 ON T_Data.工事番号=T_Key.工事番号 AND T_Data.登録時刻=T_Key.登録時刻;

Q2. インデックスの恩恵を受けているか簡単に調べる方法はありますか。

AccessでSQLの実行計画を取得する方法 | アイビースター

ACCESS(MDB)の実行計画を見る方法

Comments

[tokita.]

回答ありがとうございます。
たしかに、andとorをたくさん使えば望みの結果が得られますね。

>抽出条件用のテーブルを作成して結合

これのイメージがつかないのですが、もう少し具体的にご教示願えますでしょうか。
（結局、そのテーブルに対して2キーでのwhere inに該当する処理が必要になる方法しか、思い浮かばず…）

実行計画の御教示ありがとうございます。試してみます。

[hatena19]

> これのイメージがつかないのですが、もう少し具体的にご教示願えますでしょうか。

回答に追記しました。

[tokita.]

追記ありがとうございます。
AccessにExcelのデータを仮置きするための作業用テーブルを設けるということですね。
Accessはデータを保管するためのものというイメージがありこの発想には至りませんでした。
検討に加えてみます。

[tokita.]

showplan(実行計画)について
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug\JETSHOWPLAN
をONにしたのちAccess再起動したところ記録されるようになりました。（当方環境Win7 Access2007)
これを使って検討を進めさせていただきます。

[tokita.]

皆様から頂いた方法をすべて試した結果、
hatena19様から最初に頂いたandとorでつなぐ方法がusing rushmoreで実行されており
もっともシンプルかつ早いと期待できるため、使用させて頂くこととなりました。

ありがとうございました。

[sazi]

後から追加された案件情報のレイアウトなら普通にjoinすればいいだけでしょうに。

[tokita.]

saziさん、追加コメントありがとうございます。
私のレベルが低く、サンプルコードなしだと仰る意味がうまく理解できていないのですが、

検索したい情報を一度Accessのテーブル（たとえばExcel情報というテーブルを作っておく）に保存してから
select a.* from Excel情報 as e left join 案件情報 as a on e.工事番号=a.工事番号 and e.登録時刻=a.登録時刻

と実行する、という意味でしょうか？

実際に実行速度を計れるほどのデータがまだなく、試していないのですが、
一旦Accessにデータを保存する、という処理に時間がかかるかと思ったのですが、
ORを大量に使うのも望ましくないらしく、どちらがよいのか決めかねています。

[sazi]

案件情報はリンクテーブルの状態ですか？
リンクテーブルでは無くインポートしてから、インデックス付与してjoinした方が経験からは高速です。
インデックス付きのテーブルにインポートの方が分かり易いと思いますが。
Joinに関しては、そのSQLのイメージです。

[tokita.]

案件情報はAccessにベタ打ちされているテーブルです
Access標準のデータのリンク・インポートといった操作は使っていません
（私のPCは開発のためにAccessが入っていますが、実際に使用するPCにAccessは導入されていません）

よって仰っていることは
・Accessにワーキングテーブル（例：Excel情報）を用意
・使用前にdelete * Excel情報　でデータクリア
・insert でExcelの情報をAccessに書き込み
・JoinするSQLを実行して結果吐き出し
という形で実装することとなります

Accessに一度値を入れるにはHDDへの書き込みが発生するから遅くなるかと思ったのですが
何分私は経験不足なもので、saziさんの経験を頼ってみようと思います。

ところで1つ疑問なのですが、Joinする方法は単キーの場合でも使えると思いますが、
その場合、Where inを使用するのと、Joinでは、どちらのほうが(速度の観点で)好ましいのでしょうか？

[sazi]

whereで条件指定するという事は明示的な結合順序はありませんので、Accessのオプチマイザー次第です。
そこまで検証した事はないので、正しくテーブル設計が出来ていて、結合順序もリレーションを意識した記述を行うなら、joinの方が効率良い気がします。
そういった点や、可読性の面も含め、joinをお薦めします。

[hatena19]

エクセルのVBAで捜査しているのですよね。
推測ですが、 Excel側のキー条件数かそれほど多くなければ、OR で連結した条件で抽出するのが早いとと思います。件数が多くなるなら、AccessのワークテーブルにエクスポートしてJoinするのがよさそうです。
件数の分岐点は実データで試してみることになると思います。

[tokita.]

saziさま　hatena19さま　ありがとうございます。

以下のように理解しました。
Joinの方法→HDD書き込みの負荷がある、件数が増えても速度はほとんど落ちない
OR(where in)の方法→ループ処理がかかるので件数が増えるほど遅くなる。件数が増えるとSQLがどんどん複雑になる

まだデータのほうが出そろっておらず、評価できない状態ですが
いつでも切り替えられる実装にしておこうと思います。

[hatena19]

あくまで推測ですので実際はどうなのかは実験して確かめるしかないと思います。
あと、SQL文の長さの制限かあったと思いますので、それを超える場合は、エクスポートしてJoinしかないですね。