首页 > 代码库 > 测试sql语句
测试sql语句
------------------------- 1. 建库 -------------------------
02.USE [master]
03.GO
04.if exists(select * from sysdatabases where name=‘DB_TEST_MEMTB‘)
05. DROP DATABASE DB_TEST_MEMTB
06.go
07.CREATE DATABASE [DB_TEST_MEMTB]
08. ON PRIMARY
09.(
10. NAME = N‘DB_TEST_MEMTB_DATA‘,
11. FILENAME = N‘e:\db\test\DB_TEST_MEMTB_DATA.mdf‘,
12. SIZE = 512000KB,
13. MAXSIZE = UNLIMITED,
14. FILEGROWTH = 1024KB
15.),
16.--下面的文件就是数据流文件了
17.FILEGROUP [MEM_DIR] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
18.(
19. NAME = N‘DB_TEST_MEMTB_DIR‘,
20. FILENAME =N‘e:\db\test\DB_TEST_MEMTB_DIR‘,
21. MAXSIZE = UNLIMITED
22.)
23.LOG ON
24.(
25. NAME = N‘DB_TEST_MEMTB_LOG‘,
26. FILENAME = N‘e:\db\test\DB_TEST_MEMTB_LOG.ldf‘,
27. SIZE = 512000KB,
28. MAXSIZE = 2048GB,
29. FILEGROWTH = 1024KB
30.)
31.GO
32.------------------------- 2. 建表和本地编译存储过程 -------------------------
33.USE DB_TEST_MEMTB
34.GO
35.-- 1. 建立普通磁盘表
36.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[t_disk]‘) AND type in (N‘U‘))
37. DROP TABLE [dbo].[t_disk]
38.GO
39.create table [t_disk]
40.(
41. c1 int not null primary key,
42. c2 nchar(48) not null
43.)
44.go
45.-- 2. 建立内存优化表 (后面的测试不使用本地编译存储过程)
46.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[t_mem]‘) AND type in (N‘U‘))
47. DROP TABLE [dbo].[t_mem]
48.GO
49.create table [t_mem]
50.(
51. c1 int not null primary key nonclustered hash with (bucket_count=10000000),
52. c2 nchar(48) not null
53.) with (memory_optimized=on, durability = schema_and_data)
54.GO
55.-- 3.0 建立内存优化表 (后面的测试使用本地编译存储过程 NATIVE_COMPILATION)
56.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[t_mem_nc]‘) AND type in (N‘U‘))
57. DROP TABLE [dbo].t_mem_nc
58.GO
59.create table t_mem_nc
60.(
61. c1 int not null primary key nonclustered hash with (bucket_count=10000000),
62. c2 nchar(48) not null
63.) with (memory_optimized=on, durability = schema_and_data)
64.GO
65.-- 3.1 本地编译存储过程_insert
66.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[Proc_t_mem_nc_Insert]‘) AND type in (N‘P‘, N‘PC‘))
67. DROP PROCEDURE [dbo].[Proc_t_mem_nc_Insert]
68.GO
69.CREATE PROCEDURE [Proc_t_mem_nc_Insert]
70. @rowcount int,
71. @c nchar(48)
72.WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
73.AS
74. BEGIN ATOMIC
75. WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘us_english‘)
76. declare @i int = 1
77.
78. while @i <= @rowcount
79. begin
80. INSERT INTO [dbo].t_mem_nc values (@i, @c)
81. set @i += 1
82. end
83.END
84.GO
85.-- 3.2 本地编译存储过程_delete
86.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[Proc_t_mem_nc_delete]‘) AND type in (N‘P‘, N‘PC‘))
87. DROP PROCEDURE [dbo].[Proc_t_mem_nc_delete]
88.GO
89.CREATE PROCEDURE [Proc_t_mem_nc_delete]
90. @rowcount int
91.WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
92.AS
93. BEGIN ATOMIC
94. WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘us_english‘)
95. DECLARE @i INT = 1
96. while @i<=@rowcount
97. begin
98. DELETE FROM dbo.t_mem_nc WHERE c1=@i
99. set @i += 1
100. end
101.END
102.GO
103.-- 3.3 本地编译存储过程_update
104.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[Proc_t_mem_nc_update]‘) AND type in (N‘P‘, N‘PC‘))
105. DROP PROCEDURE [dbo].[Proc_t_mem_nc_update]
106.GO
107.CREATE PROCEDURE [Proc_t_mem_nc_update]
108. @rowcount INT,
109. @c nchar(48)
110.WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
111.AS
112. BEGIN ATOMIC
113. WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘us_english‘)
114. DECLARE @i INT = 1
115. while @i<=@rowcount
116. begin
117. UPDATE dbo.t_mem_nc SET c2=@c WHERE c1=@i
118. set @i += 1
119. end
120.END
121.GO
122.-- 3.4 本地编译存储过程_select
123.IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[Proc_t_mem_nc_select]‘) AND type in (N‘P‘, N‘PC‘))
124. DROP PROCEDURE [dbo].[Proc_t_mem_nc_select]
125.GO
126.CREATE PROCEDURE [Proc_t_mem_nc_select]
127.WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
128.AS
129. BEGIN ATOMIC
130. WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N‘us_english‘)
131. SELECT c1,c2 FROM dbo.t_mem_nc
132.END
133.GO
134.------------------------- 3. 效率评测 -------------------------
135.DECLARE @i INT=1,@iMax INT = 1000000 --最大一百万条记录
136.DECLARE @v NCHAR(48)=‘123456789012345678901234567890123456789012345678‘
137.DECLARE @t DATETIME2 = sysdatetime()
138.--3.1 insert
139.--
140.set nocount on
141.while @i<=@iMax
142.begin
143. insert into t_disk (c1,c2) values(@i, @v)
144. set @i+=1
145.end
146.select ‘insert (t_disk): ‘+ convert(varchar(10), datediff(ms, @t, sysdatetime()))
147.--
148.set @i=1
149.set @t=SYSDATETIME()
150.while @i<=@iMax
151.begin
152. insert into t_mem (c1,c2) values(@i, @v)
153. set @i+=1
154.end
155.select ‘insert (t_mem): ‘+ convert(varchar(10), datediff(ms, @t, sysdatetime()))
156.--
157.set @t=SYSDATETIME()
158.exec [Proc_t_mem_nc_Insert]
159. @rowcount=@iMax,
160. @c=@v
161.select ‘insert (t_mem_nc): ‘+ convert(varchar(10), datediff(ms, @t, sysdatetime()))
162.--结果：
163.--insert (t_disk): 242111
164.--insert (t_mem): 221358
165.--insert (t_mem_nc): 2147
166.
167.--insert (t_disk): 243174
168.--insert (t_mem): 223465
169.--insert (t_mem_nc): 2214
170.
171.--3.2 update
172.--时间较长，故分段执行另设变量
173.DECLARE @u INT=1,@uMax INT = 1000000 --最大一百万条记录
174.DECLARE @uv NCHAR(48)=‘1234567890123456789012345678901234567890abcdefgh‘
175.DECLARE @ut DATETIME2 = sysdatetime()
176.set nocount on
177.while @u<=@uMax
178.begin
179. update t_disk set c2=@uv where c1=@u
180. set @u+=1
181.end
182.select ‘update (t_disk): ‘+ convert(varchar(10), datediff(ms, @ut, sysdatetime()))
183.--
184.set @u=1
185.set @ut=SYSDATETIME()
186.while @u<=@uMax
187.begin
188. update t_mem set c2=@uv where c1=@u
189. set @u+=1
190.end
191.select ‘update (t_mem): ‘+ convert(varchar(10), datediff(ms, @ut, sysdatetime()))
192.--
193.set @ut=SYSDATETIME()
194.exec [Proc_t_mem_nc_Update]
195. @rowcount=@uMax,
196. @c=@uv
197.select ‘update (t_mem_nc): ‘+ convert(varchar(10), datediff(ms, @ut, sysdatetime()))
198.--update (t_disk): 199369
199.--update (t_mem): 368297
200.--update (t_mem_nc): 3715
201.
202.--update (t_disk): 203251
203.--update (t_mem): 355356
204.--update (t_mem_nc): 3732
205.
206.--3.3 select
207.DECLARE @st DATETIME2 = sysdatetime()
208.set nocount on
209.--
210.select c1,c2 from t_disk
211.select ‘select (t_disk): ‘+ convert(varchar(10), datediff(ms, @st, sysdatetime()))
212.set @st=SYSDATETIME()
213.select c1,c2 from t_mem
214.select ‘select (t_mem): ‘+ convert(varchar(10), datediff(ms, @st, sysdatetime()))
215.set @st=SYSDATETIME()
216.exec Proc_t_mem_nc_select
217.select ‘select (t_mem_nc): ‘+ convert(varchar(10), datediff(ms, @st, sysdatetime()))
218.--select (t_disk): 8934
219.--select (t_mem): 9278
220.--select (t_mem_nc): 8889
221.
222.--select (t_disk): 8861
223.--select (t_mem): 9978
224.--select (t_mem_nc): 9108
225.
226.--3.4 delete
227.--时间较长，故分段执行另设变量
228.DECLARE @d INT=1,@dMax INT = 1000000 --最大一百万条记录
229.DECLARE @dt DATETIME2 = sysdatetime()
230.set nocount on
231.while @d<=@dMax
232.begin
233. delete from t_disk where c1=@d
234. set @d+=1
235.end
236.select ‘delete (t_disk): ‘+ convert(varchar(10), datediff(ms, @dt, sysdatetime()))
237.--
238.set @d=1
239.set @dt=SYSDATETIME()
240.while @d<=@dMax
241.begin
242. delete from t_mem where c1=@d
243. set @d+=1
244.end
245.select ‘delete (t_mem): ‘+ convert(varchar(10), datediff(ms, @dt, sysdatetime()))
246.--
247.set @dt=SYSDATETIME()
248.exec [dbo].[Proc_t_mem_nc_delete] @rowcount=@dMax
249.select ‘delete (t_mem_nc): ‘+ convert(varchar(10), datediff(ms, @dt, sysdatetime()))
250.
251.--delete (t_disk): 199438
252.--delete (t_mem): 342959
253.--delete (t_mem_nc): 928
254.
255.--delete (t_disk): 199637
256.--delete (t_mem): 341771
257.--delete (t_mem_nc): 803
测试sql语句