【SQLserver】大量のデータを爆速にinsertする方法

こんにちは、管理人のPaZooです。

性能調査など負荷試験を行う時に、SQLServerのテーブルに大量のデータをinsertするケースがありましたのでその時に調べた内容を記事にしました。

データを大量に作成するとなると時間がかかって大変でしたが、とても良い経験になりましたのでお役に立てれば幸いです。

テスト環境を整える

まずは実行する環境を整えます。

【実行環境】SQL Server2019 Express(64bit)

サンプルデータベースの作成

テスト用にまずはデータベースを作成します。データベースの作成は下記の記事が分かりやすかったので、ぜひ参考にされてください。

sql-oracle.com

私はサンプルデータベースとして、「mstDB」を作成しました。

サンプルテーブルの作成

では次に、サンプルデータを格納するためにサンプルテーブルの作成を行います。

USE [mstDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [usrDB].[test_usertbl](
	[id] [int] NOT NULL,
	[Name] [nchar](15) NOT NULL,
	[RegDate] [date] NOT NULL,
 CONSTRAINT [PK_usertbl] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ループを使ってレコードを作成するクエリ

まずは単純にループするだけのクエリを作ってみました。

    SET NOCOUNT ON 
        DECLARE @insertNumber INT
        SET @insertNumber = 0
        WHILE @insertNumber < 1000000
        BEGIN
                INSERT INTO [mstDB].[usrDB].[test_usertbl] (
					[id]
				  ,[Name]
				 ,[RegDate]
				 )
                VALUES
                        (@insertNumber , 
     'test'
    ,  GETDATE())
                SET @insertNumber = @insertNumber + 1
        END

 ループ処理でデータをinsertした結果、なんと5分もかかりました…。

100万件で5分なので、もし1000万件のレコードを作成するならば単純に考えても50分はかかります。

さらにカラム数が多い場合やデータ件数が増えてしまうと、より性能面で不安を感じますね…。こんなクエリは検証環境でも実行したくありません。。。

再帰SQLで大量のテストデータをinsertする

今回、再帰SQLを使ってinsertしてみました。

再帰SQLとは、前に行った処理の結果を利用し同じ処理を繰り返す場合に使われます。

今回はwith句を使っています。with句とは、メインクエリの中で同じクエリを何度も呼び出す場合に使用されます。

下記のクエリを管理人の環境で実行したところ、20秒ほどで100万件のデータをinsertできました！

USE [mstDB]
GO

DECLARE @insertNumber Bigint    -- INSERTする行数
SELECT @insertNumber=1000000;  -- 100万に設定


WITH Base AS
  (
    SELECT
      1 AS n

    UNION ALL
    
    SELECT 
      n + 1
    FROM
      Base
    WHERE
      n < @insertNumber
  ),
  Nums AS
  (
     SELECT
       Row_Number() OVER(ORDER BY n) AS n
     FROM
       Base
  )
  
  INSERT INTO [mstDB].[usrDB].[test_usertbl]
  SELECT
      n
    , 'test'
    ,  GETDATE()
  FROM
    Nums
  WHERE 
    n <= @insertNumber

OPTION (MaxRecursion 0); -- 再帰SQLの再帰回数の上限を無制限にする

ちなみに再帰SQLでは、再帰回数を指定していない場合に無限ループを考慮し、再帰可能上限値の規定値が100となっています。

ですので、上限最大値を変更するためには「MaxRecursion」を指定してください。

MaxRecursionで設定できる値は、「0」から「32,767」までとなっており「0」は上限なしです。

今回は100万件のレコードを作成するために、インクリメントさせてます。

まとめ

100万件のデータを扱うとなると性能面を最も気にしなきゃいけないので、今回調べた再帰クエリはとても勉強になりました！

データを更新するときにも使えるので、大量のデータを扱う際はぜひ再帰SQLを使ってみてはいかがでしょうか？