Entity Framework開発者のためのSQLにおけるnvarchar(max)の危険性

問題を理解する: Entity Framework におけるデフォルトの動作

Timはまず、C#の開発者がFirstNameやLastNameのようなフィールドを持つモデルを定義する、Entity Frameworkの一般的なシナリオを説明します。 移行を使用してテーブルがSQL Serverで自動的に作成される場合、生成されるスキーマは、デフォルトでこれらの文字列フィールドをnvarchar(max)に設定します。

Timが説明するように、これはEntity Frameworkが割り当てる適切な文字列サイズを知らないため、デフォルトで最大長を割り当てるという安全なルートを選択するために起こります。 つまり、各nvarcharカラムは最大2^31-1文字、最大ストレージサイズはギガバイトです。

この決定は便利なように見えますが、危険なパフォーマンスコストが隠されています。

2つのテーブルのセットアップ例: nvarchar(max)と固定長の比較

この問題を強調するために、ティムは2つの同じ表を作成します：

• ユーザー：姓と名にnvarchar(50)を使用。

• UsersToTheMax: 同じフィールドをnvarchar(max)で。

2:39で、TimはDapperを使用して、nvarcharデータ型だけが異なるように、100万行の同じ行を両方のテーブルにどのように入力したかを説明しています。

この設定により、彼は固定長のUnicode列と可変長のmax列の間で一貫した比較を行うことができます。

クエリと実行計画を比較する

Tim は、両方のテーブルで次の SQL クエリを使用します：

SELECT * FROM dbo.Users ORDER BY LastName；
SELECT * FROM dbo.UsersToTheMax ORDER BY LastName；

3:34では、これらのクエリを実行する際にSQL Serverが内部的に何を行っているかを分析するために、実際の実行計画を可能にしている。

注：このテストは、マシン間の総実行時間についてではありません。Timは、nvarchar(max)がパフォーマンスにどのように影響するかを分離するために、同じデータを同じサーバー上でクエリを比較することに重点を置いています。

衝撃的な結果

実行計画を見ると、大きな違いがあることがわかります：

• nvarchar(50)のクエリは、バッチコストのわずか2%を使用しています。

• nvarchar(max)のクエリは、なんと98%のコストを使用しています。

ティムが言うように、これは、SQL Serverが処理する方法という点で、最大クエリが50倍高価であることを意味します。

CPU時間

• nvarchar(50)のソートには107msかかります。

• nvarchar(max)のソートには339msかかります。

しかし、最大の違いは、特定の並列処理にあります：

• 固定長：0.43秒

• 最大長：22.17秒

同じデータであっても、50倍以上遅いのです。

メモリ消費の違い

Tim は、SQL Server が各クエリに割り当てるメモリ量について説明します：

• nvarchar(50)クエリ：340MB

• nvarchar(max)クエリ：641MB

これだけでも赤信号ですが、キャッシュされていない列をテストする場合、影響はさらに劇的です：

• FirstNameの長さは固定です：357MB

• FirstNameの最大長： 8.5GB

この増加は、SQL Serverがnvarchar値をmaxとして定義したときにどれだけの大きさになるかを知らないため、最大サイズに対応するためにより大きなメモリブロックを確保するために発生します。

なぜ nvarchar(max) は高価なのですか?

9:15で、ティムは根本的な理由を説明する。 nvarchar(max)データ型：

• 最大2^31-1ユニコード文字をサポートし、最大2GBのストレージスペースを消費します。

• SQL Serverでは、行内に直接格納するのではなく、ポインタを使用して、行の外に値を格納する必要があります。

• 固定長カラムと同じ方法でインデックスを作成することはできません。

その結果

• nvarchar(max)カラムにインデックスを付けることができないため、SQL Serverは最適化せずにデータセット全体をソートまたはフィルタリングしなければなりません。

• これは、nvarchar(max) フィールドの ORDER BY、WHERE、JOIN などの操作に影響します。

このような動作は、間違った文字データ長を選択するだけで、メモリ使用量、CPU負荷、速度低下を引き起こします。

ティムの最終的な提案

最後にティムがこう言っています：

Entity Framework のクエリでは、すべての文字列のサイズを指定するようにしてください。

予想されるデータに応じて、nvarchar(100)やnvarchar(255)のように、常に最大文字数で文字列プロパティを定義してください。 このマイナーチェンジにより、以下のことが保証されます：

• 最適化されたストレージスペース

• インデックスのサポート

• クエリコストの削減

• パフォーマンスの一貫性向上

適切な長さを設定することで、データベーススキーマをより効率的にし、いい加減なデフォルト設定の落とし穴を避けることができます。

結論

Tim Corey 氏の video は、SQL の文字列フィールドのデフォルトの長さとして nvarchar(max) を使用すると、気づかないうちにパフォーマンスが低下するという重要な教訓を与えてくれます。 SQL Serverは、名前や住所のような通常のUnicodeテキスト入力であっても、過剰なメモリを割り当て、インデックスをスキップし、CPUコストを増加させます。

課題は？ nvarcharデータ型を理解し、大きなドキュメントや可変長のコンテンツを格納するフィールドに本当に必要でない限り、maxは避けてください。

文字列サイズを指定することで、バイトやメモリを節約できるだけでなく、Entity Framework や SQL コードの効率性、拡張性、堅牢性も向上します。Timのガイダンスに従うことで、アプリケーションの動作が設計上遅くなることはありません。

.NETでデータベースを扱う人にとっては、標準ツールキットの一部となるべきベストプラクティスです。 ティムのチャンネルでSQL関連の動画をチェックしてください。