ストアドプロシージャの使用はやめるべきだ

アプリケーション開発者の皆さん、どうかお願いですから、コードベースにストアドプロシージャ（sproc）への依存を許すのをやめてください。アプリケーション開発者の皆さん、どうかお願いですから、SQLを少し学んでください！データベース管理者の皆さん、どうかお願いですから、アプリケーション開発者の不足を補うための道具としてストアドプロシージャを使うのをやめてください。データベース管理者の皆さん、どうかお願いですから、SQL、インデックス、クエリプラン、トランザクション、分離レベル、パラメータ化クエリ、パラメータスニッフィング、Sargability、プロファイリング、ブックマークルックアップ、スキーマ設計、正規形、非正規化、カーディナリティ、B(+)-tree、WAL、CPU/Log IO/Data IOの聖なる三位一体、そして私のDBAがまだ教えてくれていない全ての事柄について、開発者を導いてください。 11あるいは私が言及し忘れたこと... また、私は自分で学ぶ能力がないわけではなく、確かにそうしています。DBAが何かを教えてくれると、いつもより良く学べるのです😅 - 私、過去5年間さて、それが終わったので、sprocと呼び続けることができます。聞くところによると、私たちは話す必要があります。 22あるいは私があなたに話す必要があるのかもしれません。アンドリュー@heffree.devまで返信してください。これが私の職場だけのものなのかどうかは分かりませんが、その場合、私たちは少し自分をさらけ出すことになるのでしょうが、私はあまりにも多くの（つまり、一つでも）アプリケーションがsprocを呼び出しているのを目にしています。幸いなことに、この（疑わしいほど新しい）Redditユーザーもそれを言及していたので、私だけではないはずです！私は以前からこのように感じていましたが、これを見て（それに最近の職場の出来事もあって）、それをまとめた簡単な投稿をする気になりました。私の希望は、この投稿に包括的な理由を固め、議論の余地がなく、最終的な判断にのみ意見の相があるようにすることです。幸いなことに、それほど多くのことはありません、本当です。これがSQL Server中心であることについて私を許してください、しかしそれは、あなたがアプリケーションを所有しているなら、あなたのアプリのロジックとDBアクセスロジックを併置するためにできる限りのことをすべきだという主張を変えるものではありません。DBMSに何を求めますか？データベースにリクエストを送信するとき、私たちが再現したい主要なsprocの利点は2つあります。1つ目はプランキャッシングです。データベースクエリを実行すると、DBMSはデータの統計情報を使用して、データを収集するための最適な方法を計算します。これはコストのかかるプロセスなので、理想的にはまれな操作です。2つ目はパラメータ化クエリです。パラメータ化クエリは、DBMSに入力タイプの情報を伝えることを可能にし、データとロジックを分離することを可能にします。都合の良いことに、パラメータ化クエリはプランキャッシュからのクエリプランの再利用にも役立ちます。もし私がSELECT 1 FROM my_table WHERE code = 'SUPER_COOL_GUY'を送信し、次にSELECT 1 FROM my_table WHERE code = 'SUPER_UNCOOL_GUY'を送信した場合、それは2つの別々のクエリプランであり、それは動的クエリと呼ばれる悪です。 33通常、クエリの構造が大きく変化する場合に動的クエリが見られますが、これはカウントされます！しかし、もし私が以下を送信した場合：-- おい、見てみろ、ストアドプロシージャだ、なんてこった... -- これには頼ることができる、それは問題ないEXEC sp_executesql N'SELECT 1 FROM my_table WHERE code = @code', N'@code NVARCHAR(50)', @code = N'SUPER_MEH_GUY'これで、コードに何でも送信でき、DBMSが再計算する必要なしに、同じクエリプランを繰り返し使用できるパラメータ化クエリができました。 44それは、パラメータがパラメータセンシティブプランスニッフィングされて別のクエリプランが必要だと判断されない限りです。しかし、それはsprocでも起こり得ます。そして、それは非常にSQL Server固有です。では、ストアドプロシージャは何をもたらすのでしょうか？全く何も！まあ、頭痛は一つですが。しかし、あなたが上記のSQLを実行するか、sprocを呼び出すかに関わらず、DBMSはそれを同じように扱います。それは入力のパラメータへのバインディングを処理し、最初の実行時にクエリプランを生成し（はい、sprocのために事前計算されるわけではありません）、同じクエリボディを見たときにクエリプランを再利用します。私たちは同じ利点をすべて得ます。sprocを使用することによるその他の楽しい副作用は何でしょうか？さて、私たちのアプリケーションコードとDBアクセスは別々にバージョン管理され、sprocは異常な（つまり、非常にまれで、狂った）DBAによって私たちの足元で変更される可能性があります。クエリを更新するためにマイグレーションをデプロイする必要があり、変更がどのように行われたかを確認するために diff migration_for_my_sproc migration_for_my_sproc_n を実行する必要があります。そして、ロールバックについては、ああ、神様...デプロイ後にデータベースロールバックを開始したのはいつですか？私のチームでは、一度もありません。実際、それはあまりにも複雑で危険なので、私たちは手段さえ導入していません。一方で、私たちがクエリを所有しているなら、私たちはクエリを所有できます、それは素晴らしいことです！データベースアクセスをアプリケーションロジックと同期してバージョン管理できます。実際のバージョン管理を利用できます。それは、sprocの新しいバージョンをデプロイするためにマイグレーションを記述するたびに更新することを覚えておこうとするファイルを持っているのではなく、バージョン管理を利用できます。そして、それは常に同期が取れていません。なぜなら、誰もCIチェックを行って、あなたがデプロイしたばかりのマイグレーションが、バージョン管理のためだけに追跡している冗長なファイルも更新したことを確認しなかったからです... *激しい呼吸* そして、私たちはアプリケーションとクエリをロールバックできます！これらすべてを経て、もしあなたがまだストアドプロシージャを望むなら、それは単にあなたのDBAにあなたのSQLを所有してほしいからだとしか推測できません（その理由は存在します）。彼らに、あなたと一緒にオンコールでいてくれたことに感謝しましょう。 アプリケーション開発者はDBとどのようにやり取りすべきでしょうか？そうです、あなたのDBです、少しはプライドを持ちましょう。 55この時点で「禁止」を選択した場合、そうでなければあなたのプライドを好きなところに置いてください。データベースアクセスに対する責任を受け入れた今、私たちは立派な同僚であり、DBAの負担を可能な限り軽減することが重要です。あなたはすべての間違いを避けることはできません、DBは難しいです、DBAは賢いです。しかし、いくつかの基本をマスターすれば、大きく前進できます。そのうちのいくつかを説明します。 66このうちいくつかはかなり明白かもしれませんが、私は多くの人が明白なことを書くのが良いことだと言っているのを見てきました:Dネットワークリクエストまず第一に、アプリケーション開発者として、ネットワークリクエストは私たちの最大の敵であることを知っておくべきです。ほとんどの場合、それは遅いです。オンプレミスでない場合、そのDBは...あなたのクラウドで実行されているアプリにさえ近くありません。それは別のネットワークホップ先にあり、永続接続であっても、少なくともおそらく同じデータセンターにあるでしょう。したがって、可能な限り少ないリクエストでDBとやり取りしたいのです。ほとんどのDBには、挿入または更新した行の値を返すオプションがあります。そうでない場合は、SELECTとバンドルされたupsertパターンを使用してください。私はすべてのDBのインとアウト/理由を説明することはできませんが、すべてのDBはあなたの目標を達成するために多くの別々のリクエストをしないことをサポートしています。インデックスインデックスを使用してください。クエリの述語（WHERE句）で列を使用している場合、その列にインデックスが必要になります。おそらく、列のコレクションに対する複合インデックスでしょう。述語にインデックスが必要ない日は、地獄が凍る日になるでしょう。明らかに誇張ですが、非常に多くの場合、それは真実です。具体的には、インデックスは列のカーディナリティが大きいほど効果的です。ブール値またはビットのカーディナリティは2です（NULL可能であれば3）。そのため、せいぜい2つの「バケット」にしか分離できません。インデックスを避けたい理由は、DBへの書き込み速度に影響を与えるためですが、これはほとんど常に些細なことであり、問題が見られるまで考慮する価値はありません。問題が見られるまでインデックスを付けないという逆を選択すべきではありません。いつものように、プロファイル、ベンチマーク、分析を行ってください。DBMSはカバリングインデックス（通常はINCLUDEで指定）と呼ばれるものもサポートしているはずです。私の経験では、これらはもう少し状況に応じたものですが、読み取り速度を向上させたいが書き込みがそれほど多くない場合は、クエリが要求するデータをインデックスに含めることができます。ORMORMは...まあまあです。急成長するアプリケーションの簡単なクエリには役立ちます。