SQL Server の計算列に助けられたが、実行速度が気になったので検証

2020/04/18

経緯

SharePoint の 1 行テキストにある「コード列」というのがあり、オンプレのシステムにバッチで同期を取っているシステムがあったとします。

納品日に言われました。
「コード列の並び順は数字順にしてください」

俺「そのフィールドは 255 文字の 1 行テキストなので、数値型以外が入る可能性があります。」
お客様「んー それは並ばなくていいから、数字のだけ並べてください」

(´；ω；｀)ｳｯ…

今更、同期バッチに手を入れる訳にもいかなかったので、SQL Server の計算列を作り、ORDER BY 句だけ変更する事で逃げ切れました。

その時に気になったのは、次の 3 パターンで実行速度がどの程度変わったのだろう？ということ。

  • ORDER BY 句で CAST する
  • 計算列の値を物理的に保存しない仮想列で作る
  • 計算列の値を物理的に保存する PRESISTED を付ける

そもそも計算列とは？

Docs には次の説明がありました。

計算列の式は、他の列のデータを使用して値を計算し、それを自身の列に格納します。

検証環境

検証で使ったのは SQL Server 2017 (64bit)で、検証用テーブルは次の通り。

CREATE TABLE [dbo].[dummy](
    [CODE] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

検証用データとして次のデータを作成しています。

  • CODE 列に、TEST00 ～ TEST09 を登録
  • CODE 列に、000010 ～ 999999 をランダムに登録

検証用計算列をつけます。

ALTER TABLE [dbo].[dummy] ADD [INTCODE] AS TRY_CAST(CODE AS int);
ALTER TABLE [dbo].[dummy] ADD [INTCODE_PERSISTED] AS TRY_CAST(CODE AS int) PERSISTED;

PERSISTED を付けると物理的にデータを保存します。
かかった時間は、テストデータが入っている状態で 3 秒ほどでした。

検証

SQL Server Management Studio で実行

計測で使った SQL 文

DECLARE @StartTime datetime
DECLARE @EndTime datetime

-- キャッシュのクリア
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE 

SELECT @StartTime=GETDATE()

-- ここに検証する SQL 文

SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]

検証する SQL 文

  • ORDER BY する時に CAST してソート

    SELECT [CODE] FROM [test].[dbo].[dummy] ORDER BY TRY_CAST(CODE AS int);

  • 計算列の値を物理的に保存しない仮想列でソート

    SELECT [CODE] FROM [test].[dbo].[dummy] ORDER BY [INTCODE];

  • 計算列の値を物理的に保存する PRESISTED をつけた列でソート

    SELECT [CODE] FROM [test].[dbo].[dummy] ORDER BY [INTCODE_PERSISTED];

検証結果

検証内容 かかった時間(ミリ秒)
ORDER BY する時に CAST してソート 7606
計算列の値を物理的に保存しない仮想列でソート 7624
計算列の値を物理的に保存する PRESISTED をつけた列でソート 7300

計算列にはインデックスも張れるようなので、インデックスもつけて再度検証してみる

  • 作成したインデックス

    CREATE NONCLUSTERED INDEX IX_INTCODE ON [dbo].[dummy]([INTCODE]);
CREATE NONCLUSTERED INDEX IX_INTCODE_PERSISTED ON [dbo].[dummy]([INTCODE_PERSISTED]);

  • インデックスをつけて検証

検証内容 かかった時間(ミリ秒)
計算列の値を物理的に保存しない仮想列でソート 7424
計算列の値を物理的に保存する PRESISTED をつけた列でソート 7316

考察

数字を見てどちらでも良かったんだなと安心。
今回は同期バッチを修正せず、プログラム側も最小限の変更に抑えたかったので、計算列を使って良かったと今でも思っている。

向いている処理としては、各種税の計算を計算列でやるとかはアリかもと思った。

参考

DatabaseSQLServer

Posted by みやみや