SQL Server 2012における大量データの迅速な挿入の例と疑問
4687 ワード
SQL Server 2008のSQLアプリケーションシリーズ--目次索引
今日の実例プレゼンテーションでは、SQL Server 2012でInsert文を使用して1万件のデータを挿入した結果、奇妙な現象に遭遇し、興味のある学生の参考にするために、プロセスを共有しました.
添付:私のテスト環境:SQL Server 2012、名前付きインスタンス
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) Feb 10 2012 19:13:17 Copyright (c) Microsoft CorporationEnterprise Edition on Windows NT 6.0 (Build 6002: Service Pack 2)
サンプル・データベースの作成
この挿入文は、SQL Server 2008 r 2およびSQL Server 2012のバージョンで、テスト結果は次のとおりです.
驚いたことに、SQL Server 2012は5分以上かかりましたが、SQL Server 2008 R 2版は、約6秒しかかかりません.さらにわかりにくいのは、クエリのI/O統計とelapsed timeは、この2つのバージョンでほぼ同じです.この異象については,Insertのたびにミリ秒レベルの精度がこの操作による微細な差を測定するのに十分ではないかもしれないとしか理解できないが,蓄積すると非常に大きい.
ソリューション1:Set NoCount Onを使用すると、効果がすぐに現れます.
Set NoCount On(http://msdn.microsoft.com/zh-cn/library/ms189837.aspx)の役割:返される結果にTransact-SQL文の影響を受ける行数に関する情報が含まれないようにします.これにより、一括挿入時にパフォーマンスが大幅に向上します.この例では、なぜSQL Server 2008 R 2版がこのスイッチの影響を受けないのか、知ってほしい学生は教えてくれて、感謝しています.
改善策2:Set NoCount On+Transactionの使用
ソリューション3:再帰CTEを使用した挿入
まとめ:SQL Server 2012に大量にデータを挿入する場合、Set NoCount ONを覚えておいて、できるだけTransactionを加えてください.もちろん、CTEの使用をお勧めします.これは性能の大きな向上をもたらす可能性があります.
招待月補足:
その後、マイクロソフトのアジア太平洋エンジニアと何度も交流し、初歩的な結論を出した.
「set nocount on」を開かない場合、SSMS 2012とSSMS 2008 r 2バージョンのUIは実行効率に大きな差がある可能性がありますが、SQL Serverエンジンとは明らかな関連はありません.
招待月注:本文の著作権は招待月とCSDNが共同で所有し、転載は出典を明記してください.人を助けるのはセルフサービスに等しい! [email protected]
今日の実例プレゼンテーションでは、SQL Server 2012でInsert文を使用して1万件のデータを挿入した結果、奇妙な現象に遭遇し、興味のある学生の参考にするために、プロセスを共有しました.
添付:私のテスト環境:SQL Server 2012、名前付きインスタンス
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) Feb 10 2012 19:13:17 Copyright (c) Microsoft CorporationEnterprise Edition on Windows NT 6.0
サンプル・データベースの作成
IF OBJECT_ID('DemoPager2012') IS NOT NULL
DROP DataBase DemoPager2012
GO
CREATE Database DemoPager2012
GO
USE DemoPager2012
GO
/*
Setup script to create the sample table and fill it with
sample data.
*/
IF OBJECT_ID('Customers','U') IS NOT NULL
DROP TABLE Customers
CREATE TABLE Customers ( CustomerID INT primary key identity(1,1),
CustomerNumber CHAR(4),
CustomerName VARCHAR(50),
CustomerCity VARCHAR(20) )
GO
TRUNCATE table Customers
GO
----
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @d Datetime
SET @d=getdate();
declare @i int=1
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end
select [ ( )]=datediff(ms,@d,getdate())
SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO
この挿入文は、SQL Server 2008 r 2およびSQL Server 2012のバージョンで、テスト結果は次のとおりです.
驚いたことに、SQL Server 2012は5分以上かかりましたが、SQL Server 2008 R 2版は、約6秒しかかかりません.さらにわかりにくいのは、クエリのI/O統計とelapsed timeは、この2つのバージョンでほぼ同じです.この異象については,Insertのたびにミリ秒レベルの精度がこの操作による微細な差を測定するのに十分ではないかもしれないとしか理解できないが,蓄積すると非常に大きい.
ソリューション1:Set NoCount Onを使用すると、効果がすぐに現れます.
TRUNCATE table Customers
GO
----
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @d Datetime
SET @d=getdate();
set nocount on
declare @i int=1
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end
select [ ( )]=datediff(ms,@d,getdate())
SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO
Set NoCount On(http://msdn.microsoft.com/zh-cn/library/ms189837.aspx)の役割:返される結果にTransact-SQL文の影響を受ける行数に関する情報が含まれないようにします.これにより、一括挿入時にパフォーマンスが大幅に向上します.この例では、なぜSQL Server 2008 R 2版がこのスイッチの影響を受けないのか、知ってほしい学生は教えてくれて、感謝しています.
改善策2:Set NoCount On+Transactionの使用
TRUNCATE table Customers
GO
----
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @d Datetime
SET @d=getdate();
set nocount on
declare @i int=1
BEGIN TRANSACTION
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end
COMMIT
select [ ( )]=datediff(ms,@d,getdate())
SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO
ソリューション3:再帰CTEを使用した挿入
TRUNCATE table Customers
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
DECLARE @d Datetime
SET @d=getdate();
/***** CTE ， ， ***********************/
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),
cast('Customer ' + STR(num,6) AS NVARCHAR(50)),
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 10000
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)
select [ ( )]=datediff(ms,@d,getdate())
SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO
まとめ:SQL Server 2012に大量にデータを挿入する場合、Set NoCount ONを覚えておいて、できるだけTransactionを加えてください.もちろん、CTEの使用をお勧めします.これは性能の大きな向上をもたらす可能性があります.
招待月補足:
その後、マイクロソフトのアジア太平洋エンジニアと何度も交流し、初歩的な結論を出した.
「set nocount on」を開かない場合、SSMS 2012とSSMS 2008 r 2バージョンのUIは実行効率に大きな差がある可能性がありますが、SQL Serverエンジンとは明らかな関連はありません.
招待月注:本文の著作権は招待月とCSDNが共同で所有し、転載は出典を明記してください.人を助けるのはセルフサービスに等しい! [email protected]