-- -- Table for generating unique values -- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[timestamps] ( [spid] [int] NOT NULL , [junk] [smallint] NOT NULL , [tms] [timestamp] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[timestamps] ADD CONSTRAINT [timestamps_XPK] PRIMARY KEY NONCLUSTERED ( [spid] ) ON [PRIMARY] GO -- -- Application tables -- CREATE TABLE [dbo].[Orders] ( [id] [varchar](20) NOT NULL , [doc_number] [int] NOT NULL , [doc_date] [datetime] NULL , [branch] [varchar](10) NOT NULL , [address] [varchar](255) NOT NULL , [payment_date] [datetime] NULL , [processed] [int] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrderDetails] ( [id] [varchar](20) NOT NULL , [code] [varchar](4) NOT NULL , [price] [decimal](15,2) NULL , [quantity] [int] NULL , [tax] [decimal](2) NOT NULL , [exp_date] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersXML_Attr] ( [id] [varchar](20) NOT NULL , [doc_number] [int] NOT NULL , [doc_date] [datetime] NULL , [branch] [varchar](10) NOT NULL , [address] [varchar](255) NOT NULL , [payment_date] [datetime] NULL , [processed] [int] NULL, [x] [xml] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersXML_Elem] ( [id] [varchar](20) NOT NULL , [doc_number] [int] NOT NULL , [doc_date] [datetime] NULL , [branch] [varchar](10) NOT NULL , [address] [varchar](255) NOT NULL , [payment_date] [datetime] NULL , [processed] [int] NULL, [x] [xml] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[OrdersXML_AllIn1] ( [id] [varchar](20) NOT NULL , [doc_number] [int] NOT NULL , [doc_date] [datetime] NULL , [branch] [varchar](10) NOT NULL , [address] [varchar](255) NOT NULL , [payment_date] [datetime] NULL , [processed] [int] NULL, [x] [xml] NULL ) ON [PRIMARY] GO -- Normal indexes ALTER TABLE OrdersXML_Attr ADD CONSTRAINT OrdersXML_Attr_XPK PRIMARY KEY (id) GO ALTER TABLE OrdersXML_Elem ADD CONSTRAINT OrdersXML_Elem_XPK PRIMARY KEY (id) GO CREATE UNIQUE INDEX Orders_XK1 on Orders (id) GO CREATE INDEX OrderDetails_XK1 on OrderDetails (id,code) GO ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO if exists (select * from sysobjects where id = object_id('dbo.RandTable2') and sysstat & 0xf = 4) drop procedure dbo.RandTable2 GO CREATE PROCEDURE RandTable2 ( @P_sTbName1 varchar(128), @P_sTbName2 varchar(128), @P_nRows int = 10, -- Rows to generate @P_nMDFactor int = 1000, -- Child rows to each primary row (random) @RP_nErrCode int = 0 OUT, @RP_sErrDescription varchar(1024) = null OUT ) AS set nocount on declare @sInsert1 varchar(2048) declare @sInsert2 varchar(2048) declare @sInsertSrc1 varchar(2048) declare @sInsertSrc2 varchar(2048) declare @sInsertAll1 varchar(8000) declare @sInsertAll2 varchar(8000) declare @i int declare @k int declare @e int declare @sNewIID varchar(20) declare @dtStart datetime declare @dtFinish datetime declare @nSec int set @dtStart = getdate() exec dbo.RandQuery @P_sTbName = @P_sTbName1, @RP_sQuery = @sInsertSrc1 OUT, @RP_nErrCode = @RP_nErrCode OUT, @RP_sErrDescription = @RP_sErrDescription OUT if (@RP_nErrCode < 0) return(0) exec dbo.RandQuery @P_sTbName = @P_sTbName2, @RP_sQuery = @sInsertSrc2 OUT, @RP_nErrCode = @RP_nErrCode OUT, @RP_sErrDescription = @RP_sErrDescription OUT if (@RP_nErrCode < 0) return(0) select @i = 1 while (@i <= @P_nRows) begin select @i = @i + 1 select @sNewIID = '' if (charindex('', @sInsertSrc1) > 0) exec dbo.NewID @sNewIID OUT exec dbo.ReplInsert @sInsertSrc1, @sInsert1 OUT select @sInsertAll1 = replace(@sInsert1, '', @sNewIID) exec(@sInsertAll1) set @k = 1 set @e = round(rand() * @P_nMDFactor, 0) while (@k < @e) begin set @k = @k + 1 exec dbo.ReplInsert @sInsertSrc2, @sInsert2 OUT select @sInsertAll2 = replace(@sInsert2, '', @sNewIID) exec(@sInsertAll2) end if (@i % 100 = 0) exec dbo.DoCommit if (@i % 1000 = 0) print @i end exec dbo.DoCommit set @dtFinish = getdate() set @nSec = datediff(ss, @dtStart, @dtFinish) if (@nSec = 0) print '? rows/sec' else print convert(varchar, @P_nRows / @nSec) + ' rows/sec' GO if exists (select * from sysobjects where id = object_id('dbo.ReplInsert') and sysstat & 0xf = 4) drop procedure dbo.ReplInsert GO CREATE PROCEDURE ReplInsert ( @P_sString varchar(2048), @RP_sRetValue varchar(2048) OUT, @RP_sXmlColumns varchar(1024) = null OUT ) AS declare @nInd int declare @nInd2 int declare @sMacro varchar(10) declare @sType varchar(1) declare @nLength int declare @sInsValue varchar(255) declare @sColumn varchar(255) -- insert into tb (iid, f1, f2, f3) values ( '{I}', {N3}, '{C20}', '{D}') select @RP_sRetValue = @P_sString while (1=1) begin select @nInd = charindex('{', @RP_sRetValue) if (@nInd = 0) break select @nInd2 = charindex('}', @RP_sRetValue, @nInd) select @sMacro = substring(@RP_sRetValue, @nInd, @nInd2 - @nInd + 1) select @sType = substring(@RP_sRetValue, @nInd + 1, 1) if (@sType = 'I') begin exec dbo.NewID @sInsValue OUT end else if (@sType = 'D') begin select @sInsValue = convert(varchar, dateadd(dd, round(rand() * 1000, 0), '01/01/2000'), 113) end else begin select @nLength = convert(smallint, substring(@RP_sRetValue, @nInd + 2, @nInd2 - @nInd - 2)) select @sInsValue = substring(convert(varchar(20), convert(decimal(18,16),rand())), 3, @nLength) end select @RP_sRetValue = replace(@RP_sRetValue, @sMacro, @sInsValue) end GO if exists (select * from sysobjects where id = object_id('dbo.RandQuery') and sysstat & 0xf = 4) drop procedure dbo.RandQuery GO CREATE PROCEDURE RandQuery ( @P_sTbName varchar(128), @RP_sQuery varchar(2048) OUT, @RP_nErrCode int = 0 OUT, @RP_sErrDescription varchar(1024) = null OUT ) AS declare @T_column_name sysname declare @T_prec smallint declare @T_scale smallint declare @T_status tinyint declare @T_db_datatype varchar(30) declare @T_db_datatypeU varchar(30) declare @sFields varchar(2048) declare @sValues varchar(2048) declare @sParamValue varchar(60) declare @sParent varchar(10) declare @nCounter int select @RP_nErrCode = 0 select @sParent = '''' + '???' + '''' if (left(@P_sTbName, 1) = '#') declare hC_columns cursor FAST_FORWARD LOCAL for select c.prec, c.scale, t.name, c.name, c.status from tempdb..syscolumns c, tempdb..systypes t where c.id = object_id('tempdb..' + @P_sTbName) and c.xtype = t.xtype and c.xusertype = t.xusertype order by c.colid else declare hC_columns cursor FAST_FORWARD LOCAL for select c.prec, c.scale, t.name, c.name, c.status, t2.name from dbo.syscolumns c, dbo.systypes t, dbo.systypes t2 where c.id = object_id(@P_sTbName) and c.xtype = t.xtype and c.xusertype = t.xusertype and t.xtype = t2.xtype and t2.xtype = t2.xusertype order by c.colid open hC_columns select @sFields = 'insert into ' + @P_sTbName + '(' select @sValues = ' values (' select @nCounter = 1 while (1=1) begin fetch next from hC_columns into @T_prec, @T_scale, @T_db_datatypeU, @T_column_name, @T_status, @T_db_datatype if (@@fetch_status = -1) break if (@@fetch_status = -2) continue if (@T_status & 0x80 > 0 or @T_db_datatype = 'uniqueidentifier') continue if (@T_column_name = 'id') select @sParamValue = '''' + '' + '''' else if (@T_db_datatype = 'datetime') select @sParamValue = '''' + '{D}' + '''' else if (@T_db_datatype = 'int') select @sParamValue = '{N4}' else if (@T_db_datatype = 'smallint') select @sParamValue = '{N2}' else if (@T_db_datatype = 'tinyint') select @sParamValue = '{N1}' else if (@T_db_datatype = 'decimal') select @sParamValue = '{N' + convert(varchar, @T_prec - @T_scale) + '}' else if (@T_db_datatype in ('char','varchar','nvarchar')) select @sParamValue = '''' + '{C' + convert(varchar, @T_prec) + '}' + '''' else select @sParamValue = 'null' if (@nCounter = 1) begin select @sFields = @sFields + @T_column_name select @sValues = @sValues + @sParamValue end else begin select @sFields = @sFields + ',' + @T_column_name select @sValues = @sValues + ',' + @sParamValue end select @nCounter = @nCounter + 1 end close hC_columns deallocate hC_columns select @RP_sQuery = @sFields + ')' + @sValues + ')' GO if exists (select * from sysobjects where id = object_id('dbo.NewID') and sysstat & 0xf = 4) drop procedure dbo.NewID GO CREATE PROCEDURE NewID ( @RP_sIID varchar(20) OUT ) AS set nocount on declare @dbts binary(8) declare @sBranch varchar(10) update timestamps with (ROWLOCK) set junk = 0 where spid = @@spid if (@@rowcount = 0) insert into timestamps with (ROWLOCK) (spid, junk) values (@@spid, 0) select @dbts = tms from timestamps with (NOLOCK) where spid = @@spid -- <<>> set @RP_sIID = '000' + right('0000000000000' + convert(varchar,convert(int, @dbts)) + right('000' + convert(varchar,@@spid), 3), 14) GO if exists (select * from sysobjects where id = object_id('dbo.DoCommit') and sysstat & 0xf = 4) drop procedure dbo.DoCommit GO CREATE PROCEDURE DoCommit AS while (@@trancount > 0) commit tran GO if exists (select * from sysobjects where id = object_id('dbo.DoRollback') and sysstat & 0xf = 4) drop procedure dbo.DoRollback GO CREATE PROCEDURE DoRollback AS if (@@trancount > 0) rollback tran GO if exists (select * from sysobjects where id = object_id('dbo.FillXml_Attr') and sysstat & 0xf = 4) drop procedure dbo.FillXml_Attr GO CREATE PROCEDURE FillXml_Attr AS set nocount on declare @id varchar(20) declare @x xml declare @i int truncate table OrdersXML_Attr insert into OrdersXML_Attr (id,doc_number,doc_date,branch,address,payment_date,processed, x) select id,doc_number,doc_date,branch,address,payment_date, null, null from Orders exec dbo.DoCommit declare hC cursor STATIC LOCAL for select id from OrdersXML_Attr where processed is null open hC set @i = 0 while (1=1) begin fetch next from hC into @id if (@@fetch_status = -1) break if (@@fetch_status = -2) continue set @i = @i + 1 if (@i % 1000 = 0) print @i if (@i % 100 = 0) exec DoCommit set @x = (select * from (select 1 as tag, null as parent, d.id as [Orders!1!id], null as [OrderDetails!2!code], null as [OrderDetails!2!price], null as [OrderDetails!2!quantity], null as [OrderDetails!2!tax], null as [OrderDetails!2!exp_date] from OrderDetails d where d.id = @id union select 2 as tag, 1 as parent, d.id, d.code, d.price, d.quantity, d.tax, d.exp_date from OrderDetails d where d.id = @id ) q order by [Orders!1!id],[OrderDetails!2!code] for xml explicit, type) update OrdersXML_Attr set x = @x, processed = 1 where id = @id end close hC deallocate hC GO if exists (select * from sysobjects where id = object_id('dbo.FillXml_Elem') and sysstat & 0xf = 4) drop procedure dbo.FillXml_Elem GO CREATE PROCEDURE FillXml_Elem AS set nocount on declare @id varchar(20) declare @x xml declare @i int truncate table OrdersXML_Elem insert into OrdersXML_Elem (id,doc_number,doc_date,branch,address,payment_date,processed,x) select id,doc_number,doc_date,branch,address,payment_date, null, null from Orders exec dbo.DoCommit declare hC cursor STATIC LOCAL for select id from OrdersXML_Elem where processed is null open hC set @i = 0 while (1=1) begin fetch next from hC into @id if (@@fetch_status = -1) break if (@@fetch_status = -2) continue set @i = @i + 1 if (@i % 1000 = 0) print @i if (@i % 100 = 0) exec dbo.DoCommit set @x = (select * from (select 1 as tag, null as parent, d.id as [Orders!1!id!ELEMENT], null as [OrderDetails!2!code!ELEMENT], null as [OrderDetails!2!price!ELEMENT], null as [OrderDetails!2!quantity!ELEMENT], null as [OrderDetails!2!tax!ELEMENT], null as [OrderDetails!2!exp_date!ELEMENT] from OrderDetails d where d.id = @id union select 2 as tag, 1 as parent, d.id, d.code, d.price, d.quantity, d.tax, d.exp_date from OrderDetails d where d.id = @id ) q order by [Orders!1!id!ELEMENT],[OrderDetails!2!code!ELEMENT] for xml explicit, type) update OrdersXML_Elem set x = @x, processed = 1 where id = @id end close hC deallocate hC GO if exists (select * from sysobjects where id = object_id('dbo.FillXml_AllIn1') and sysstat & 0xf = 4) drop procedure dbo.FillXml_AllIn1 GO CREATE PROCEDURE FillXml_AllIn1 AS set nocount on declare @x xml truncate table OrdersXML_AllIn1 set @x = (select * from (select 1 as tag, null as parent, d.id as [Orders!1!id!ELEMENT], null as [OrderDetails!2!code!ELEMENT], null as [OrderDetails!2!price!ELEMENT], null as [OrderDetails!2!quantity!ELEMENT], null as [OrderDetails!2!tax!ELEMENT], null as [OrderDetails!2!exp_date!ELEMENT] from OrderDetails d union select 2 as tag, 1 as parent, d.id, d.code, d.price, d.quantity, d.tax, d.exp_date from OrderDetails d ) q order by [Orders!1!id!ELEMENT],[OrderDetails!2!code!ELEMENT] for xml explicit, type) insert into OrdersXML_AllIn1 (id,doc_number,doc_date,branch,address,payment_date,processed,x) select '1', '00001', '01/01/2007', '000001', 'address', '01/01/2007', 1, @x exec dbo.DoCommit GO ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_WARNINGS ON SET ANSI_PADDING ON SET CONCAT_NULL_YIELDS_NULL ON GO truncate table Orders truncate table OrderDetails truncate table OrdersXML_Attr truncate table OrdersXML_Elem truncate table OrdersXML_AllIn1 GO exec dbo.RandTable2 'Orders', 'OrderDetails', 100, 100 exec dbo.RandTable2 'Orders', 'OrderDetails', 100, 200 exec dbo.RandTable2 'Orders', 'OrderDetails', 1000, 20 exec dbo.RandTable2 'Orders', 'OrderDetails', 1000, 10 exec dbo.RandTable2 'Orders', 'OrderDetails', 1000, 30 exec dbo.RandTable2 'Orders', 'OrderDetails', 1000, 40 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 10 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 20 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 30 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 40 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 50 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 30 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 40 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 10 exec dbo.RandTable2 'Orders', 'OrderDetails', 10000, 20 exec dbo.RandTable2 'Orders', 'OrderDetails', 100, 100 GO exec dbo.FillXml_Attr GO exec dbo.FillXml_Elem GO -- SQL custom indexes CREATE INDEX OrderDetails_U1 on OrderDetails (code, id) GO -- XML indexes (FOR VALUE) CREATE PRIMARY XML INDEX OrdersXML_Attr_PRIMARY on OrdersXML_Attr (x) GO CREATE XML INDEX OrdersXML_Attr_VALUE ON OrdersXML_Attr(x) USING XML INDEX OrdersXML_Attr_PRIMARY FOR VALUE GO CREATE PRIMARY XML INDEX OrdersXML_Elem_PRIMARY on OrdersXML_Elem (x) GO CREATE XML INDEX OrdersXML_Elem_VALUE ON OrdersXML_Elem(x) USING XML INDEX OrdersXML_Elem_PRIMARY FOR VALUE GO ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_WARNINGS ON SET ANSI_PADDING ON SET CONCAT_NULL_YIELDS_NULL ON GO CREATE XML INDEX OrdersXML_Attr_PATH ON OrdersXML_Attr(x) USING XML INDEX OrdersXML_Attr_PRIMARY FOR PATH GO CREATE XML INDEX OrdersXML_Elem_PATH ON OrdersXML_Elem(x) USING XML INDEX OrdersXML_Elem_PRIMARY FOR PATH GO