use wroxdb
go

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'perftest')
BEGIN
    DROP PROCEDURE perftest
END
GO
 
CREATE PROCEDURE perftest;1 @orderxml varchar(4000) 
AS 
DECLARE @hDoc int

	SET NOCOUNT ON

	EXEC sp_xml_preparedocument @hDoc OUTPUT, @orderxml

	UPDATE orders
	SET customer = x.customer, 
		adm_rev_no=orders.adm_rev_no+1,
		adm_time = getdate()
	FROM  OPENXML (@hdoc, '/order', 1) 
	WITH ( xact                         char(1), 
			order_no                       int,
			customer                       varchar(30),
			adm_rev_no                     int,
			adm_time                       datetime) AS x
	WHERE x.order_no = orders.order_no
	AND xact = 'U'

	EXEC sp_xml_removedocument @hDoc   
GO

CREATE PROCEDURE perftest;2 @order int, @cust varchar(30), @adm int
AS 
	SET NOCOUNT ON

	UPDATE orders
	SET customer = @cust,
		adm_rev_no= adm_rev_no+1,
		adm_time = getdate()
	WHERE order_no = @order
GO

CREATE PROCEDURE perftest;3 
AS 
	SET ROWCOUNT 1

	SELECT * FROM orders
GO

CREATE PROCEDURE perftest;4
AS
	SET ROWCOUNT 1

	SELECT * FROM orders
	FOR XML AUTO
GO


IF EXISTS (SELECT * FROM sysobjects WHERE name = 'perftest')
   PRINT  '>>> Created procedure perftest <<<'
GO

/* test

DECLARE @loop int
DECLARE @start datetime
DECLARE @ms  int

	SET NOCOUNT ON

	SET @loop = 100
	SET @start = getdate()
	
	WHILE @loop > 0
	BEGIN

		EXEC perftest;1 "<order xact='U' order_no='1000' customer='Alfred Lord Tennyson' adm_rev_no='2' adm_time='12 Mar 2001 13:38:04' />"
		--EXEC perftest;2 1000, 'Alfred Lord Tennyson', 2
	
		SET @loop = @loop - 1
	END

	SET @ms = DATEDIFF(ms, @start, getdate())

	PRINT @ms

-- results from Query Analyser
	test;1  420ms
	test;2  80

-- results from VB/ADO
				time   io
   test;1  320ms	 400
	test;2  120ms   200



*/

