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のプロジェクトにレポートサーバープロジェクトを追加しましょう。

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

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

新しいプロジェクトを追加

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

レポートサーバープロジェクト

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

ソリューションエクスプローラーに追加されたプロジェクト

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

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

共有データソースの追加
共有データソースのプロパティ設定

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

次はレポートに表示させるデータを取得するためのストアドを作成します。
地震データを中心において都道府県マスタと震度マスタを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には座標データが含まれているので実行すると空間結果を見ることができます。
震源の座標のみがプロットされた空間結果なのですが・・・
日本の地図とどう重なるのかイメージできてしまう程に地震が発生していたことがわかります。

空間結果

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

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

実際の実行プランを含める
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

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

チューニング前の実際の実行プラン
・地震データの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]

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

インデックス作成後の実行プラン
・地震データの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と連携させるレポートのデザインを行います。


ネクストスケープ企業サイトへ

NEXTSCAPE

検索する

タグ

メタデータ

投稿のRSS

著者

データベースの中のデータはその企業の財産のはず。
しかし現実にはユーザー自身が自由に見れないことが多い。

データを未来のために活かせる状態にすることを求めるエンジニアです。
「sakuma」さんの全ポストを読む