Test SQL statements

-------------------------1. Build Library-------------------------
02.USE [Master]
03.GO
04.if exists (SELECT * from sysdatabases where name= ' DB_TEST_MEMTB ')
DROP DATABASE DB_TEST_MEMTB
06.go
07.CREATE DATABASE [DB_TEST_MEMTB]
. On PRIMARY
09. (
NAME = N ' Db_test_memtb_data ',
FILENAME = N ' E:\db\test\DB_TEST_MEMTB_DATA.mdf ',
SIZE = 512000KB,
MAXSIZE = UNLIMITED,
FileGrowth = 1024KB
15.),
16.--The following file is the data stream file.
17.FILEGROUP [Mem_dir] CONTAINS memory_optimized_data DEFAULT
18. (
NAME = N ' Db_test_memtb_dir ',
FILENAME =n ' E:\db\test\DB_TEST_MEMTB_DIR ',
MAXSIZE = UNLIMITED
22.)
23.LOG on
24. (
NAME = N ' Db_test_memtb_log ',
FILENAME = N ' E:\db\test\DB_TEST_MEMTB_LOG.ldf ',
. SIZE = 512000KB,
MAXSIZE = 2048GB,
FileGrowth = 1024KB
30.)
31.GO
-------------------------2. Build tables and natively compile stored procedures-------------------------
33.USE DB_TEST_MEMTB
34.GO
35.--1. Create a normal disk table
36.IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ T_disk] ') and type in (N ' U '))
A. DROP TABLE [dbo]. [T_disk]
38.GO
39.create table [T_disk]
40. (
c1 int NOT NULL primary key,
C2 NCHAR ($) NOT NULL
43.)
44.go
45.--2. Create a memory-optimized table (subsequent tests do not use a locally compiled stored procedure)
46.IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ T_MEM] ') and type in (N ' U '))
DROP TABLE [dbo]. [T_mem]
48.GO
49.create table [T_mem]
50. (
Wuyi C1 int NOT null primary key nonclustered hash with (bucket_count=10000000),
C2 nchar (.) NOT NULL
.) with (memory_optimized=on, durability = schema_and_data)
54.GO
55.--3.0 establishing a memory-optimized table (subsequent tests using the locally compiled stored procedure native_compilation)
56.IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ T_MEM_NC] ') and type in (N ' U '))
DROP TABLE [DBO].T_MEM_NC
58.GO
59.create Table T_mem_nc
60. (
c1 int NOT NULL primary key nonclustered hash with (bucket_count=10000000),
C2 nchar (.) NOT NULL
.) with (memory_optimized=on, durability = schema_and_data)
64.GO
65.--3.1 natively compiled stored procedure _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 '))
DROP PROCEDURE [dbo]. [Proc_t_mem_nc_insert]
68.GO
69.CREATE PROCEDURE [Proc_t_mem_nc_insert]
@rowcount int,
@c nchar (48)
72.WITH native_compilation, SCHEMABINDING, EXECUTE as OWNER
73.AS
A. BEGIN ATOMIC
(TRANSACTION Isolation level = SNAPSHOT, LANGUAGE = N ' us_english ')
DECLARE @i int = 1
77.
@i <= @rowcount
"Begin"
INSERT into [DBO].T_MEM_NC values (@i, @c)
Bayi. Set @i + = 1
The. End
83.END
84.GO
85.--3.2 Natively compiled stored procedure _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 '))
A. DROP PROCEDURE [dbo]. [Proc_t_mem_nc_delete]
88.GO
89.CREATE PROCEDURE [Proc_t_mem_nc_delete]
@rowcount int
91.WITH native_compilation, SCHEMABINDING, EXECUTE as OWNER
92.AS
ATOMIC. BEGIN
94. With (TRANSACTION isolation level = SNAPSHOT, LANGUAGE = N ' us_english ')
DECLARE @i INT = 1
. While @i<[email protected]
The "Begin"
98. DELETE from DBO.T_MEM_NC WHERE [email protected]
Set @i + = 1
. end
101.END
102.GO
103.--3.3 Natively compiled stored procedure _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 '))
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
ATOMIC. BEGIN
113. With (TRANSACTION isolation level = SNAPSHOT, LANGUAGE = N ' us_english ')
DECLARE @i INT = 1
@i<[email protected]
A. Begin
117. UPDATE DBO.T_MEM_NC SET [email protected] WHERE [email protected]
118. Set @i + = 1
119. End
120.END
121.GO
122.--3.4 Natively compiled stored procedure _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
TRANSACTION Isolation level = SNAPSHOT, LANGUAGE = N ' us_english ')
131. SELECT C1,C2 from DBO.T_MEM_NC
132.END
133.GO
134.-------------------------3. Efficiency Evaluation-------------------------
135.DECLARE @i int=1, @iMax INT = 1000000--MAX 1 million records
136.DECLARE @v NCHAR (48) = ' 123456789012345678901234567890123456789012345678 '
137.DECLARE @t DATETIME2 = Sysdatetime ()
138.--3.1 Insert
139.--
140.set Nocount ON
141.while @i<[email protected]
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), DateDiff (MS, @t, Sysdatetime ()))
147.--
148.set @i=1
149.set @t=sysdatetime ()
150.while @i<[email protected]
151.begin
Insert into T_mem (C1,C2) VALUES (@i, @v)
153. Set @i+=1
154.end
155.select ' Insert (T_MEM): ' + convert (varchar), DateDiff (MS, @t, Sysdatetime ()))
156.--
157.set @t=sysdatetime ()
158.exec [Proc_t_mem_nc_insert]
159. @[email protected],
@[email protected]
161.select ' Insert (T_MEM_NC): ' + convert (varchar), DateDiff (MS, @t, Sysdatetime ()))
162.--results:
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.--time is longer, so the segment executes another variable
173.DECLARE @u int=1, @uMax INT = 1000000--MAX 1 million records
174.DECLARE @uv NCHAR () = ' 1234567890123456789012345678901234567890abcdefgh '
175.DECLARE @ut DATETIME2 = Sysdatetime ()
176.set Nocount ON
177.while @u<[email protected]
178.begin
179. Update T_disk set [email protected] where [email protected]
Set @u+=1
181.end
182.select ' Update (t_disk): ' + convert (varchar), DateDiff (MS, @ut, Sysdatetime ()))
183.--
184.set @u=1
185.set @ut =sysdatetime ()
186.while @u<[email protected]
187.begin
188. Update T_MEM set [email protected] where [email protected]
189. Set @u+=1
190.end
191.select ' Update (T_MEM): ' + convert (varchar), DateDiff (MS, @ut, Sysdatetime ()))
192.--
193.set @ut =sysdatetime ()
194.exec [Proc_t_mem_nc_update]
195. @[email protected],
196. @[email Protected]
197.select ' Update (T_MEM_NC): ' + convert (varchar), 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), DateDiff (MS, @st, Sysdatetime ()))
212.set @st =sysdatetime ()
213.select C1,C2 from T_mem
214.select ' Select (T_MEM): ' + convert (varchar), DateDiff (MS, @st, Sysdatetime ()))
215.set @st =sysdatetime ()
216.exec Proc_t_mem_nc_select
217.select ' Select (T_MEM_NC): ' + convert (varchar), 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.--time is longer, so the segment executes another variable
228.DECLARE @d int=1, @dMax INT = 1000000--MAX 1 million records
229.DECLARE @dt DATETIME2 = Sysdatetime ()
230.set Nocount ON
231.while @d<[email protected]
232.begin
233. Delete from t_disk where [email protected]
234. Set @d+=1
235.end
236.select ' Delete (t_disk): ' + convert (varchar), DateDiff (MS, @dt, Sysdatetime ()))
237.--
238.set @d=1
239.set @dt =sysdatetime ()
240.while @d<[email protected]
241.begin
242. Delete from t_mem where [email protected]
243. Set @d+=1
244.end
245.select ' Delete (t_mem): ' + convert (varchar), DateDiff (MS, @dt, Sysdatetime ()))
246.--
247.set @dt =sysdatetime ()
248.exec [dbo]. [Proc_t_mem_nc_delete] @[email protected]
249.select ' Delete (T_MEM_NC): ' + convert (varchar), 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

Test SQL statements