【SQL】WITH句で大量にコピーデータが作れてしかも早かった

2023.04.22

概要

諸事情でTypeScriptで十数万件のコピーデータを作っていたのですが、１時間半近くかかるので別の方法を探していました。
結果的にカーソルを使ったプロシージャよりもWITH句を使ったクエリのほうが早く処理が完了したのでうろたえていました。
データベースはSQL Serverです。

TypeScript：1時間30分前後
カーソル：20分ちょっと
WHITH句：10分ちょっと

という感じ。早いなー。

参考

SQL WITH句のサンプル | ITSakura
itsakura.com

内容

WITH句はサブクエリ（副問合せ）の一種だそうです。
WITH句でselect結果のViweのようなものを作っておき、その後のクエリで再利用できる便利なやつです。

今回は下記のようなテーブルがあって、AテーブルIDを使って、BテーブルのID001データを増やすというWITH句を書いてみます。

Aテーブル

|ID |NAME|
|---|----|
|001|a001|
|002|a002|
|003|a003|
|004|a004|
|005|a005|

Bテーブル

|ID |YEAR|MONTH|
|---|----|-----|
|001|2023|   01|

テストテーブルとベースになるデータを準備します。

-- Aテーブルを作成する
CREATE TABLE A (
  ID varchar(10) PRIMARY KEY,
  NAME varchar(50)
);

-- データを挿入する
INSERT INTO A (ID, NAME) VALUES ('001', 'a001');
INSERT INTO A (ID, NAME) VALUES ('002', 'a002');
INSERT INTO A (ID, NAME) VALUES ('003', 'a003');
INSERT INTO A (ID, NAME) VALUES ('004', 'a004');
INSERT INTO A (ID, NAME) VALUES ('005', 'a005');

-- Bテーブルを作成する
CREATE TABLE B (
  ID varchar(10) PRIMARY KEY,
  YEAR varchar(4),
  MONTH varchar(2)
);
INSERT INTO B (ID, YEAR, MONTH) VALUES ('001', '2023', '01');

WITH句を使用したデータ作成のSQLです。
今回はCROSS JOINを使って増やしてみます。

WITH B_Copies AS (
  SELECT
    A.ID AS ID
    , B.YEAR AS YEAR
    , B.MONTH AS MONTH
  FROM
    A
  CROSS JOIN B
)
INSERT INTO B (ID, YEAR, MONTH)
SELECT ID, YEAR, MONTH
FROM B_Copies
WHERE B_Copies.ID > '001'; --コピー元のデータはキー重複しているので除いておく

ダミーデータ実行後のBテーブルはこうなります。

|ID |YEAR|MONTH|
|---|----|-----|
|001|2023|   01|
|002|2023|   01|
|003|2023|   01|
|004|2023|   01|
|005|2023|   01|

B_Copies 内に指定したselect結果を持っておけるのが変数感あっていいですね。SQLの見通しもよくてわかりやすいです。
設定をコピーしたデータを大量作成したいときにも便利だけど、今までカーソル使っていたプロシージャ処理やサーバー側でforなりmapなりで回していた部分を修正するともっと処理が早くなりそうです。

おわり。

スポンサーリンク