· 5 years ago · Apr 07, 2020, 07:18 AM
1/*******************************************************************
2 * Script: JackRabbitOAK8977.sql
3 * By: Solomon Rutzky ( https://SqlQuantumLeap.com/ )
4 * On: 2020-03-29
5 * Issue: RDBBlobStore performance improvement for SQL Server (MSSQL)
6 * https://issues.apache.org/jira/browse/OAK-8977
7 *******************************************************************/
8
9GO
10-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
11SET PARSEONLY ON;
12GO
13
14-- add RDBBlobStore performance test ( https://issues.apache.org/jira/browse/OAK-8926 )
15-- http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-store-document/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBBlobStoreDB.java?revision=1874271&view=markup
16
17
18
19--------------------------------------------------------------------------------
20--- BEGIN SETUP
21--- Highlight down to "END SETUP" and execute
22--------------------------------------------------------------------------------
23
24
25
26USE [tempdb];
27
28---------------------------------------------------------
29-- DROP TABLE dbo.[DATASTORE_DATA];
30-- DROP TABLE dbo.[DATASTORE_META];
31
32CREATE TABLE dbo.[DATASTORE_DATA]
33(
34 [ID] VARCHAR(64) COLLATE Latin1_General_CI_AS NOT NULL, -- Scenario = 1
35 [DATA] VARBINARY(MAX),
36 CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
37);
38
39CREATE TABLE dbo.[DATASTORE_META]
40(
41 [ID] VARCHAR(64) COLLATE Latin1_General_CI_AS NOT NULL, -- Scenario = 1
42 [LVL] INT,
43 [LASTMOD] BIGINT,
44 CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
45);
46---------------------------------------------------------
47GO
48IF (OBJECT_ID(N'dbo.StoreBlockInDatabase') IS NULL)
49BEGIN
50 EXEC(N'CREATE PROCEDURE dbo.[StoreBlockInDatabase] AS SET NOCOUNT ON;');
51END;
52
53GO
54ALTER PROCEDURE dbo.[StoreBlockInDatabase]
55(
56 @Digest VARBINARY(32),
57 @Level INT,
58 @Data VARBINARY(MAX)
59)
60AS
61SET NOCOUNT ON;
62
63DECLARE @Id VARCHAR(64) = CONVERT(VARCHAR(64), @Digest, 2),
64 @Now BIGINT;
65
66SELECT @Now = [cpu_ticks]
67FROM sys.dm_os_sys_info;
68
69BEGIN TRY
70 UPDATE dbo.[DATASTORE_META]
71 SET [LASTMOD] = @Now
72 WHERE [ID] = @Id;
73
74 IF (@@ROWCOUNT = 0)
75 BEGIN
76 INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@Id, @Data);
77 INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@Id, @Level, @Now);
78 END;
79END TRY
80BEGIN CATCH
81 ;THROW;
82END CATCH;
83
84GO
85---------------------------------------------------------
86GO
87IF (OBJECT_ID(N'dbo.StoreBlockInDatabase2') IS NULL)
88BEGIN
89 EXEC(N'CREATE PROCEDURE dbo.[StoreBlockInDatabase2] AS SET NOCOUNT ON;');
90END;
91
92GO
93ALTER PROCEDURE dbo.[StoreBlockInDatabase2]
94(
95 @Digest VARBINARY(32),
96 @Level INT,
97 @Data VARBINARY(MAX)
98)
99AS
100SET NOCOUNT ON;
101
102DECLARE @Id VARCHAR(64) = CONVERT(VARCHAR(64), @Digest, 2),
103 @Now BIGINT;
104
105SELECT @Now = [cpu_ticks]
106FROM sys.dm_os_sys_info;
107
108BEGIN TRY
109 IF (EXISTS(
110 SELECT *
111 FROM dbo.[DATASTORE_META]
112 WHERE [ID] = @Id
113 ))
114 BEGIN
115 UPDATE dbo.[DATASTORE_META]
116 SET [LASTMOD] = @Now
117 WHERE [ID] = @Id;
118 END;
119 ELSE
120 BEGIN
121 INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@Id, @Data);
122 INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@Id, @Level, @Now);
123 END;
124END TRY
125BEGIN CATCH
126 ;THROW;
127END CATCH;
128
129GO
130---------------------------------------------------------
131GO
132IF (OBJECT_ID(N'tempdb..#RunTests') IS NULL)
133BEGIN
134 EXEC(N'CREATE PROCEDURE #RunTests AS SET NOCOUNT ON;');
135END;
136
137GO
138ALTER PROCEDURE #RunTests
139(
140 @Iterations TINYINT = 10,
141 @BlockSize INT = 1500,
142 @DurationMS INT = 2000,
143 @SingleTestPattern TINYINT = NULL
144)
145AS
146SET NOCOUNT ON;
147
148IF (OBJECT_ID(N'dbo.[TestResults]') IS NULL)
149BEGIN
150 -- DROP TABLE dbo.[TestResults];
151 CREATE TABLE dbo.[TestResults]
152 (
153 [TestResultsID] INT NOT NULL
154 IDENTITY(1, 1)
155 CONSTRAINT [PK_TestResults] PRIMARY KEY,
156 [TestTime] DATETIME2 NOT NULL
157 CONSTRAINT [DF_TestResults_TestTime] DEFAULT (SYSDATETIME()),
158 [Scenario] TINYINT NOT NULL,
159 [TestPattern] TINYINT NOT NULL,
160 [RowsInserted] INT NOT NULL,
161 [TotalRows] INT NOT NULL
162 );
163END;
164
165
166DECLARE @TestPattern TINYINT = 1;
167
168DECLARE @Iteration TINYINT = 0,
169 @RowCount INT = 0,
170 @EndTime DATETIME2,
171 @DATA VARBINARY(MAX),
172 @Digest BINARY(32),
173 @ID VARCHAR(64),
174 @SQL NVARCHAR(MAX),
175 @Scenario TINYINT;
176
177-- NOTE: Does not yet handle scenario 3!!
178SELECT @Scenario = CASE col.[collation_name]
179 WHEN N'Latin1_General_CI_AS' THEN 1
180 WHEN N'Latin1_General_BIN2' THEN 2
181 ELSE 0
182 END
183FROM sys.columns col
184WHERE col.[object_id] = OBJECT_ID(N'dbo.DATASTORE_META')
185AND col.[name] = N'ID';
186
187
188WHILE (@TestPattern < 7)
189BEGIN
190 SET @Iteration = 0;
191
192 -- Execute the following 2 statements when changing either TestPattern or Scenario (for consistency):
193 TRUNCATE TABLE dbo.[DATASTORE_DATA];
194 TRUNCATE TABLE dbo.[DATASTORE_META];
195
196 WHILE (@Iteration < @Iterations)
197 BEGIN
198 SET @RowCount = 0;
199 SET @EndTime = DATEADD(MILLISECOND, @DurationMS, SYSDATETIME());
200
201 WHILE (SYSDATETIME() < @EndTime)
202 BEGIN
203 SET @DATA = CRYPT_GEN_RANDOM(@BlockSize);
204
205 SET @Digest = HASHBYTES('SHA2_256', @DATA);
206
207 SET @ID = CONVERT(VARCHAR(64), @Digest, 2);
208
209
210 BEGIN TRAN; -- I think the JDBC code is doing this
211
212 IF (@TestPattern = 1)
213 BEGIN
214 UPDATE dbo.[DATASTORE_META]
215 SET [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
216 WHERE [ID] = @ID;
217
218 IF (@@ROWCOUNT = 0)
219 BEGIN
220 INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@ID, @DATA);
221 INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@ID, 0, CONVERT(INT, GETDATE()));
222 END;
223 END;
224
225
226 IF (@TestPattern = 2)
227 BEGIN
228 IF (EXISTS(
229 SELECT *
230 FROM dbo.[DATASTORE_META]
231 WHERE [ID] = @ID
232 ))
233 BEGIN
234 UPDATE dbo.[DATASTORE_META]
235 SET [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
236 WHERE [ID] = @ID;
237 END;
238 ELSE
239 BEGIN
240 INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@ID, @DATA);
241 INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@ID, 0, CONVERT(INT, GETDATE()));
242 END;
243 END;
244
245
246 IF (@TestPattern = 3)
247 BEGIN
248 SET @SQL = N'
249 UPDATE dbo.[DATASTORE_META]
250 SET [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
251 WHERE [ID] = ''' + @ID + N''';
252
253 IF (@@ROWCOUNT = 0)
254 BEGIN
255 INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (''' + @ID + N''', ' + CONVERT(NVARCHAR(MAX), @DATA, 1) + N');
256 INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (''' + @ID + N''', 0, CONVERT(INT, GETDATE()));
257 END;
258 ';
259
260 EXEC(@SQL);
261 END;
262
263
264 IF (@TestPattern = 4)
265 BEGIN
266
267 SET @SQL = N'EXEC dbo.[StoreBlockInDatabase] 0x' + @ID + N', 0, ' + CONVERT(NVARCHAR(MAX), @DATA, 1) + N';';
268
269 EXEC(@SQL);
270 END;
271
272
273 IF (@TestPattern = 5)
274 BEGIN
275 EXEC sp_executesql
276 N'EXEC dbo.[StoreBlockInDatabase] @tmpDigest, 0, @tmpData;',
277 N'@tmpDigest VARBINARY(32), @tmpData VARBINARY(MAX)',
278 @tmpDigest = @Digest,
279 @tmpData = @DATA;
280 END;
281
282
283 IF (@TestPattern = 6)
284 BEGIN
285 EXEC sp_executesql
286 N'EXEC dbo.[StoreBlockInDatabase2] @tmpDigest, 0, @tmpData;',
287 N'@tmpDigest VARBINARY(32), @tmpData VARBINARY(MAX)',
288 @tmpDigest = @Digest,
289 @tmpData = @DATA;
290 END;
291
292
293 COMMIT TRAN; -- I think the JDBC code is doing this
294
295 SET @RowCount += 1;
296 END;
297
298 INSERT INTO dbo.[TestResults] ([Scenario], [TestPattern], [RowsInserted], [TotalRows])
299 OUTPUT INSERTED.*
300 SELECT @Scenario, @TestPattern, @RowCount, COUNT(*)
301 FROM dbo.[DATASTORE_DATA];
302
303 SET @Iteration += 1;
304
305 WAITFOR DELAY '00:00:00.500'; -- half-second delay
306 END;
307
308 SET @TestPattern += 1;
309END;
310GO
311
312--------------------------------------------------------------------------------
313--- END SETUP
314--------------------------------------------------------------------------------
315
316
317-- Exec test proc, apply Scenario 2 changes, then exec test proc again:
318
319EXEC #RunTests;
320
321
322----------------------------------------
323-- Scenario = 2 (change collation to binary)
324
325ALTER TABLE [DATASTORE_DATA] DROP CONSTRAINT [DATASTORE_DATA_PK];
326ALTER TABLE [DATASTORE_DATA] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
327ALTER TABLE [DATASTORE_DATA] ADD CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY CLUSTERED ([ID] ASC);
328
329ALTER TABLE [DATASTORE_META] DROP CONSTRAINT [DATASTORE_META_PK];
330ALTER TABLE [DATASTORE_META] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
331ALTER TABLE [DATASTORE_META] ADD CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY CLUSTERED ([ID] ASC);
332----------------------------------------
333
334EXEC #RunTests;
335
336
337
338/* -- Highlight and execute as necessary:
339
340SELECT * FROM dbo.[DATASTORE_DATA];
341SELECT * FROM dbo.[DATASTORE_META];
342
343DELETE FROM dbo.[TestResults] WHERE [TestResultsID] = xyz;
344SELECT * FROM dbo.[TestResults];
345*/
346
347
348
349-- TestPattern 3 = close approximation of current code
350-- TestPattern 5 = proposed stored procedure approach
351SELECT [Scenario], [TestPattern],
352 COUNT([RowsInserted]) AS [TestCount],
353 MIN([RowsInserted]) AS [MinRows],
354 AVG([RowsInserted]) AS [AvgRows],
355 MAX([RowsInserted]) AS [MaxRows],
356 SUM([RowsInserted]) AS [TotalRows]
357FROM dbo.[TestResults]
358GROUP BY [Scenario], [TestPattern]
359ORDER BY [Scenario], [TestPattern];
360/*
361Scenario TestPattern TestCount MinRows AvgRows MaxRows TotalRows
3621 1 10 11605 13329 14583 133297
3631 2 10 11409 14522 16547 145221
3641 3 10 1681 2108 2322 21080
365
3662 1 10 12755 16064 17108 160640
3672 2 10 13959 15312 16927 153125
3682 3 10 2144 2422 2696 24228
3692 4 10 2238 3012 3802 30121
3702 5 10 963 4876 6626 48768
3712 6 10 907 4696 6532 46969
372*/
373
374
375
376----------------------------------------
377-- Scenario = 3 (keep binary collation, change PK to NONclustered)
378
379ALTER TABLE [DATASTORE_DATA] DROP CONSTRAINT [DATASTORE_DATA_PK];
380ALTER TABLE [DATASTORE_DATA] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
381ALTER TABLE [DATASTORE_DATA] ADD CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC);
382
383ALTER TABLE [DATASTORE_META] DROP CONSTRAINT [DATASTORE_META_PK];
384ALTER TABLE [DATASTORE_META] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
385ALTER TABLE [DATASTORE_META] ADD CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC);
386----------------------------------------
387
388GO
389SET PARSEONLY OFF;
390GO