SQL ServerでOPENQUERYを使い、ストアドから結果セットを取得する

1時間以上かかっていたクエリが1秒程度になりました。

実現したいこと

• とあるBIツールでデータをインポートするクエリが遅いのを改善したい。
  • SELECT * FROM <VIEW> としているだけだが、VIEW内で集計や結合などをWITH句を駆使して実現している。
  • BIツール側では複雑なクエリを記述できないので、遅いVIEWを何とかしたい。
  • テーブル側は毎日洗い替えされるテーブルのため、インデックス追加は難しい。
  • VIEWのSQLに結合ヒントを設定するなどである程度は改善されるが、十分ではない。

改善案として考えたこと

• VIEWをSPに置き換える。
• SP内でローカル一時テーブルを作成、クラスター化インデックスを追加して結合条件や集計の速度を向上する。
• BIツール側のインポートクエリでOPENQUERY関数を使い、SPから結果セットを取得する。
  • OPENQUERY関数はリンクサーバでSQLを実行し、結果セットを返す関数。
  • BIツール側インポートクエリ
    • SELECT * FROM OPENQUERY(<LinkedServerName>, 'EXEC <sp_name>')

実現可能かの検証

手順

1. SQLServerに自分自身をリンクサーバとして登録する。
2. 簡単なSPを作成し、結果セットを返す。
3. BIツール側でインポートクエリを記述する。
4. インポートを実行する。

結果

• 期待通りの結果が得られることを確認。

プロダクトコードにした際に発生した問題と解決策

問題

• ローカル一時テーブルをSP内で使用している場合、OPENQUERY関数がエラーとなる。
  • OPENQUERY関数を呼び出すと、sys.sp_describe_first_result_setが呼び出されてSPの返す結果セットのメタデータを取得しているらしく、ローカル一時テーブルを使っているとエラーとなってしまう。

解決策

• OPENQUERY関数内でEXECする際に、WITH RESULT SETSで結果セットの型を明示する。
  • EXEC <sp_name> WITH RESULT SETS ( ([ID] INT NOT NULL, [NAME] NVARCHAR(100) NULL) )
• これで問題が解消することを確認できた。

メンテナンス性を向上するための工夫

• BIツールでは、同データを何度もインポートするため、複数個所での記述が必要となる。項目追加や修正をBIツール側で複数個所修正するのはメンテナンス上望ましくない・・・
• ユーザー定義テーブル型を作成し、`WITH RESULT SETSに指定することで記述を1か所にまとめられるのではないか？
  • 実現できる。ただし、リンクサーバの設定時にDB名を指定する必要があるので注意が必要。