SQL Serverのパフォーマンス SELECT INTO vs INSERT INTO for temporary tables
By: Jignesh Raiyani｜更新日時：2017-01-04 2017-01-04｜コメント（9）｜関連記事 その他 > T-SQL
MSSQLTipsの無料Webセミナー。
このウェビナーでは、SQL Server の開発のベスト プラクティスについて説明します。
問題
SQL Server には、一時テーブルにデータを挿入するための SELECT…INTO および INSERT…INTO コードがあります。
解決策
SQL Server は一時テーブルに 2 つのオプションを用意しています。
- ローカル一時テーブル
- グローバル一時テーブル
ローカル一時テーブルには「#」、グローバル一時テーブルには「##」というプレフィックスを追加する必要があります。 これらのオブジェクトはTempDBシステムデータベースに作成されます。 一時テーブルは、TempDBデータベースに固有のオブジェクト名で保存され、接続レベルまたはセッションレベルで作成されます。 これらのテーブルは、セッション内でのみ表示され、アクセス可能です。DROP TABLEコマンドを使用して一時テーブルを削除することもできますし、セッションが切断されると一時テーブルは自動的に削除されます。 また、SQL Serverは一時テーブルの統計情報を管理します。
一時テーブルの作成と削除には、TempDBのアロケーション・マップ・ページ（IAM、SGAM、PES）へのアクセスと変更が必要です。 一時テーブルは、ディスクベースの永久テーブルと比較して、バッファプールにキャッシュされます。 一時テーブルのキャッシングでは、SQL Server はテーブルを物理的に削除せず、切り捨てて IAM とデータページを維持します。
一時テーブルは 2 つの方法で作成できます。
- CREATE TABLE
- SELECT INTO
一般的に言って、少量のデータであれば、どちらの方法でも同じようなパフォーマンスが得られます。 データは一時テーブルに素早く挿入されますが、データ量が多い場合は、クエリのパフォーマンスが低下することがあります。 これは通常、一時テーブルに大量の行を挿入したときに起こります。
SQL Server INSERT INTO の例
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLCREATE TABLE #list_to_table(Id BIGINT)SELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML) INSERT INTO #list_to_tableSELECT f.x.value('.', 'BIGINT') AS IdFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
SQL Server の解析およびコンパイル時の例です。
CPU time = 0 ms, elapsed time = 2 ms.
Table '#list_to_table____000000000015'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
入力パラメーターとして大量のカンマ区切りの値がある場合、コードはデータをテンプレに挿入する間、数秒遅れることがあります。
SQL Server SELECT INTO の例
別の解決策として、SELECT … … INTO コマンドを使用することができます。
SET STATISTICS IO ONSET STATISTICS TIME ONDECLARE @ids NVARCHAR(MAX) = N'115676,115678,115679,115680,115681'DECLARE @input_xml XMLSELECT @input_xml = Cast('<root><x>'+ Replace(@ids, ',', '</x><x>')+ '</x></root>' AS XML)SELECT f.x.value('.', 'BIGINT') AS IdINTO #list_to_tableFROM @input_xml.nodes('/root/x') f(x)DROP TABLE #list_to_table
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(5 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
SQL Server INSERT。…SELECT vs. SELECT…INTO with a large data set
上記の例は非常に小さいので、以下の例を使って、大量のデータでのパフォーマンスを見てみましょう。
SQL Server INSERT… SELECT
CREATE TABLE #EMPLOYEEEEE (Emp_id BIGINT, f_name NVARCHAR(100), l_name NVARCHAR(100), Email NVARCHAR(100), is_active BIT) INSERT INTO #EMPLOYEESELECT Emp_id, f_name, l_name, Email, is_active FROM employee
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 1407 ms, elapsed time = 3241 ms.(1727264 row(s) affected)
SQL Server SELECT….INTO
SELECT Emp_id, f_name, l_name, Email, is_activeeINTO #EMPLOYEEFROM employee
SQL Server parse and compile time:
SQL Server Execution Times: CPU time = 1499 ms, elapsed time = 489 ms.(1727264 row(s) affected)
結果と分析
見ての通り、SELECT.
INSERT…INTO コマンドは、挿入/更新/削除操作のためにキャッシュに作成されたデータ ページを再利用します。 SELECT…INTO コマンドは、regulartables と同様にテーブル作成のために新しいページを作成し、一時的なテーブルが削除されたときに物理的に削除します。
次のステップ
- コードを開発したり、最適に動作していないコードをチューニングする際には、これらのパフォーマンスに関する考慮事項を念頭に置いてください。
- 自分の環境でさらにシナリオをテストして、どのコーディング技法が最もパフォーマンスが高いかを判断します。
- さまざまなコーディング技法に対して常にオープンマインドであり、各技法をテストして、パフォーマンスが答えを決めるようにします。
最終更新。 2017-01-04
著者について 著者について
Jignesh Raiyani氏はSQL Serverの開発者/DBAで、T-SQLプロシージャの設計と開発、およびクエリのパフォーマンスチューニングの経験があります。SQLプロシージャの設計と開発、そしてクエリのパフォーマンスチューニングの経験があります。全てのTipsを見る
- More Database Developer Tips…