SQL Server ストアド プロシージャのメジャーの作成と最適化

[はじめに] データベースの開発プロセスでは、複雑なビジネス ロジックとデータベース操作に遭遇することがよくあります。このとき、SP はデータベース操作をカプセル化するために使用されます。プロジェクトに多数の SP が存在し、一定の記述基準がないと、将来のシステムの保守が困難になったり、大規模な SP のロジックを理解することが難しくなります。データベースでは、複雑なビジネス ロジックが頻繁に発生します。データベース操作の場合、現時点では SP を使用してデータベース操作をカプセル化します。プロジェクト内にSPが多数存在し、一定の記述基準がないと、今後のシステムの維持が困難になったり、データ量が多くなるとSPのロジックが理解しにくくなったりします。データベースが大きいか、プロジェクトに SP に対する高いパフォーマンス要件がある場合、最適化の問題が発生します。そうでない場合、速度が非常に遅くなる可能性があります。個人的な経験から言えば、最適化された SP は SP よりも数百倍効率的です。性能が悪いSP。

詳細:
1. 開発者が他のライブラリのテーブルまたはビューを使用する場合、ライブラリ間の操作を実装するには、現在のライブラリにビューを作成する必要があります。「databse.dbo.table_name」を直接使用しないことをお勧めします。 sp_depends SP が使用するデータベース間テーブルやビューが表示できず、検証に不便です。

2. SP を送信する前に、開発者は set showplan on を使用してクエリ プランを分析し、独自のクエリ最適化チェックを実行する必要があります。

3. プログラムの動作効率を向上させ、アプリケーションを最適化するには、SP の書き込みプロセス中に次の点に注意する必要があります:

(a) SQL の使用仕様:

i。システムの同時実行性を向上させるには、holdlock 句を使用します。

ii. 同じテーブル、特に大量のデータを含むテーブルに繰り返しアクセスしないようにしてください。最初に条件に基づいてデータを一時テーブルに抽出してから、接続を作成することを検討できます。

iii. カーソルによって操作されるデータが 10,000 行を超える場合は、カーソルの使用を避けるようにしてください。カーソル操作でのテーブル結合は避けるようにしてください。 。

iv. where ワードの記述に注意してください。条件句の順序は、インデックスの順序と範囲のサイズに従って決定する必要があります。可能な限り、範囲は大きいものから小さいものまでです。

v. where 句の「=」の左側で関数、算術演算、またはその他の式演算を実行しないでください。そうしないと、システムがインデックスを正しく使用できない可能性があります。

vi. レコードが存在するかどうかを判断するには、select count(1) の代わりにexists を使用してみてください。count 関数はテーブル内のすべての行をカウントする場合にのみ使用され、count(1) の方が count(*) より効率的です。 。

vii. 「>」の代わりに「>=」を使用してみてください。

viii. 一部の or 句と Union 句の間の置換に注意してください。

ix. テーブル間の接続のデータ型に注意し、異なる種類のデータ間の接続を避けてください。

x. ストアド プロシージャのパラメーターとデータ型の関係に注意してください。

xi. 他のアプリケーションとの競合を防ぐために、挿入および更新操作のデータ量に注意してください。データ量が 200 データ ページ (400k) を超えると、システムはロックをアップグレードし、ページ レベルのロックはテーブル レベルのロックにアップグレードされます。

(b) インデックスの使用仕様:

i. インデックスの作成は、アプリケーションと併せて考慮する必要があります。大規模な OLTP テーブルには 6 つを超えるインデックスを持たないようにすることをお勧めします。

ii. 可能な限りインデックス フィールドをクエリ条件として使用し、特にクラスター化インデックスを使用して、インデックスを強制的に指定できます

iii。必要に応じて新しいインデックスを作成します。

iv. インデックス フィールドを条件として使用する場合、インデックスが結合インデックスの場合、システムが確実にインデックスを使用するようにインデックスの最初のフィールドを条件として使用する必要があります。そうでない場合、インデックスは使用されません。 。

v. インデックスのメンテナンスに注意し、定期的にインデックスを再構築し、ストアド プロシージャを再コンパイルします。

(c) Tempdb の使用仕様:

i。distinct、order by、group by、have、join、cumute の使用は避けてください。これらのステートメントは tempdb の負荷を増大させるためです。

ii. 一時テーブルの頻繁な作成と削除を避け、システム テーブル リソースの消費を削減します。

iii. 一時テーブルを作成するときに、一度に挿入されるデータの量が多い場合は、create table の代わりに select into を使用すると、ログを回避し、データ量が多くない場合には速度を向上させることができます。システム テーブルのリソースを軽減するために、最初にテーブルを作成してから挿入することをお勧めします。

iv. 一時テーブルに大量のデータがあり、インデックスを作成する必要がある場合は、システムが適切に活用できるように、一時テーブルの作成とインデックス作成のプロセスを別のサブストアド プロシージャに配置する必要があります。一時テーブルのインデックス。

v. 一時テーブルを使用する場合は、ストアド プロシージャの最後にすべての一時テーブルを明示的に削除する必要があります。これにより、システム テーブルの長期ロックを回避できます。

vi. この操作では 1 つのステートメントで tempdb システム テーブルが複数回使用されるため、システム テーブルへの負担を軽減するために、他の大きなテーブルに接続されている大きな一時テーブルのクエリと変更には注意してください。

(d) 合理的なアルゴリズムの使用:

上記の SQL 最適化テクノロジと ASE チューニング マニュアルの SQL 最適化の内容に基づいて、実際のアプリケーションと組み合わせて、複数のアルゴリズムを比較に使用して、リソースの消費が最小限で最も効率的な方法を取得します。特定の ASE チューニング コマンドが使用可能です: setstatisticsioon、setstatisticstimeon、setshowplanon など。

上記は SQL Server ストアド プロシージャの作成と最適化対策の内容です。その他の関連内容については、PHP 中国語 Web サイト (m.sbmmt.com) をご覧ください。