くそストアドプロシージャ
ビジネスロジックを全てSQL Serverのストアドプロシージャで書いていてパフォーマンスがでなくて困っている話を聞いた。
どうも数千行のストアドで内容的にも分からないので、なかなか前に進まないらしい。
性能向上の第一歩は計測なのですが、その影響で性能が落ちるのが怖くて、本番計測も簡単ではないようです。
個人的にはストアドでビジネスロジックを書くこと自体受け入れ難い話なのですが、このような状況で対処するならどんな方法にするかなと
ここでの課題を並べると
・性能の計測が本番に適用できない
・パフォーマンスが出ない処理の特定方法が分からない
・でかいストアドに対する対処
性能の計測が本番に適用できない
これは性能測定をした場合の影響が小さければいいわけですが、どのくらいの影響がでるかが分からないレベルなんでしょうね
SQLプロファイラ（GUI）とかだと遅そうですが、その他にも選択肢はあります。以下の記事で様々な方法の比較があって、拡張イベントであれば10%程度遅くなりますが許容範囲のように見えます。
Measuring “Observer Overhead” of SQL Trace vs. Extended Events
トレースファイル：約20% 影響（遅くなる）
SQLプロファイル：約80％影響（遅くなる）
拡張イベント ：約10% 影響（遅くなる）
パフォーマンスが出ない処理の特定方法が分からない
計測ができていてもその解析方法が分からないと前に進みません。特定のSQLであれば実行プランなどを見て改善できますが、問題になるSQL文やプロシージャを特定する必要があります。これらの絞り込みはSQL実行時間やCPUコスト、IOコスト、実行回数を調査することになります。
でかいストアドに対する対処
問題がわかってもストアドが複雑で単純には手を付けれない場合は、その複雑度を下げることを考えます。
リファクタリングが思いつきますが、ストアドのリファクタリングツールを探してみるといくつかあるようです。
SQL Enlightをみるとストアードプロシージャや関数としてT-SQLスクリプトをカプセル化するようなこともできるので、大きなストアドを機械的に分割しながら構造化して扱い易くします。
最後に
くそストアドプロシージャとの戦いはこれらの作業を繰り返して改善していくんでしょうね。
あと、データ分割して並列実行のアプローチ（ミニバッチ化）や、それをさらに小さくしてストリーム処理にしてしまうアイデアもありですね。
一番いいのは、くそストアドプロシージャに出逢わないことなんでしょうが
