SSRSレポートパフォーマンスの改善方法

背景

ハリコフのオフィスの「中間者」である私は、突然LinkedInに招待されてポーランドに移り、1つの大手銀行で働き始めました。 非常に熟考した後、私はこれが非常に有益な経験になることを期待して自分自身をやる気にさせて同意しました。 大企業で働くことは、プログラミングの経験だけでなく、履歴書のための2、3原子および3年間の有用なメモ、および+100のスーツを着ることです（方法は関係ありませんが、それ以外の場合）。 ポーランドのgovnokodのレベルはウクライナのレベルよりもはるかに高く、少なくともここで会った上級開発者の平均レベルは、ウクライナの同僚と比較できないことがわかりました。



それで、新しい仕事に就いて、3か月間の組織的なルーチンと私のためのプロジェクトの検索の後、私は自分用のツールを手に入れ、SSRSレポートを生成しました。 彼らが世界の主要な技術者からの原則とアドバイスについて高い鐘楼からここに置かれたと言うことは言うまでもない。 しかし、何かをする必要があります-だから誰も責任がありません。

与えられた：

.Netコンソールアプリケーション。

-標準のWindowsスケジューラに従って実行されます。

-ftpから2つのtxtファイルをダウンロードします。

-構造分析を行います。

-データを接続します。

-データベースへの詰め込み。

-結果として、100行（主に）列ごとに1つのテーブル。毎日約3,000エントリが追加され、ID IDENTITYさえありません。

SQL Server。データベースを保存します。

-たくさんの列と行がある健全なテーブルをクソする;

-正規化が終了しました。

-カップルview'yuhがあります。

-ユーザー、ログなどに関する情報のような付随するテーブルがあります。

-非常に重要な詳細-大きなテーブルには、同じ列を含む2種類のデータが格納されていましたが、互いに一致する必要がありました。 たとえば、買い手と購入する家の取引、通りの価格、その他すべてに関する情報は同じで、家と買い手を相互に関連付けることができるキーを構成するフィールドはごくわずかです。 これがレポートの主要なタスクでした。

SSRSの報告

-特定の日にビューからデータを選択するクエリを使用して、データベースに直接登ります。 日次レポートと月の両方を生成できます。 例：

SELECT a.col1, a.col2 .... a.col100 FROM vw_Data_With_User_Info WHERE a.TimeStamp >= @StartDate AND a.TimeStamp <=@EndDate
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

追加条件：

-UATへのアクセスはありません。

-1か月間のDEVデータベースデータテストでは、実際のデータへのアクセスはありません。

-プロファイラーに対する権利はありません。

挑戦する

-1日分のレポートは約3分ダウンロードされ、約40秒かかります。

-月次レポートは約1時間ダウンロードされるか、まったくロードされません-可能であれば修正してください。

ソリューション：

SSRSについてはこれまで読んだことがないので注意してください。SSRSがそれほどうまく機能しない理由をすでに知っている優れたプログラマもいるかもしれませんが、ソリューションを完成させるのに1か月かかりました。

試行1

最初の最も論理的な解決策は、レポートの列数を減らすことでした。 結局のところ、会社やユーザーごとにグループ化されていても、通常は100列から3000レコードまでのデータを表示することはできません。 顧客と話をして、最も必要なスピーカーを見つけました。そして、彼らの数を16に減らすことは完全に痛みがないことがわかりました。それで、Liteレポートが生まれました。

試行の結果1. Report_Lite for 1 day-40秒-1分20秒は、サーバーのムードと星の位置によって異なります。 1か月間、30分-1時間。 これで、顧客はしばらく結果に満足しました。

試行2

翌月、私は徐々にレポートの読み込み時間を短縮し、ビューとクエリの順序を整えようとしました。 たとえば、コンソールアプリケーションでのすべての計算、およびクライアントとハウスの相互マッチングのロジック。これは、作業速度の要件を誰もまだ提出していないためです。 その後、いくつかの列がテーブルに追加されました。その中にはMatchingKeyがあり、UATに関するレポートの速度は急激に低下しました。 また、今月中に、生成率がゆっくりと1分（平均）から1.30に低下したことにも注意してください。 その後、新しい列を追加するという事実に混乱しました。 私は、MatchingKeyがNULLになる前にテーブル内のレコードを探しました。これにより、クエリが自然に約100万レコードを反復し、一致するものが見つかりませんでした。 さらに、日付と時間の制限なしでビューが最初に実行され、その後、そこから時間データが選択されることに気付きました。 私の論理では、これによりレポート生成のパフォーマンスが大幅に向上し、ちなみにDEVサーバーで多少動作し、40秒で投資しましたが、UATには何の影響もありませんでした。



試みの結果2.ほとんど見えない。

試行3（成功および普遍的な認識）

