別のテーブルの情報を元にUPDATEしたい！（ただし重いので速くしたい）

https://teratail.com/questions/227186
状況は以前ご質問したこれと同じです。
tableAとtableBがIDで対応づいていて、tableBの内容をもとにtableAをupdateしたいです。
このときと同じような感じでupdateしてみました。
やりたいことはできましたが、完了に70分かかりました…。

tableAのレコード数：24万行

tableBのレコード数：29万行

更新対象のレコード数：900行
更新対象のカラム数：6カラム

カラム6つあるのでこういう感じのsql文を書きました。

sql
1update tableA set
2answer1 = (select answer1 from tableB where A_ID=tableA.ID),
3answer2 = (select answer2 from tableB where A_ID=tableA.ID),
4answer3 = (select answer3 from tableB where A_ID=tableA.ID),
5answer4 = (select answer4 from tableB where A_ID=tableA.ID),
6answer5 = (select answer5 from tableB where A_ID=tableA.ID),
7answer6 = (select answer6 from tableB where A_ID=tableA.ID)
8where exists(
9select answer1 from tableB where is_reliable=1 and A_ID=tableA.ID
10-- tableAのあるカラムが空欄であるレコードが対象であることがわかっているので絞ってみた。
11-- 対象のレコード数が24万行から4万行ぐらいになった。
12and tableA.somecolumn = '';
13);

同じようなサブクエリをたくさん書いているのがスマートじゃないような気がしています。

coalesceを使ったsql文を先に書いてみましたが、そちらは30分待っても動かなかったのでキャンセルしてしまいました
（もう少し待ってみてもよかった……）。

もう少し早く動かせる方法はありそうでしょうか？
こういう場合どこが一番ネックになっていますか？

sqlite 3.24.0

m.ts10806

2020/01/13 05:40

SQLiteって実行計画とれましたっけ

FKM

2020/01/13 06:31 編集

もはやsqliteで処理していい行数ではないような気もする…。ここまで物量があったらupdate文を作るより、一時ファイル作って、バルクインサートでローテーションした方が速いでしょうね。

行動規範の内容に同意します

回答1件

ベストアンサー

元を見ましたがサブクエリを使っているものをupdateするのは非常に時間を要します。それに物量も万行単位なので、待避用のテーブルを作って、そこに一旦作成。その後、元のテーブル内データを削除(truncate)してから、移し替えるという手段が速いでしょう。

調べたらSQLiteでもバルクインサートが使えるようなので。

##行程

テーブルAと全く同じテーブルＡDASHを作る。
テーブルＢから取得したデータをテーブルＡDASHにバルクインサートで挿入
テーブルＡを空にする。もし、残したいデータも存在する場合は該当条件のものだけをidなどで紐付け、delete文で削除する。
テーブルＡDASHの値をまるまるテーブルＡに複写する。
テーブルＡDASHを空にする

##補足:バルクインサートとは
insert into テーブル名 values(...);
を
insert into テーブル名 values(...),(...),n;
という風につなげられる挿入処理（バルクとは船荷のこと）です。船荷のようにまとめて投入できるので、実行処理が一度で済むため、長い行数だと処理は格段に速くなります。

###テーブルの複写
また、複写の手段ですが

insert into テーブルA select * from テーブルADASH;

という方法があります。全く同じ構造にしておくのがポイントで、この待避テーブルを作成するやり方はけっこうデータ処理の常套手段です。

あと、削除といっても、間違ってもdelete文を使わないでください。泣きを見ます。それから、試す前に必ず、ロールバックできるようにバックアップをとっておくようにしてください。

まあ、更に速い手段でCSVエクスポート&インポートで突っ込むという手もありますが。

投稿2020/01/13 06:41

編集2020/01/13 06:53

FKM

総合スコア3633

hacosato

2020/01/13 10:22

ありがとうございます！行程のところが理解しきれておらず、確認させてください。 1.テーブルAと全く同じテーブルＡDASHを作る。 2.テーブルＢから取得したデータをテーブルＡDASHにバルクインサートで挿入 3.テーブルＡを空にする。もし、残したいデータも存在する場合は該当条件のものだけをidなどで紐付け、delete文で削除する。 4.テーブルＡDASHの値をまるまるテーブルＡに複写する。 5.テーブルＡDASHを空にするテーブルAのレコード（約）240000行あるうち、今回のupdate対象は900行で、残りの239100行は変更なしです。行程2の時点では、テーブルADASHは240900行になっている理解でいいでしょうか？（updateではなくてinsertを使うでOKですか？）これの状態のテーブルADASHを、行程4のとおり「まるまるテーブルAに複写」すると、レコード数が増えてしまうと思うのですが、その分はどこで帳尻を合わせるのでしょうか？勘違いしているところがあれば、ご指摘いただけますでしょうか。

FKM

2020/01/13 12:26 編集

それならＡDASHは900個でいいですね。そして紐付いたidの差分を元データから削除し、一時ファイルに入れていた増分を後で足します。

hacosato

2020/01/13 12:35

なるほど！ありがとうございました。 http://fanblogs.jp/scripts/archive/11/0 このページを見ていたんですが、updateに時間がかかるのでinsertでやりくりすると理解しました。 4.の行程はunionでいいでしょうか？ > あと、削除といっても、間違ってもdelete文を使わないでください。この「削除」というのは、5.の行程のことでしょうか？ 5.は古いテーブルまるごと不要になると思ったのでdropでいいかと思ったんですがおかしいでしょうか？なんとなく見えてきました！

FKM

2020/01/20 00:18 編集

delete文を使うなというのはもし、データを刷新したい場合deleteを使うと、データを空っぽにするのではなくて、受け皿のテーブル情報ごと消してしまう危険があるからです。条件付きで消すのは問題ありません。 dropはデータベース、テーブル構造ごと削除するものです。一度きりならdeleteで消してください何度も使う機会があるならtruncate tableでデータだけ空っぽにしておくといいでしょう。 4の工程は書いている通りで、 insert into 追加させたいテーブル select * from 退避したテーブル; という記述が使えます。

hacosato

2020/01/13 12:48

あっ勘違いしていました…ありがとうございます。そして全体像がわかったと思います。試しにやってみます！！

行動規範の内容に同意します