Don't build-up XML instance types serially!
I'm half temped to start a "museum of dumb ideas I've had". Here's an example using YukonSQL Server 2005. This is why do not want to construct large XML documents in a serial fashion using the XML type...
declare @doc xml
declare @index int
declare @started datetime
declare @ended datetime
declare @order tinyint
declare @interations int
declare @dur float
set @order = 1
while @order < 33
begin
set @doc = '<root />'
set @doc.modify('insert <node id="{sql:variable("@index")}" /> as last into (/root)[1]')
set @started = GetDate()
set @interations = power(2.0,@order)
set @index = 0
while @index < @interations
begin
set @doc.modify('insert <node id="{sql:variable("@index")}" /> as last into (/root)[1]')
set @index = @index + 1
end
set @dur = datediff(ms,@started,GetDate())
select @order,@dur 'duration',@interations 'interations'
set @order = @order +1
end
Run it. First three people that report their execution times for any iterations above 24 get a free gmail invite.