daisukenishino2/CREATE ORDERS2.sql

## 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]
	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
	<?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
	-- 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
	)
	<?xml version="1.0" encoding="UTF-8" ?>
	<ROOT>
	SELECT
	OrderID
	FROM
	Orders
	ORDER BY
	OrderID
	</ROOT>
	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
	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
#	測定モデル		処理時間(msec)
1-1	RerunnableBatch_sample	静的	955
1-2	RerunnableBatch_sample	動的	1,193
2-1	RerunnableBatch_sample2	静的	767
2-2	RerunnableBatch_sample2	動的	1,036
3	RerunnableBatch_sample3	静的	522
4	Transact-SQL > Cursor		582
5	Transact-SQL > SELECT > INSERT		11
測定モデル\データ件数	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