SQL Serverのユーザファンクション(ユーザー定義テーブル値関数)を使用していて性能が出ない(遅い)場合にすること。
SQL Server上の開発したシステムでSQLの共通化(複雑な部分の隠蔽)にユーザファンクション(ユーザー定義テーブル値関数)を使用しました。
ユーザー定義テーブル値関数 | Microsoft Docs
ユーザファンションは大抵は計算式とかで使うと思いますが、SQL Serverではテーブルを返すこともできるのです。
ビューではパラメタを渡せませんが、ファンクションなのでパラメタを渡すことができて便利です。
共通化はうまく作用してSQLのスキルが低い開発者でも開発ができて非常に有用でした。
ところが、結合テストでデータ件数が10,000件を超えると途端に実行速度が遅くなりました。
件数が少ないときは数秒だったのに、実行しても全然返ってきません。
いろいろ調べるとユーザファンクションとテーブルのJOINでは特に問題が確認できませんでしたが、 ユーザファンクション同士をJOINしているところが問題で、 どうやらインデックスが効いていないっぽいことがわかりました。
対策として試しにユーザファンクションのところを、ユーザファンクション内で定義しているSQLに置き換えることで性能が改善しました。
ただ、ユーザファンクションで定義しているSQLを展開しただけなので、実行されるSQLとしては同じものになると思っていたのですが、若干違うみたいです。
まとめるとユーザファンクション(ユーザー定義テーブル値関数)を使用して性能が出ない場合は、ユーザファンクション同士をJOINなどしていないか確認し、していたらユーザファンクションで定義しているSQLをそのまま置き換えると性能が改善すると思います。