SQLiteで大量の外部結合

Question

前提・実現したいこと

【SQL構成】

テーブル名:TableA
列A:code1
列B:code2
列C:code3
列D:code4
...（code20まで続いてます）

テーブル名:TableB
列A:code
列B:name
（2列のみです）

TableAの各要素をTableBと外部結合したいのですが、
20個も外部結合するのが普通なのか・他に効率のいい呼び出し方があるのかをご教授願いたいです。

発生している問題・エラーメッセージ

エラーは出てないですが、処理が遅くなるのが目に見えているので
対処法をご教授願いたいです。

該当のソースコード

現在は、

LEFT JOIN TableB AS No1 ON TableA.code1 = No1.code
LEFT JOIN TableB AS No2 ON TableA.code1 = No2.code
LEFT JOIN TableB AS No3 ON TableA.code1 = No3.code

...とやってます。

試したこと

希望の出力は出来ています。
ただ、もっと効率のいい書き方を知りたいです。

宜しくお願い致します。

Answer 1

どの程度の速度が欲しいのかにもよりますが、
そもそもTableB.codeにインデックスが貼られている場合、
何十回外部結合で参照しようがそこまで遅くはなりません。

しかし、このSQLを短時間で何度も発行するようなら、変数に入れる設計も検討した方が良いかもしれませんね。
まず最初にTableBの中身を全て取り出して、連想配列やハッシュマップに変換して変数に格納。（言語により実装はまちまち）

TableAのCodeからTableBのNameに変換したいなーと思った時だけ、
ハッシュの変数にアクセスして取り出せばよくて、TableBの中身が凄まじく重いとかで無い限り早いでしょうね。

Comments

[退会済みユーザー]

回答ありがとうございます。
拝見させていただきました。

外部結合を何十回しようがそこまで遅くならないのは初めて知りました。
では、コードは長くなるが処理にあまり影響は無いという事ですね。

もし、TableA自体にCodeとNameを両方入れた場合処理ははやくなりますか。
Tableをわけて参照する癖があったのでこうしましたが、実際TableA自体も外部結合で取ってくるので
まとめた方がはやいよという事であればまとめたいと思います。

宜しくお願い致します。

[miyabi-sun]

一概に速くなるとは言えません。
名前とコードをまとめたらデータが重複するわけですよね？
コードを捨てて名前で管理するなら兎も角、データ量が増えることでその分何処かが低速化するはずです。

実際の所、本物のデータを見ながら試行錯誤してみて、その経験が蓄積されてなんとなくこっちの方が速いんじゃね？とわかる話なので、解答欄でアドバイス出来る領域から大きくハズレてしまっています。
データは機密情報なのでおいそれと公開出来ないかと思いますので、色々なケースを自分で作って試してみてください。
またプロの方に秘密保持契約を結んだ上で見てもらうという手段もあります。
データの管理・アクセスのノウハウにはやはりそれだけの価値があるということですね……

Answer 2

逆によく正規化されてるなと思うぐらいですね。
遅くなるケースと考えられるのは非常にバカでかいテーブルで結合してる場合でしょう
その場合予めサブクエリで対象テーブルを絞り込む方法もあります。（これはDBエンジンにより逆効果になる場合が有るので検証が必要です）

LEFT JOIN TableB AS No1 ON TableA.code1 = No1.code
LEFT JOIN (select * from table c where c.age > =20 ) AS No2 ON TableA.code1 = No2.code

ただ、SQLiteでパフォーマンスを期待するのはナンセンスです。
性能問題があるならばMysqlなど別なDBへ切り替えるべきです。

Comments

[退会済みユーザー]

回答ありがとうございます。
拝見させていただきました。

LEF JOIN内でWHEREが使えるのは初めて知りました。
ありがとうございます。
仕様上SQLiteが望ましく、MySQL等はすみませんが無しでお願い致します。

下記返答にも記載していますがもし、TableA自体にCodeとNameを両方入れた場合処理ははやくなりますか。
Tableをわけて参照する癖があったのでこうしましたが、実際TableA自体も外部結合で取ってくるので
まとめた方がはやいよという事であればまとめたいと思います。

宜しくお願い致します。

[namda]

miyabi-sunさんの回答どおりINDEXさえ張ってれば極端な性能劣化はありません。
今回のSQLでnameだけTableAに移すのであれはほとんど人間に感知できないレベルで多少高速化するとおもいますが
ただこれは実際のDB構成で負荷検証しなければ絶対と言えません。

リレーショナル・データベース的に正規化することは好ましいことです。
その正規化方法は別途専門書があるような知識体系なので詳しくは割愛しますが
tableAがたとえば最終退室者テーブルでnameを置くのはDBが肥大化しさらに他のSQLが複雑化し性能が落ちる可能性があります。
そこはバランスですので繰り返しになりますが負荷検証してどちらが早いか検証比較していく他ありません。