1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
use tempdb;
set ansi_nulls, quoted_identifier, xact_abort on;
set nocount on;
go
create table dbo.s (id int, a int, b nvarchar(100), c nchar(2000));
go
insert into dbo.s
(id, a, b, c)
select top (100000)
row_number() over (order by 1/0), 1, cast(newid() as nvarchar(100)), replicate(N'a', 2000)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;
go
select top (0) * into dbo.t1 from dbo.s;
select top (0) * into dbo.t2 from dbo.s;
select top (0) * into dbo.t3 from dbo.s;
select top (0) * into dbo.t4 from dbo.s;
select top (0) * into dbo.t5 from dbo.s;
go
declare @q nvarchar(max), @dt datetime2, @sx nvarchar(max), @x xml, @sj nvarchar(max), @s nvarchar(max);
declare @result table (descr nvarchar(30), rows_affected bigint, [elapsed_time (ms)] int);
select
@q = N'begin tran; ' + t.x.value('.', 'nvarchar(max)') + N' commit;'
from
(
select
N'insert into dbo.t1 (id, a, b, c) values (' + cast(id as nvarchar(10)) + N', ' + cast(a as nvarchar(10)) + N', ''' + b + ''', ''' + c + ''');'
from
dbo.s
for xml path(''), type
) t(x);
declare @t table (q nvarchar(max));
insert into @t
(q)
select
b.q
from
(select top (100) row_number() over (order by 1/0) - 1 from master.dbo.spt_values) a(n) cross apply
(
select top (1000)
N'insert into dbo.t3 (id, a, b, c) values (' + cast(id as nvarchar(10)) + N', ' + cast(a as nvarchar(10)) + N', ''' + b + ''', ''' + c + ''');'
from
dbo.s
where
id > a.n * 1000
for xml path(''), type
) t(x) cross apply
(select N'begin tran; ' + t.x.value('.', 'nvarchar(max)') + N' commit;') b(q)
where
b.q is not null;
set @dt = sysdatetime();
exec(@q);
insert into @result
(descr, rows_affected, [elapsed_time (ms)])
select N'footcloth of inserts', count(*), datediff(ms, @dt, sysdatetime()) from dbo.t1;
select
@sx = t.x
from
(
select
id as [@id], a as [@a], b as [@b], c as [@c]
from
dbo.s
for xml path('item')--, type
) t(x);
set @dt = sysdatetime();
set @x = @sx;
insert into dbo.t2
(id, a, b, c)
select
t.n.value('@id', 'int'),
t.n.value('@a', 'int'),
t.n.value('@b', 'nvarchar(100)'),
t.n.value('@c', 'nchar(2000)')
from
@x.nodes('item') t(n);
insert into @result
(descr, rows_affected, [elapsed_time (ms)])
select N'xml', count(*), datediff(ms, @dt, sysdatetime()) from dbo.t2;
set @x = NULL;
set @dt = sysdatetime();
declare @i as int;
set @x = '<root>' + @sx + '</root>';
exec sp_xml_preparedocument @i output, @x;
insert into dbo.t4
(id, a, b, c)
select id, a, b, c
FROM OPENXML (@i, '/root/item', 1)
With (id int '@id',
a int '@a',
b nvarchar(100) '@b',
c nchar(2000) '@c'
);
exec sp_xml_removedocument @i
insert into @result
(descr, rows_affected, [elapsed_time (ms)])
select N'OPENXML', count(*), datediff(ms, @dt, sysdatetime()) from dbo.t4;
select
@sj = t.x
from
(
select
id as [id], a as [a], b as [b], c as [c]
from
dbo.s
for json auto
) t(x);
set @dt = sysdatetime();
set @s = @sj;
insert into dbo.t5
(id, a, b, c)
select id, a, b, c
FROM OPENJSON (@s)
With (id int '$.id',
a int '$.a',
b nvarchar(100) '$.b',
c nchar(2000) '$.c'
);
insert into @result
(descr, rows_affected, [elapsed_time (ms)])
select N'json', count(*), datediff(ms, @dt, sysdatetime()) from dbo.t5;
declare t cursor local fast_forward for
select q from @t;
open t;
set @dt = sysdatetime();
while 1 = 1
begin
fetch next from t into @q;
if @@fetch_status <> 0
break;
exec(@q);
end;
close t;
deallocate t;
insert into @result
(descr, rows_affected, [elapsed_time (ms)])
select N'batches of inserts', count(*), datediff(ms, @dt, sysdatetime()) from dbo.t3;
select * from @result order by [elapsed_time (ms)];
go
drop table dbo.s, dbo.t1, dbo.t2, dbo.t3, dbo.t4, dbo.t5;
go