2人の試みで上司を失望させた後、特定の指示を受けました。 すべてをドロップして、パフォーマンスのみを実行します。 最初のアイデアは、アプリケーションの完全な処理でした。 大きなテーブルをファイルとして2つに分割し、IDで3つに減らして、計算をすべて.netアップリッシュに完全に転送したいと考えました。 レポートからできるだけ多くのグループを削除し、一般に、マカロニの神が命じたとおりにすべてを行います。 これに1週間を費やしましたが、データベースにデータを保存するところまで来たら、テーブルとクエリの実験を開始し、ビューの選択、2つの別々のテーブルの結合によるクエリ実行の速度に実質的な違いはないことがわかりました。 少なくとも、レポートが1分間ロードされるわけではありません。 3000レコードの場合、SQLのクエリには3〜5秒かかりました。 そのため、クライアント側でデータをグループ化することがポイントです。 すべてのグループを削除し、並べ替えを行わなくてもクリーンなレポートを生成します-生成するのに数秒かかります。 SQLクエリ3〜5の場合、グループ化には数秒かかることがわかります。 そして、ほぼ一分はどこに行きますか？



問題は修辞的なものではなく、そのような実験は試行1の前に実行しなければなりませんでしたが、そのことになると実行されました。 実験を続けます。 レポートからのリクエストで遊び始めます。 その時までに、私はすでにプロファイラーへのアクセスを管理者に求めていました。 Profilerを開きましたが、プロファイラーの経験があまりないため、あまり注意深く見ていません。 そして、ある時点で、毎回@StartDateと@EndDateを入力するのにうんざりしていました。30分ごとに2つのカレンダーをクリックする必要があるのは面倒です。 したがって、グループ化されていないレポートでは、クエリジェネレーターに移動して、@ StartDateと@EndDateの代わりに「2014-06-11」を作成しました。 拍手、3秒で生成！ どうして？ キャッシュが存在し、どこでもキャッシュがクリアされている必要があります。 3秒 できません。 今グループ化-5秒。 はい、冗談です。

30分間カブをひっかいてプロファイラーを掘り続けた後、私はついに1か月間私を苦しめたもの、そして半年間この貧しいプロジェクトを見つけました。

結果

たとえば、レポートにクエリが含まれていることがわかりました。

 SELECT * FROM Table WHERE time >= @StartDate AND time <= @EndDate
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SQL側では、次のように実行されます。

 EXEC sp_executesql N'SELECT * FROM Table WHERE time >= @StartDate AND time <= @EndDate','@StartDate datetime, @EndDate datetime','2014-06-11','2014-06-11'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして今、そのようなリクエストは約1.30秒実行されます。 一方、クエリに値をすぐに代入したとき、実行されたのはDSQLではなく、通常のクエリでした。



その結果、クライアントにストアドプロシージャに切り替えるように説得することができました。現在、毎日のレポートは6秒間読み込まれ、毎月のレポートは1分です。

結論

• 1.急いで人をからかう
• 2.急いで、1か月失う
• 3.上司を怒らせて
• 4.特に新しい初心者のトピックでは、可能な解決策を期待するよりも、問題を理解することに時間を費やす方が良いでしょう。

PSこの記事は、問題を解決することではなく、問題を解決することがいかに間違っているかについての可能性が高いことを理解しています。 しかし、私はこれについて多くのことをグーグルで調べ、そのようなアドバイスや、レポートがそのような冗談を作る手がかりを見つけませんでした。



PS2そのような要求がなぜそれほど長くかかっているのかについていくつかの提案がありますが、正確な理由を知っている誰かがいれば非常に感謝します-そして私に説明します。



UPD1：この記事に関する苦情を考慮し、批判の原因となったすべての言葉を修正しようとしました。



UPD2：今日、既にストアドプロシージャに変換した新しいレポートで、私はまだ問題に遭遇しました。

 exec rOtex_Mis_DailyHighLevelReport @StartDate='2014-06-01 00:00:00',@EndDate='2014-06-30 00:00:00'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

レポートの生成中に6分かかり、手で1秒かかりました。 したがって、「SSRSストアドプロシージャが遅い」という特定の質問でGoogleにクロールしました。 そして、彼はわかりやすい答えを受けました。 グーグルと握手するのは残念だ。彼は正しい検索の表面に横たわっていた。

stackoverflow.com/questions/2283943/fast-query-runs-slow-in-ssrs

事実、これはよく知られたSQL Serveraの問題であり、私の仲間であるBelAntとマイクロユーザーが私に正しくアドバイスしたように、パラメータースニッフィングに関連しています。そして、解決策はかなりくだらないです。 ストアドプロシージャの例を示します。

 @StartDate Date, @EndDate Date AS BEGIN declare @Start datetime declare @End datetime set @Start = @StartDate set @End = @EndDate
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そしてそれは動作します：

結論2.0

- まあまあ、エプト



UPD3：いくつかのテストの後、ストアドプロシージャも「大きな心」から発明されたことが判明しました。 同志BelAntがアドバイスしたように、リクエストの最後にOPTIONS（RECOMPILE）を追加するだけで十分であることが判明しました。 ここに結論を書きませんが、脳なしでは結論に達しません。そして、嫌がらせの前に結論を出します:(