NEXTSCAPE blog

株式会社ネクストスケープの社員による会社公式ブログです。ネスケラボでは、社員が日頃どのようなことに興味をもっているのか、仕事を通してどのような面白いことに取り組んでいるのかなど、会社や技術に関する情報をマイペースに紹介しています。

MENU

Reporting ServicesでBing Mapsと連携したレポートを作成する – データソースの設定とチューニング [SSRS with Bing Maps #5]

おはようございます。佐久間です。

「Reporting ServicesとBing MapsでWEB上のHTMLデータを可視化するレポートを作成する」

前回まででWeb上のHTMLからデータを抽出し、レポートで使用する形に変換するところまで完了しました。
第5回目はBing Mapsと連携した地図レポートの作成に取り掛かります。
(目次はこちら)

3. データを表示するレポートを作成する:Reporting Services
3-1. Bingとの連携
 3-1-1. データソースの設定とチューニング ← 今回
 3-1-2. レポートの作成

3-2. シェープファイルへのマッピング

参考. Windows FormsやASP.NetからSSRSのレポートを利用する

参考-1. Windows FormsからSSRSのレポートを利用する
参考-2. ASP.NetからSSRSのレポートを利用する

 
3. データを表示するレポートを作成する:Reporting Services

3-1. Bingとの連携
3-1-1. データソースの設定とチューニング

今回はReporting Servicesのレポートを作成します。
まずはBIDS上でこれまで使ってきたSSISのプロジェクトにレポートサーバープロジェクトを追加しましょう。

– レポートサーバープロジェクトの作成 –

・新しいプロジェクトを追加します。

f:id:nextscape_blog:20210908170345p:plain

・「レポートサーバープロジェクト」を選択。

f:id:nextscape_blog:20210908170405p:plain

・ソリューションエクスプローラーにレポートサーバープロジェクトが追加されます。

f:id:nextscape_blog:20210908170441p:plain

– 共有データソースの作成 –

レポートサーバープロジェクトを追加したらレポートに表示するデータの取得先にあたる共有データソースを作成します。
ソリューションエクスプローラー中の共有データソースを右クリックして「共有データソースの追加」を選択してください。
その後表示されるダイアログに従ってデータを用意したDBへの接続文字列を設定すれば共有データソースの作成は完了します。

f:id:nextscape_blog:20210908170502p:plain

f:id:nextscape_blog:20210908170537p:plain

– データ取得用のストアドを作成する –

次はレポートに表示させるデータを取得するためのストアドを作成します。
地震データを中心において都道府県マスタと震度マスタをJOINさせるだけのとてもシンプルなSQLにしました。

CREATE PROCEDURE dbo.GetMapData
AS
BEGIN
  SELECT
    data.地震データID
    , data.日時
    , data.座標
    , data.震源
    , data.都道府県ID
    , r.都道府県名
    , data.深さ
    , data.マグニチュード
    , data.最大震度ID
    , s.震度
  FROM dbo.地震データ data
  INNER JOIN dbo.都道府県マスタ r
  ON data.都道府県ID = r.都道府県ID
  INNER JOIN dbo.震度マスタ s
  ON data.最大震度ID = s.震度ID
END

上記のSQLには座標データが含まれているので実行すると空間結果を見ることができます。
震源の座標のみがプロットされた空間結果なのですが・・・
日本の地図とどう重なるのかイメージできてしまう程に地震が発生していたことがわかります。

f:id:nextscape_blog:20210908170609p:plain

– ストアドのチューニング –

続いて上記のストアドのチューニングを行います。
チューニングといっても今回はインデックスの作成程度です。
ManagementStudioの「実際の実行プランを含める」オプションを有効にしてストアドのSQL部分のみを切り出して実行し、実行プランの計測を行います。

f:id:nextscape_blog:20210908170631p:plain

SELECT
  data.地震データID
  , data.日時
  , data.座標
  , data.震源
  , data.都道府県ID
  , r.都道府県名
  , data.深さ
  , data.マグニチュード
  , data.最大震度ID
  , s.震度
FROM dbo.地震データ data
INNER JOIN dbo.都道府県マスタ r
ON data.都道府県ID = r.都道府県ID
INNER JOIN dbo.震度マスタ s
ON data.最大震度ID = s.震度ID

・チューニング前の実際の実行プラン

f:id:nextscape_blog:20210908170705p:plain

・地震データのClustered Index Scan
実際の行数 2341
実行回数 1
操作の推定コスト 0.0243756
・震度マスタのClustered Index Seek
実際の行数 2341
実行回数 2341
操作の推定コスト 0.0187349
・都道府県マスタのClustered Index Seek
実際の行数 2341
実行回数 2341
操作の推定コスト 0.0032831
・SELECT
サブツリーの推定コスト 0.052148

実行プランを見ると地震データテーブルのところにClustered Index Scan、震度・都道府県マスタのところにClustered Index Seekと記述されています。
これは地震データテーブルの全2341件分のリーフをすべてスキャンして1件ずつデータを取得し、
震度マスタのクラスタ化インデックスを2341回、都道府県マスタのクラスタ化インデックスを2341回シークすることを表しています。

割合として地震データテーブルのScanのコストが高いので、これをやめさせるために震度マスタとのJOINで使用している最大震度IDにインデックスを作成してみましょう。

CREATE NONCLUSTERED INDEX IX_震度 ON dbo.地震データ
(
  最大震度ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

・チューニング後の実際の実行プラン

f:id:nextscape_blog:20210908170801p:plain

・地震データのIndex Seek (NonClustered)
実際の行数 2341
実行回数 1
操作の推定コスト 0.0048641
・震度マスタのClustered Index Scan
実際の行数 11
実行回数 1
操作の推定コスト 0.0032941
・都道府県マスタのClustered Index Seek
実際の行数 2341
実行回数 2341
操作の推定コスト 0.0032831
・SELECT
サブツリーの推定コスト 0.0147787

インデックスの作成により震度マスタがClustered Index Scan (Clustered)されるように変わりました。
これはわずか11件しかない小さなテーブルなのでSeekするよりも全件スキャンしてしまった方が効率的なためです。
件数が少ないので左のNested Loopsに向けて伸びる矢印も細くなっています。
対する地震データはというとインデックスを使用するようになったためInex Seek (NonClustered)に変化しており、コストも1桁分下がっているので有効であったと言えます。
SELECTのサブツリーの推定コストも0.052148から0.0147787(28%)へ下がったのでまずまずといったところでしょうか。

また、都道府県マスタとのJOINに使用する都道府県IDにインデックスを作成しても実行プラン上効果は現れませんが、Where句に都道府県IDによる絞り込みを追加する場合には作成しておくことをおすすめします。

CREATE NONCLUSTERED INDEX IX_都道府県 ON dbo.地震データ
(
  都道府県ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

インデックスの作成が完了したら以前TODO状態で作成したストアドDisableIndexesに無効化処理を追加します。

ALTER PROCEDURE dbo.DisableIndexes
AS
BEGIN
  SET NOCOUNT ON;
  ALTER INDEX IX_震度 ON dbo.地震データ DISABLE
  ALTER INDEX IX_都道府県 ON dbo.地震データ DISABLE
END

以上で、データソースの作成とチューニングは完了です。
次回はBingMapsと連携させるレポートのデザインを行います。