SHARE
TWEET

Jackrabbit OAK-8977 (Improve performance)

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