一般に、実際のテーブルを選択するよりも一時テーブルを選択する方が速いですか？

tempdbへの書き込みは、tempdbにない実際のテーブルよりも高速です

その通りです。TempDbには2つのIO拡張機能があります。

ユーザーデータベースのテーブルへの書き込みでは、コミット時にログレコードをディスクにフラッシュする必要があります。または、最小限のログの挿入（SELECT … INTOなど）の場合は、データベースページをディスクにフラッシュする必要があります。コミット時。ユーザーデータベースで最小限のログが機能する方法は、データベースページがディスクに直接書き込まれることです。SELECT… INTOが完了するまでに、新しいページはすべて物理ファイルに書き込まれています。

TempDbは障害後に回復されないため、コミット時にTempDb書き込みをディスクにフラッシュする必要はありません。したがって、単にそうではありません。変更によってログレコードが生成されますが、ログバッファは、すべてのコミットではなく、いっぱいになったときにのみディスクにフラッシュされます。

および SQL Server 2014以降、TempDbの最小ログ挿入は、常にディスクに書き込まれるとは限りません。小さいものをロードする場合、短命の一時テーブルは、ディスクにまったく書き込まれない可能性があります。ログには、テーブルのページ割り当てとメタデータエントリに関するいくつかのレコードが含まれますが、それだけです。

EGは次を実行します。 tempdbのバッチ、完全リカバリデータベース、および単純なリカバリデータベースを使用して、違いを確認します。

drop table if exists foo go declare @data bigint declare @log bigint select @log = sum(case when type_desc = "LOG" then num_of_bytes_written end) ,@data = sum(case when type_desc = "ROWS" then num_of_bytes_written end) from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs select * into foo from sys.objects select -@log + sum(case when type_desc = "LOG" then num_of_bytes_written end) log_bytes ,-@data + sum(case when type_desc = "ROWS" then num_of_bytes_written end) data_bytes , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs 

次のように表示されます：

単純なリカバリの場合：

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE 

完全なリカバリの場合：

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL 

およびtempdb：

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE 

tempdbの場合、ログバッファがフラッシュされることがあります：

log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE 

コメント

• 最初の挿入の方が速い場合がありますが、後で戻ってきて噛み付きます。このデモは、怠惰なライターが、もう存在しない一時テーブルに対してダーティなディスクtempdbページへの書き込みでビジー状態になっているため、データをバッファーキャッシュに取り込むクエリを示しています。 youtube .com / watch？v = X60ipwYv1Ms & feature = youtu.be
• はい。 'は、どのオブジェクトにも割り当てられていないバッファプールページのフラッシュを排除するための潜在的な将来の拡張機能です。ただし、大きな一時テーブルをロードすると、キャッシュに使用できるメモリが減少するため、常に直接または間接的にIOが駆動されます。

tempdbへの書き込みだけでなく、IOによっては、 David Browneの回答で拡張されているように、ヒットするディスク/ネットワークIOのすべてではないことがよくあります。構成では、データをディスクにスプールする必要があるほど大きい場合でも、「通常の」テーブルを選択するよりも高速であることがわかります。

• TempDBが異なる場合があります。ドライブがあるため、独自のIO帯域幅があります。これは、SSDではなく回転ドライブで特に重要です。同じデータベース（または同じドライブ上の別のデータベース）へのおよび書き込みからの読み取りには、より多くのヘッドの動きが必要になります。 IOレイテンシーを追加し、有効なIO帯域幅を制限する可能性があります。異なるドライブ/アレイ上のデータベース間でデータをコピーしても、同じ余分なレイテンシーは発生しません。

• TempDBはfa上にある場合もあります。メインストレージよりもスターメディア。おそらく、メインストレージがネットワーク上にあるローカルドライブ、またはメインストアが従来のドライブ上にあるNVMeSSDにあります。

これらの違いは両方とも同じ内部に見られる場合があります複数のファイルグループを使用してデータの一部を異なるドライブ/アレイ間で分散する場合はデータベース。

アクティブに使用されている複数のデータベースがある場合は、逆の場合もあります。 TempDBは共有リソースであるため、TempDBとそれをホストするドライブ/ネットワークは、個々のDBのデータファイルよりも負荷がかかる可能性があります。