Last active
October 7, 2018 08:27
-
-
Save daisukenishino2/5402109ba92be11f26058e163a1a770a to your computer and use it in GitHub Desktop.
Open棟梁:バッチ・サンプルの性能測定(1)に使用した、各種リソース情報と、その測定結果。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [Northwind] | |
GO | |
/****** Object: Table [dbo].[Orders2] Script Date: 09/18/2012 20:02:26 ******/ | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[Orders2]( | |
[OrderID] [int] NOT NULL, | |
[CustomerID] [nchar](5) NULL, | |
[EmployeeID] [int] NULL, | |
[OrderDate] [datetime] NULL, | |
[RequiredDate] [datetime] NULL, | |
[ShippedDate] [datetime] NULL, | |
[ShipVia] [int] NULL, | |
[Freight] [money] NULL, | |
[ShipName] [nvarchar](40) NULL, | |
[ShipAddress] [nvarchar](60) NULL, | |
[ShipCity] [nvarchar](15) NULL, | |
[ShipRegion] [nvarchar](15) NULL, | |
[ShipPostalCode] [nvarchar](10) NULL, | |
[ShipCountry] [nvarchar](15) NULL, | |
CONSTRAINT [PK_Orders2] PRIMARY KEY CLUSTERED | |
( | |
[OrderID] ASC | |
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | |
) ON [PRIMARY] | |
GO | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="utf-8" ?> | |
<ROOT> | |
-- DaoOrders2_D1_Insert | |
-- 2014/2/9 日立 太郎 | |
INSERT INTO | |
[Orders2] | |
( | |
<DELCMA> | |
<INSCOL name="OrderID">[OrderID],</INSCOL> | |
<INSCOL name="CustomerID">[CustomerID],</INSCOL> | |
<INSCOL name="EmployeeID">[EmployeeID],</INSCOL> | |
<INSCOL name="OrderDate">[OrderDate],</INSCOL> | |
<INSCOL name="RequiredDate">[RequiredDate],</INSCOL> | |
<INSCOL name="ShippedDate">[ShippedDate],</INSCOL> | |
<INSCOL name="ShipVia">[ShipVia],</INSCOL> | |
<INSCOL name="Freight">[Freight],</INSCOL> | |
<INSCOL name="ShipName">[ShipName],</INSCOL> | |
<INSCOL name="ShipAddress">[ShipAddress],</INSCOL> | |
<INSCOL name="ShipCity">[ShipCity],</INSCOL> | |
<INSCOL name="ShipRegion">[ShipRegion],</INSCOL> | |
<INSCOL name="ShipPostalCode">[ShipPostalCode],</INSCOL> | |
<INSCOL name="ShipCountry">[ShipCountry],</INSCOL> | |
</DELCMA> | |
) | |
VALUES | |
( | |
<DELCMA> | |
<IF>@OrderID,</IF> | |
<IF>@CustomerID,</IF> | |
<IF>@EmployeeID,</IF> | |
<IF>@OrderDate,</IF> | |
<IF>@RequiredDate,</IF> | |
<IF>@ShippedDate,</IF> | |
<IF>@ShipVia,</IF> | |
<IF>@Freight,</IF> | |
<IF>@ShipName,</IF> | |
<IF>@ShipAddress,</IF> | |
<IF>@ShipCity,</IF> | |
<IF>@ShipRegion,</IF> | |
<IF>@ShipPostalCode,</IF> | |
<IF>@ShipCountry,</IF> | |
</DELCMA> | |
) | |
</ROOT> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- DaoOrders2_S1_Insert | |
-- 2014/2/9 日立 太郎 | |
INSERT INTO | |
[Orders2] | |
( | |
[OrderID], | |
[CustomerID], | |
[EmployeeID], | |
[OrderDate], | |
[RequiredDate], | |
[ShippedDate], | |
[ShipVia], | |
[Freight], | |
[ShipName], | |
[ShipAddress], | |
[ShipCity], | |
[ShipRegion], | |
[ShipPostalCode], | |
[ShipCountry] | |
) | |
VALUES | |
( | |
@OrderID, | |
@CustomerID, | |
@EmployeeID, | |
@OrderDate, | |
@RequiredDate, | |
@ShippedDate, | |
@ShipVia, | |
@Freight, | |
@ShipName, | |
@ShipAddress, | |
@ShipCity, | |
@ShipRegion, | |
@ShipPostalCode, | |
@ShipCountry | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="UTF-8" ?> | |
<ROOT> | |
SELECT | |
OrderID | |
FROM | |
Orders | |
ORDER BY | |
OrderID | |
</ROOT> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?xml version="1.0" encoding="UTF-8" ?> | |
<ROOT> | |
SELECT | |
[OrderID], | |
[CustomerID], | |
[EmployeeID], | |
[OrderDate], | |
[RequiredDate], | |
[ShippedDate], | |
[ShipVia], | |
[Freight], | |
[ShipName], | |
[ShipAddress], | |
[ShipCity], | |
[ShipRegion], | |
[ShipPostalCode], | |
[ShipCountry] | |
FROM | |
[Orders] | |
WITH (UPDLOCK) | |
<WHERE> | |
WHERE | |
<LIST>AND [OrderID] IN(@OrderID)</LIST> | |
</WHERE> | |
</ROOT> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [Northwind] | |
GO | |
DECLARE @Counter INT | |
SET @Counter = 1 | |
WHILE @Counter <= 2 | |
BEGIN | |
INSERT INTO [dbo].[Orders] | |
([CustomerID] | |
,[EmployeeID] | |
,[OrderDate] | |
,[RequiredDate] | |
,[ShippedDate] | |
,[ShipVia] | |
,[Freight] | |
,[ShipName] | |
,[ShipAddress] | |
,[ShipCity] | |
,[ShipRegion] | |
,[ShipPostalCode] | |
,[ShipCountry]) | |
VALUES | |
('VINET' | |
,5 | |
,GETDATE() | |
,GETDATE() | |
,GETDATE() | |
,3 | |
,32.38 | |
,'Vins et alcools Chevalier' | |
,'59 rue de l''Abbaye' | |
,'Reims' | |
,'RJ' | |
,51100 | |
,'France') | |
SET @Counter = @Counter + 1 | |
END | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE cursorOrders cursor FOR SELECT * FROM [Northwind].[dbo].[Orders]; | |
DECLARE @OrderID [int]; | |
DECLARE @CustomerID [nchar](5); | |
DECLARE @EmployeeID [int]; | |
DECLARE @OrderDate [datetime]; | |
DECLARE @RequiredDate [datetime]; | |
DECLARE @ShippedDate [datetime]; | |
DECLARE @ShipVia [int]; | |
DECLARE @Freight [money]; | |
DECLARE @ShipName [nvarchar](40); | |
DECLARE @ShipAddress [nvarchar](60); | |
DECLARE @ShipCity [nvarchar](15); | |
DECLARE @ShipRegion [nvarchar](15); | |
DECLARE @ShipPostalCode [nvarchar](10); | |
DECLARE @ShipCountry [nvarchar](15); | |
-- カーソルオープン | |
OPEN cursorOrders | |
-- データを格納 | |
FETCH NEXT FROM cursorOrders | |
INTO @OrderID, @CustomerID, @EmployeeID ,@OrderDate ,@RequiredDate ,@ShippedDate ,@ShipVia ,@Freight ,@ShipName ,@ShipAddress ,@ShipCity ,@ShipRegion ,@ShipPostalCode ,@ShipCountry | |
-- カーソルのデータが終わるまで繰り返す | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
-- 繰り返し処理 | |
INSERT INTO [Northwind].[dbo].[Orders2] VALUES | |
(@OrderID, @CustomerID, @EmployeeID ,@OrderDate ,@RequiredDate ,@ShippedDate ,@ShipVia ,@Freight ,@ShipName ,@ShipAddress ,@ShipCity ,@ShipRegion ,@ShipPostalCode ,@ShipCountry); | |
-- データを格納 | |
FETCH NEXT FROM cursorOrders | |
INTO @OrderID, @CustomerID, @EmployeeID ,@OrderDate ,@RequiredDate ,@ShippedDate ,@ShipVia ,@Freight ,@ShipName ,@ShipAddress ,@ShipCity ,@ShipRegion ,@ShipPostalCode ,@ShipCountry | |
END | |
-- カーソル終了処理 | |
CLOSE cursorOrders | |
DEALLOCATE cursorOrders |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO [Orders2] | |
SELECT * FROM [Orders] |
測定結果2
RerunnableBatch_sample(静的
1万件
- ExT:9188[msec], CT:5453[msec], KT:1984[msec], UT:3469[msec]
- ExT:8058[msec], CT:5531[msec], KT:2047[msec], UT:3484[msec]
- ExT:7042[msec], CT:5172[msec], KT:1828[msec], UT:3344[msec]
- ExT:8149[msec], CT:5766[msec], KT:2156[msec], UT:3609[msec]
- ExT:7883[msec], CT:5734[msec], KT:1938[msec], UT:3797[msec]
- ExT:9375[msec], CT:5453[msec], KT:2016[msec], UT:3438[msec]
- ExT:7775[msec], CT:5281[msec], KT:1906[msec], UT:3375[msec]
- ExT:8977[msec], CT:5859[msec], KT:2172[msec], UT:3688[msec]
- ExT:7967[msec], CT:5172[msec], KT:1750[msec], UT:3422[msec]
- ExT:9161[msec], CT:5469[msec], KT:1797[msec], UT:3672[msec]
10万件
- ExT:76111[msec], CT:52734[msec], KT:18016[msec], UT:34719[msec]
- ExT:75945[msec], CT:53578[msec], KT:18172[msec], UT:35406[msec]
- ExT:74696[msec], CT:53484[msec], KT:17625[msec], UT:35859[msec]
- ExT:72110[msec], CT:51531[msec], KT:19391[msec], UT:32141[msec]
- ExT:69156[msec], CT:50297[msec], KT:17156[msec], UT:33141[msec]
20万件
- ExT:155149[msec], CT:109547[msec], KT:37078[msec], UT:72469[msec]
- ExT:149833[msec], CT:105828[msec], KT:35516[msec], UT:70313[msec]
100万件
N/A
RerunnableBatch_sample2(静的
1万件
- ExT:5639[msec], CT:4234[msec], KT:1234[msec], UT:3000[msec]
- ExT:5480[msec], CT:4016[msec], KT:1156[msec], UT:2859[msec]
- ExT:5625[msec], CT:4094[msec], KT:1266[msec], UT:2828[msec]
- ExT:5701[msec], CT:4203[msec], KT:1375[msec], UT:2828[msec]
- ExT:5602[msec], CT:4063[msec], KT:1141[msec], UT:2922[msec]
- ExT:5742[msec], CT:4031[msec], KT:1172[msec], UT:2859[msec]
- ExT:5770[msec], CT:4109[msec], KT:1188[msec], UT:2922[msec]
- ExT:5594[msec], CT:3984[msec], KT:1250[msec], UT:2734[msec]
- ExT:5560[msec], CT:4094[msec], KT:1141[msec], UT:2953[msec]
- ExT:5561[msec], CT:4078[msec], KT:1266[msec], UT:2813[msec]
10万件
- ExT:51188[msec], CT:39547[msec], KT:10969[msec], UT:28578[msec]
- ExT:51467[msec], CT:39531[msec], KT:11031[msec], UT:28500[msec]
- ExT:54153[msec], CT:41031[msec], KT:11047[msec], UT:29984[msec]
- ExT:53608[msec], CT:41188[msec], KT:11953[msec], UT:29234[msec]
- ExT:53042[msec], CT:40953[msec], KT:11625[msec], UT:29328[msec]
20万件
- ExT:105219[msec], CT:79844[msec], KT:22031[msec], UT:57813[msec]
- ExT:113258[msec], CT:85344[msec], KT:24094[msec], UT:61250[msec]
100万件
N/A
RerunnableBatch_sample3(静的
1万件
- ExT:2230[msec], CT:797[msec], KT:141[msec], UT:656[msec]
- ExT:2188[msec], CT:719[msec], KT:47[msec], UT:672[msec]
- ExT:2481[msec], CT:703[msec], KT:94[msec], UT:609[msec]
- ExT:2174[msec], CT:719[msec], KT:141[msec], UT:578[msec]
- ExT:2293[msec], CT:891[msec], KT:109[msec], UT:781[msec]
- ExT:2261[msec], CT:766[msec], KT:47[msec], UT:719[msec]
- ExT:2243[msec], CT:750[msec], KT:141[msec], UT:609[msec]
- ExT:2284[msec], CT:672[msec], KT:109[msec], UT:563[msec]
- ExT:2247[msec], CT:719[msec], KT:141[msec], UT:578[msec]
- ExT:2245[msec], CT:703[msec], KT:78[msec], UT:625[msec]
10万件
- ExT:20703[msec], CT:7328[msec], KT:1016[msec], UT:6313[msec]
- ExT:18764[msec], CT:7172[msec], KT:1359[msec], UT:5813[msec]
- ExT:18827[msec], CT:6641[msec], KT:1078[msec], UT:5563[msec]
- ExT:18790[msec], CT:6922[msec], KT:1281[msec], UT:5641[msec]
- ExT:18796[msec], CT:7172[msec], KT:1344[msec], UT:5828[msec]
- ExT:18718[msec], CT:7203[msec], KT:1391[msec], UT:5813[msec]
- ExT:18658[msec], CT:6969[msec], KT:1234[msec], UT:5734[msec]
- ExT:19024[msec], CT:6891[msec], KT:1281[msec], UT:5609[msec]
- ExT:19119[msec], CT:7016[msec], KT:1391[msec], UT:5625[msec]
- ExT:19109[msec], CT:6844[msec], KT:1203[msec], UT:5641[msec]
20万件
- ExT:38376[msec], CT:14375[msec], KT:1969[msec], UT:12406[msec]
- ExT:38908[msec], CT:14359[msec], KT:2203[msec], UT:12156[msec]
- ExT:38408[msec], CT:13922[msec], KT:1953[msec], UT:11969[msec]
- ExT:42259[msec], CT:16328[msec], KT:2313[msec], UT:14016[msec]
- ExT:41773[msec], CT:16047[msec], KT:2063[msec], UT:13984[msec]
100万件
- ExT:198943[msec], CT:74766[msec], KT:10641[msec], UT:64125[msec]
- ExT:187223[msec], CT:69531[msec], KT:11063[msec], UT:58469[msec]
Transact-SQL > Cursor
1万件
10万件
20万件
100万件
Transact-SQL > SELECT > INSERT
1万件
10万件
20万件
100万件
結果の分析2
単位は、msec
測定モデル\データ件数 | 830件 | 1万件 | 10万件 | 20万件 | 100万件 |
---|---|---|---|---|---|
RerunnableBatch_sample(静的 | 955 | 8,356 | 73,604 | 152,491 | N/A |
RerunnableBatch_sample2(静的 | 767 | 5,627 | 52,692 | 109,239 | N/A |
RerunnableBatch_sample3(静的 | 522 | 2,265 | 19,051 | 39,945 | 193,083 |
Transact-SQL > Cursor | 582 | 2,419 | 17,949 | 42,744 | 176,551 |
Transact-SQL > SELECT > INSERT | 11 | 35 | 244 | 467 | 2,172 |
※ Transact-SQL > SELECT > INSERTは、単純なInsert処理より高速なので、
ヒープを直接処理しているため高速な模様(比較対象として不適切)。
※ データを見ると、性能はデータ件数に比例することを確認できる。
- 疑似フェッチ方式なので、データ件数が増えてもメモリ・リークなどは起こさない。
- また、EXE : RerunnableBatch_sample3(DB → AP → DB) は、比較対象の、T-SQL : Cursor (DB → DB)と比べて同等程度の性能だが、1筐体内でループバック・インターフェースで通信しているため、大きな性能差が出ていないものと思われる。従って、ネットワーク経由となると、コミット・インターバルのラウンド・トリップ分、クライアント-サーバ間の通信オーバーヘッドが増えて遅くなると考えられる。
追加の調査と結果
RerunnableBatch_sample2(動的)の100万件の処理時間
処理時間の計測
結果
静的 : 546,195(推定値)---> 動的 : 778,368
- ExT:783472[msec], CT:653578[msec], KT:117828[msec], UT:535750[msec]
- ExT:773263[msec], CT:652172[msec], KT:117594[msec], UT:534578[msec]
RerunnableBatch_sample3でローカルIPアドレスを使用した際の100万件の処理時間
処理時間の計測
- ローカル・ループバック・アドレスから、ローカルIPアドレスに変更して計測。
その際、ルーティング・テーブルを変更してデフォルト・ゲートウェイ経由にする。- Windowsでルーティングテーブルを操作する:Tech TIPS - @IT
http://www.atmarkit.co.jp/ait/articles/0301/11/news003.html - 例
- ローカルIPアドレス : 192.168.179.3
- デフォルト・ゲートウェイ : 192.168.179.1
- Windowsでルーティングテーブルを操作する:Tech TIPS - @IT
>route ADD 192.168.179.3 MASK 255.255.255.255 192.168.179.1 METRIC 3
- また、コミット・インターバル(上限2,100)をチューニングして性能に与える影響を調査する。
(疑似フェッチでパラメタを使用しており、SQL Serverのパラメタ上限が2,100迄のため)
結果
コミット・インターバル = 100
ローカル・ループバック・アドレス : 193,083 ---> ローカルIPアドレス : 1053,107
- ExT:1039615[msec], CT:87141[msec], KT:22859[msec], UT:64281[msec]
- ExT:1066599[msec], CT:89047[msec], KT:24016[msec], UT:65031[msec]
コミット・インターバル = 2,000
ローカル・ループバック・アドレス : 290,963 ---> ローカルIPアドレス : 781,757
- ローカル・ループバック・アドレス
- ExT:283370[msec], CT:66094[msec], KT:6172[msec], UT:59922[msec]
- ExT:298555[msec], CT:69563[msec], KT:5828[msec], UT:63734[msec]
- ローカルIPアドレス
- ExT:780203[msec], CT:76813[msec], KT:12359[msec], UT:64453[msec]
- ExT:783311[msec], CT:80953[msec], KT:13844[msec], UT:67109[msec]
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
結果の分析1
ラウンド・トリップ軽減の比較分析
RerunnableBatch_sampleとRerunnableBatch_sample2でラウンド・トリップ軽減したが、1筐体内でループバック・インターフェースで通信しているため、大きな性能差が出なかった(830回を9回に減らして200msec程度の差)。これがクライアント-サーバ間の通信となると、ラウンド・トリップのオーバーヘッドが大きくなり性能差が大きくなる。
静的SQLと動的SQLの比較分析
静的パラメタライズド・クエリと動的パラメタライズド・クエリの比較の際は、
CTを比較し、1件あたり 250msec / 830件 = 0.3msec / 件 程度と動的処理も高速。
※ XMLタグ数によるオーバヘッドについては、コチラを参照。
EXEとT-SQLの比較分析
EXE : RerunnableBatch_sample3 はバッチ専用の処理方式であり、CTを見ると、AP側の処理時間は、100msec以下と高速。
因みに、ExTは500msec程度、掛っているので、DB側の処理の方が時間が掛っていることが解る。
EXE : RerunnableBatch_sample3(DB→AP→DB) は、
比較対象の、 T-SQL : Cursor (DB→DB)と比べても十分速い事が解る。
T-SQL : Select > Insert は、非常に高速だが、間に処理を含めることができない。