none
スカラー関数をSQLに使用すると処理が遅くなるのは何故 RRS feed

  • 質問


  • こんにちは。
    スカラー関数をSQL内で使用するか検討しております。
    スカラー関数をSQLに組み込んだところ、
    処理時間がとても遅くなりました。

    そのSQLは以下となります。

    ------------------------------***

    Exec('
    Insert Into Table_CCC
    Select
      a.col1 as DDD
    , a.col2 as EEE
    , DB1.dbo.GetData1(b.col1) as FFF                      ← スカラー関数
    From
    (
    Select *
    From Table_AAA
    ) a
    Inner Join
    Table_BBB b
    On a.ID = b.ID
    Group By
      a.col1
    , a.col2
    , DB1.dbo.GetData1(b.col1)                             ← スカラー関数
    ');

    ------------------------------***

    また、スカラー関数は以下のように定義しました。
    ------------------------------***
    USE [DB1]
    GO
    Create function [dbo].[GetData1](@hoge int)
    returns int
    as
    begin

    declare @i int

    set @i = Case When @hoge = 100 Then 1
                  When @hoge = 200 Then 2
                  When @hoge = 300 Then 3
                  Else 0 End

    return @i

    end

    ------------------------------***

    スカラー関数(ユーザ定義関数)をSQLに使用するのは、
    あまり良くないのでしょうか?

    SQLに使用すると遅くなるのは、
    何故か教えて頂けますでしょうか?

    何卒、よろしくお願いいたします。

    2015年3月9日 6:48

回答

  • SQLでスカラー関数を使うことが良くないとなると、スカラー関数の存在意味がほとんど無くなってしまうと思います。
    見た限り、それほど負荷がかからない関数だと思いますが、他のところで遅くなっているということはないでしょうか?
    とりあえず、可能であれば実行プランを確認してみてください。


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2015年3月9日 7:50
  • ユーザー定義関数を使用する場合に気を付けておきたいことによると並列クエリが実行できなくなる場合があるそうです。何にせよ、クエリプランを確認することで原因がわかります。
    2015年3月9日 7:55
  • From 
    (
    Select  *
    From Table_AAA
    ) a

    単に
    From Table_AAA a
    でよいのでは?


    group by より distinct がスッキリするかな。

    Select distinct
      a.col1 as DDD
    , a.col2 as EEE
    , DB1.dbo.GetData1(b.col1) as FFF 
    From Table_AAA a
    Inner Join
    Table_BBB b
    On a.ID = b.ID

    ---
    Select distinct
      a.col1 as DDD
    , a.col2 as EEE
    , b.FFF 
    From Table_AAA a
    Inner Join
    (SELECT distinct ID, DB1.dbo.GetData1(b.col1) AS FFF FROM Table_BBB) b
    On a.ID = b.ID
    2015年3月9日 8:23
  • 佐祐理さんも指摘されていますが、スカラー関数の挙動はSELECT結果から"1件ずつ"FUNCTIONを呼ぶイメージのはず・・・

    ※単純なFUNCTIONであれば、SELECT句内に展開した方が・・・

    個人的には、FUNCTIONの内容をビュー化するのを検討します


    CREATE VIEW VIEW_GGG AS
    SELECT 100 AS COL1, 1 AS COL2
     UNION
    SELECT 200 AS COL1, 2 AS COL2
     UNION
    SELECT 300 AS COL1, 3 AS COL2
    GO
    

    とかでビュー定義しておいて

    INSERT INTO TABLE_CCC
    SELECT DISTINCT
           A.COL1 AS DDD
         , A.COL2 AS EEE
         , ISNULL(G.COL2, 0) AS FFF
      FROM TABLE_AAA A
     INNER JOIN TABLE_BBB B
        ON A.ID = B.ID
      LEFT OUTER JOIN VIEW_GGG G
        ON B.COL1 = D.COL1
    みたいな・・・

    ※スカラー関数を使うのが必須だとアレですが

    2015年3月10日 0:29

すべての返信

  • SQLでスカラー関数を使うことが良くないとなると、スカラー関数の存在意味がほとんど無くなってしまうと思います。
    見た限り、それほど負荷がかからない関数だと思いますが、他のところで遅くなっているということはないでしょうか?
    とりあえず、可能であれば実行プランを確認してみてください。


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2015年3月9日 7:50
  • ユーザー定義関数を使用する場合に気を付けておきたいことによると並列クエリが実行できなくなる場合があるそうです。何にせよ、クエリプランを確認することで原因がわかります。
    2015年3月9日 7:55
  • From 
    (
    Select  *
    From Table_AAA
    ) a

    単に
    From Table_AAA a
    でよいのでは?


    group by より distinct がスッキリするかな。

    Select distinct
      a.col1 as DDD
    , a.col2 as EEE
    , DB1.dbo.GetData1(b.col1) as FFF 
    From Table_AAA a
    Inner Join
    Table_BBB b
    On a.ID = b.ID

    ---
    Select distinct
      a.col1 as DDD
    , a.col2 as EEE
    , b.FFF 
    From Table_AAA a
    Inner Join
    (SELECT distinct ID, DB1.dbo.GetData1(b.col1) AS FFF FROM Table_BBB) b
    On a.ID = b.ID
    2015年3月9日 8:23
  • >trapemiya 様
    >佐祐理 様


    ご回答をありがとうございます!
    助かります。

    ではまず、
    実行プランを確認してみます。

    結果はまたアップいたします。


    >pang2

    ご回答をありがとうございます!

    SQLの内容に付きましても、
    再度確認してみます。

    2015年3月9日 9:04
  • 佐祐理さんも指摘されていますが、スカラー関数の挙動はSELECT結果から"1件ずつ"FUNCTIONを呼ぶイメージのはず・・・

    ※単純なFUNCTIONであれば、SELECT句内に展開した方が・・・

    個人的には、FUNCTIONの内容をビュー化するのを検討します


    CREATE VIEW VIEW_GGG AS
    SELECT 100 AS COL1, 1 AS COL2
     UNION
    SELECT 200 AS COL1, 2 AS COL2
     UNION
    SELECT 300 AS COL1, 3 AS COL2
    GO
    

    とかでビュー定義しておいて

    INSERT INTO TABLE_CCC
    SELECT DISTINCT
           A.COL1 AS DDD
         , A.COL2 AS EEE
         , ISNULL(G.COL2, 0) AS FFF
      FROM TABLE_AAA A
     INNER JOIN TABLE_BBB B
        ON A.ID = B.ID
      LEFT OUTER JOIN VIEW_GGG G
        ON B.COL1 = D.COL1
    みたいな・・・

    ※スカラー関数を使うのが必須だとアレですが

    2015年3月10日 0:29
  • >trapemiya 様
    >佐祐理 様

    実行プランを実施してみました。

    関数を使ったselectではHash Match のコストが上がっており、

    関数を使わないselectではHash Match のコストはあまり上がっておりませんでした。

    違いはこれだけでしたが、

    関数とHash Matchの関係が何か分かりませんでした。

    教えていただけますでしょうか?

    2015年3月10日 8:38
  • この記述だけではちょっと判断できません。実行プランが同一のように見えても、内容が異なっていたりしませんか?
    2015年3月12日 2:49
  • >佐祐理

    ご返信をありがとうございます。

    遅くなりまして、すみませんでした。

    あれから関数化について検討したところ、

    スカラー関数は並列で実施するとパラレルに実行されないことが分かりまして、

    (直列で実施されるため、複数実施すると時間がかかる)

    select文に使用することは無くなりました。

    皆様ありがとうございます。

    勉強になりました。

    2015年3月20日 8:21