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>')
実現可能かの検証
手順
- SQLServerに自分自身をリンクサーバとして登録する。
- 簡単なSPを作成し、結果セットを返す。
- BIツール側でインポートクエリを記述する。
- インポートを実行する。
結果
- 期待通りの結果が得られることを確認。
プロダクトコードにした際に発生した問題と解決策
問題
- ローカル一時テーブルをSP内で使用している場合、OPENQUERY関数がエラーとなる。
- OPENQUERY関数を呼び出すと、
sys.sp_describe_first_result_set
が呼び出されてSPの返す結果セットのメタデータを取得しているらしく、ローカル一時テーブルを使っているとエラーとなってしまう。
- OPENQUERY関数を呼び出すと、
解決策
- 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名を指定する必要があるので注意が必要。