おはようございます。佐久間です。
「Reporting ServicesとBing MapsでWEB上のHTMLデータを可視化するレポートを作成する」
前回まででWeb上のHTMLからデータを抽出し、レポートで使用する形に変換するところまで完了しました。
第5回目はBing Mapsと連携した地図レポートの作成に取り掛かります。
（目次はこちら）
3-1-1. データソースの設定とチューニング ← 今回
3-1-2. レポートの作成
3-2. シェープファイルへのマッピング
参考. Windows FormsやASP.NetからSSRSのレポートを利用する
参考-2. ASP.NetからSSRSのレポートを利用する
3. データを表示するレポートを作成する：Reporting Services
今回は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 |
| 実際の行数 | 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 |
| 実際の行数 | 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と連携させるレポートのデザインを行います。
