SQL Server ストアド プロシージャを作成および最適化する方法

[はじめに] データベースの開発プロセスでは、複雑なビジネス ロジックとデータベース操作に遭遇することがよくあります。このとき、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 サイトの他の関連記事を参照してください。