5
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2019-06-30

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名を指定する必要があるので注意が必要。
5
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
5
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?