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] |
追加の調査と結果
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
結果の分析2
単位は、msec
※ Transact-SQL > SELECT > INSERTは、単純なInsert処理より高速なので、
ヒープを直接処理しているため高速な模様(比較対象として不適切)。
※ データを見ると、性能はデータ件数に比例することを確認できる。