先日、大阪で開催された db tech showcase 2014 の [B24] Oracle から SQL Server システム移行の勘所 でも話にあったようですが、ユーザー定義関数を使用する場合に気を付けておきたいことをメモとして。
以下のようなクエリを実行したとします。
SET NOCOUNT ON DBCC SETCPUWEIGHT(1000) DBCC FREEPROCCACHE GO DECLARE @tmp nvarchar(1000) SELECT @tmp = UPPER(Col2) FROM UDFTest WHERE Col2 LIKE 'sql server 2%' ORDER BY Col1 SELECT @tmp GO DECLARE @tmp nvarchar(1000) SELECT @tmp = dbo.udf_UPPER(Col2) FROM UDFTest WHERE Col2 LIKE 'sql server 2%' ORDER BY Col1 SELECT @tmp GO DECLARE @tmp nvarchar(1000) SELECT @tmp = dbo.clr_UPPER(Col2) FROM UDFTest WHERE Col2 LIKE 'sql server 2%' ORDER BY Col1 SELECT @tmp GO
一つ目のクエリは組み込み関数の文字列関数である UPPER を実行。
二つ目のクエリはユーザー定義関数で UPPER を実行。
三つめのクエリは SQL CLR で UPPER を実行。
となっています。
ユーザー定義関数の中身は
ALTER FUNCTION [dbo].[udf_UPPER] (@p1 nvarchar(1000)) RETURNS nvarchar(1000) AS BEGIN RETURN UPPER(@p1) END
SQL CLR の中身は
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefineCLR
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString clr_Upper(String param1)
{
// コードをここに記述してください
return new SqlString (param1.ToUpper());
}
}
となっており、UPPER による文字列操作のみを実施しています。
実行結果についてはすべて同じとなっています。
この際の実行プランを確認してみます。
すべてのクエリで実行プランが変わっています。
また、組み込み関数と SQL CLR では、並列クエリとして実行されていますが、ユーザー定義関数では並列クエリとして実行されていないことが確認できます。
以下は、並列クエリ処理 の記載となります。
次のいずれかの条件が満たされている場合、SQL Server のクエリ オプティマイザーは、クエリに対して並列実行プランを使用しません。
代替策として並列実行プランの使用を考えるほど、クエリの直列実行コストが高くない。
特定のクエリに対して可能な並列実行プランより、直列実行プランの方が速いと考えられる。
クエリに、並列では実行できないスカラー演算子または関係演算子が含まれる。演算子によっては、クエリ プランのセクションまたはプラン全体が直列モードで実行される場合があります。
他にも
– T-SQL スカラー関数
– テーブル変数の変更
– システムテーブルへのアクセス
– TOP 句の利用
– 2012 以前の Windows 関数の利用
なども影響することがあったかと思いますが、並列クエリが実行されないケースがいくつかあります。
先ほどの、クエリを以下のように変更してみます。
SET NOCOUNT ON DBCC SETCPUWEIGHT(1000) DBCC FREEPROCCACHE GO DECLARE @tmp nvarchar(1000) DECLARE @tmp2 nvarchar(1000) SELECT @tmp = UPPER(Col2),@tmp2 = UPPER(col2) FROM UDFTest WHERE Col2 LIKE 'sql server 2%' ORDER BY Col1 GO DECLARE @tmp nvarchar(1000) DECLARE @tmp2 nvarchar(1000) SELECT @tmp = UPPER(Col2),@tmp2 = dbo.udf_UPPER(col2) FROM UDFTest WHERE Col2 LIKE 'sql server 2%' ORDER BY Col1 GO DECLARE @tmp nvarchar(1000) DECLARE @tmp2 nvarchar(1000) SELECT @tmp = UPPER(Col2),@tmp2 = dbo.clr_UPPER(col2) FROM UDFTest WHERE Col2 LIKE 'sql server 2%' ORDER BY Col1 GO
この時の実行プランが以下になります。
ユーザー定義関数を使用した場合は並列クエリになっていないことが確認できます。
SQL CLR については CLR (共通言語ランタイム) 統合によるデータベース オブジェクトの構築 や SQL CLR Functions and parallelelism に記載されているように並列化を行うことができます。
効果的であるとクエリ オプティマイザーで判断された場合は、共通言語ランタイムでの CLR オブジェクト (ユーザー定義関数、ユーザー定義型、またはトリガー) の実行を複数のスレッドで行うことができます (並列プラン)。 ただし、ユーザー定義関数がデータにアクセスする場合は、直列プランで実行されます。 SQL Server 2008 よりも前のサーバー バージョンで実行したときに、ユーザー定義関数に LOB パラメーターが含まれていたり、ユーザー定義関数から値が返される場合も、直列プランで実行する必要があります。
組み込み関数で実行できないような処理をする場合、ユーザー定義関数を使用することがあるかと思いますが、その際には並列クエリとして実行されない可能性がありそうです。
関数で実行する必要のある処理を SQL Server で実行するか、アプリケーション側で実行するかはどのようなプランが使用されるかを意識して気を付けておいたほうがよさそうですね。