Open棟梁：バッチ・サンプルの性能測定（１）に使用した、各種リソース情報と、その測定結果。

CREATE ORDERS2.sql

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

DaoOrders2_D1_Insert.xml

<?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>

DaoOrders2_S1_Insert.sql

-- 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
    )

SelectAllOrderID.xml

<?xml version="1.0" encoding="UTF-8" ?>
<ROOT>
  SELECT
    OrderID
  FROM
    Orders
  ORDER BY
    OrderID
</ROOT>

SelectInOrderID.xml

<?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>

T-SQL : Add Data.sql

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

T-SQL : Cursor.sql

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

T-SQL : SELECT > INSERT.sql

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

>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]

更に追加の測定

https://gist.github.com/daisukenishino2/6ab8fd7a9f6eb0c32782c73dc95c3f27