/*******************************************************************
* Script: JackRabbitOAK8977.sql ( https://pastebin.com/csh4fy42 )
* By: Solomon Rutzky ( https://SqlQuantumLeap.com/ )
* On: 2020-03-29
* Issue: RDBBlobStore performance improvement for SQL Server (MSSQL)
* https://issues.apache.org/jira/browse/OAK-8977
* Purpose: Prove best combination is binary collation and stored proc.
*******************************************************************/
GO
-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY ON;
GO
-- add RDBBlobStore performance test ( https://issues.apache.org/jira/browse/OAK-8926 )
-- 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
--------------------------------------------------------------------------------
--- BEGIN SETUP
--- Highlight down to "END SETUP" and execute
--------------------------------------------------------------------------------
USE [tempdb];
---------------------------------------------------------
-- DROP TABLE dbo.[DATASTORE_DATA];
-- DROP TABLE dbo.[DATASTORE_META];
CREATE TABLE dbo.[DATASTORE_DATA]
(
[ID] VARCHAR(64) COLLATE Latin1_General_CI_AS NOT NULL, -- Scenario = 1
[DATA] VARBINARY(MAX),
CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
);
CREATE TABLE dbo.[DATASTORE_META]
(
[ID] VARCHAR(64) COLLATE Latin1_General_CI_AS NOT NULL, -- Scenario = 1
[LVL] INT,
[LASTMOD] BIGINT,
CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY CLUSTERED ([ID] ASC)
);
---------------------------------------------------------
GO
IF (OBJECT_ID(N'dbo.StoreBlockInDatabase') IS NULL)
BEGIN
EXEC(N'CREATE PROCEDURE dbo.[StoreBlockInDatabase] AS SET NOCOUNT ON;');
END;
GO
ALTER PROCEDURE dbo.[StoreBlockInDatabase]
(
@Digest VARBINARY(32),
@Level INT,
@Data VARBINARY(MAX)
)
AS
SET NOCOUNT ON;
DECLARE @Id VARCHAR(64) = CONVERT(VARCHAR(64), @Digest, 2),
@Now BIGINT;
SELECT @Now = [cpu_ticks]
FROM sys.dm_os_sys_info;
BEGIN TRY
UPDATE dbo.[DATASTORE_META]
SET [LASTMOD] = @Now
WHERE [ID] = @Id;
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@Id, @Data);
INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@Id, @Level, @Now);
END;
END TRY
BEGIN CATCH
;THROW;
END CATCH;
GO
---------------------------------------------------------
GO
IF (OBJECT_ID(N'dbo.StoreBlockInDatabase2') IS NULL)
BEGIN
EXEC(N'CREATE PROCEDURE dbo.[StoreBlockInDatabase2] AS SET NOCOUNT ON;');
END;
GO
ALTER PROCEDURE dbo.[StoreBlockInDatabase2]
(
@Digest VARBINARY(32),
@Level INT,
@Data VARBINARY(MAX)
)
AS
SET NOCOUNT ON;
DECLARE @Id VARCHAR(64) = CONVERT(VARCHAR(64), @Digest, 2),
@Now BIGINT;
SELECT @Now = [cpu_ticks]
FROM sys.dm_os_sys_info;
BEGIN TRY
IF (EXISTS(
SELECT *
FROM dbo.[DATASTORE_META]
WHERE [ID] = @Id
))
BEGIN
UPDATE dbo.[DATASTORE_META]
SET [LASTMOD] = @Now
WHERE [ID] = @Id;
END;
ELSE
BEGIN
INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@Id, @Data);
INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@Id, @Level, @Now);
END;
END TRY
BEGIN CATCH
;THROW;
END CATCH;
GO
---------------------------------------------------------
GO
IF (OBJECT_ID(N'tempdb..#RunTests') IS NULL)
BEGIN
EXEC(N'CREATE PROCEDURE #RunTests AS SET NOCOUNT ON;');
END;
GO
ALTER PROCEDURE #RunTests
(
@Iterations TINYINT = 10,
@BlockSize INT = 1500,
@DurationMS INT = 2000,
@SingleTestPattern TINYINT = NULL
)
AS
SET NOCOUNT ON;
IF (OBJECT_ID(N'dbo.[TestResults]') IS NULL)
BEGIN
-- DROP TABLE dbo.[TestResults];
CREATE TABLE dbo.[TestResults]
(
[TestResultsID] INT NOT NULL
IDENTITY(1, 1)
CONSTRAINT [PK_TestResults] PRIMARY KEY,
[TestTime] DATETIME2 NOT NULL
CONSTRAINT [DF_TestResults_TestTime] DEFAULT (SYSDATETIME()),
[Scenario] TINYINT NOT NULL,
[TestPattern] TINYINT NOT NULL,
[RowsInserted] INT NOT NULL,
[TotalRows] INT NOT NULL
);
END;
DECLARE @TestPattern TINYINT = 1;
DECLARE @Iteration TINYINT = 0,
@RowCount INT = 0,
@EndTime DATETIME2,
@DATA VARBINARY(MAX),
@Digest BINARY(32),
@ID VARCHAR(64),
@SQL NVARCHAR(MAX),
@Scenario TINYINT;
-- NOTE: Does not yet handle scenario 3!!
SELECT @Scenario = CASE col.[collation_name]
WHEN N'Latin1_General_CI_AS' THEN 1
WHEN N'Latin1_General_BIN2' THEN 2
ELSE 0
END
FROM sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'dbo.DATASTORE_META')
AND col.[name] = N'ID';
WHILE (@TestPattern < 7)
BEGIN
SET @Iteration = 0;
-- Execute the following 2 statements when changing either TestPattern or Scenario (for consistency):
TRUNCATE TABLE dbo.[DATASTORE_DATA];
TRUNCATE TABLE dbo.[DATASTORE_META];
WHILE (@Iteration < @Iterations)
BEGIN
SET @RowCount = 0;
SET @EndTime = DATEADD(MILLISECOND, @DurationMS, SYSDATETIME());
WHILE (SYSDATETIME() < @EndTime)
BEGIN
SET @DATA = CRYPT_GEN_RANDOM(@BlockSize);
SET @Digest = HASHBYTES('SHA2_256', @DATA);
SET @ID = CONVERT(VARCHAR(64), @Digest, 2);
BEGIN TRAN; -- I think the JDBC code is doing this
IF (@TestPattern = 1)
BEGIN
UPDATE dbo.[DATASTORE_META]
SET [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
WHERE [ID] = @ID;
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@ID, @DATA);
INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@ID, 0, CONVERT(INT, GETDATE()));
END;
END;
IF (@TestPattern = 2)
BEGIN
IF (EXISTS(
SELECT *
FROM dbo.[DATASTORE_META]
WHERE [ID] = @ID
))
BEGIN
UPDATE dbo.[DATASTORE_META]
SET [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
WHERE [ID] = @ID;
END;
ELSE
BEGIN
INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (@ID, @DATA);
INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (@ID, 0, CONVERT(INT, GETDATE()));
END;
END;
IF (@TestPattern = 3)
BEGIN
SET @SQL = N'
UPDATE dbo.[DATASTORE_META]
SET [LASTMOD] = CONVERT(INT, GETDATE()) -- not a long / ticks, but close enough for this test
WHERE [ID] = ''' + @ID + N''';
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO dbo.[DATASTORE_DATA] ([ID], [DATA]) VALUES (''' + @ID + N''', ' + CONVERT(NVARCHAR(MAX), @DATA, 1) + N');
INSERT INTO dbo.[DATASTORE_META] ([ID], [LVL], [LASTMOD]) VALUES (''' + @ID + N''', 0, CONVERT(INT, GETDATE()));
END;
';
EXEC(@SQL);
END;
IF (@TestPattern = 4)
BEGIN
SET @SQL = N'EXEC dbo.[StoreBlockInDatabase] 0x' + @ID + N', 0, ' + CONVERT(NVARCHAR(MAX), @DATA, 1) + N';';
EXEC(@SQL);
END;
IF (@TestPattern = 5)
BEGIN
EXEC sp_executesql
N'EXEC dbo.[StoreBlockInDatabase] @tmpDigest, 0, @tmpData;',
N'@tmpDigest VARBINARY(32), @tmpData VARBINARY(MAX)',
@tmpDigest = @Digest,
@tmpData = @DATA;
END;
IF (@TestPattern = 6)
BEGIN
EXEC sp_executesql
N'EXEC dbo.[StoreBlockInDatabase2] @tmpDigest, 0, @tmpData;',
N'@tmpDigest VARBINARY(32), @tmpData VARBINARY(MAX)',
@tmpDigest = @Digest,
@tmpData = @DATA;
END;
COMMIT TRAN; -- I think the JDBC code is doing this
SET @RowCount += 1;
END;
INSERT INTO dbo.[TestResults] ([Scenario], [TestPattern], [RowsInserted], [TotalRows])
OUTPUT INSERTED.*
SELECT @Scenario, @TestPattern, @RowCount, COUNT(*)
FROM dbo.[DATASTORE_DATA];
SET @Iteration += 1;
WAITFOR DELAY '00:00:00.500'; -- half-second delay
END;
SET @TestPattern += 1;
END;
GO
--------------------------------------------------------------------------------
--- END SETUP
--------------------------------------------------------------------------------
-- Exec test proc, apply Scenario 2 changes, then exec test proc again:
EXEC #RunTests;
----------------------------------------
-- Scenario = 2 (change collation to binary)
ALTER TABLE [DATASTORE_DATA] DROP CONSTRAINT [DATASTORE_DATA_PK];
ALTER TABLE [DATASTORE_DATA] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_DATA] ADD CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY CLUSTERED ([ID] ASC);
ALTER TABLE [DATASTORE_META] DROP CONSTRAINT [DATASTORE_META_PK];
ALTER TABLE [DATASTORE_META] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_META] ADD CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY CLUSTERED ([ID] ASC);
----------------------------------------
EXEC #RunTests;
/* -- Highlight and execute as necessary:
SELECT * FROM dbo.[DATASTORE_DATA];
SELECT * FROM dbo.[DATASTORE_META];
DELETE FROM dbo.[TestResults] WHERE [TestResultsID] = xyz;
SELECT * FROM dbo.[TestResults];
*/
-- TestPattern 3 = close approximation of current code
-- TestPattern 5 = proposed stored procedure approach
SELECT [Scenario], [TestPattern],
COUNT([RowsInserted]) AS [TestCount],
MIN([RowsInserted]) AS [MinRows],
AVG([RowsInserted]) AS [AvgRows],
MAX([RowsInserted]) AS [MaxRows],
SUM([RowsInserted]) AS [TotalRows]
FROM dbo.[TestResults]
GROUP BY [Scenario], [TestPattern]
ORDER BY [Scenario], [TestPattern];
/*
Scenario TestPattern TestCount MinRows AvgRows MaxRows TotalRows
1 1 10 11605 13329 14583 133297
1 2 10 11409 14522 16547 145221
1 3 10 1681 2108 2322 21080
2 1 10 12755 16064 17108 160640
2 2 10 13959 15312 16927 153125
2 3 10 2144 2422 2696 24228
2 4 10 2238 3012 3802 30121
2 5 10 963 4876 6626 48768
2 6 10 907 4696 6532 46969
*/
----------------------------------------
-- Scenario = 3 (keep binary collation, change PK to NONclustered)
ALTER TABLE [DATASTORE_DATA] DROP CONSTRAINT [DATASTORE_DATA_PK];
ALTER TABLE [DATASTORE_DATA] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_DATA] ADD CONSTRAINT [DATASTORE_DATA_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC);
ALTER TABLE [DATASTORE_META] DROP CONSTRAINT [DATASTORE_META_PK];
ALTER TABLE [DATASTORE_META] ALTER COLUMN [ID] VARCHAR(64) COLLATE Latin1_General_BIN2 NOT NULL;
ALTER TABLE [DATASTORE_META] ADD CONSTRAINT [DATASTORE_META_PK] PRIMARY KEY NONCLUSTERED ([ID] ASC);
----------------------------------------
GO
-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY OFF;
GO
Comments
0 B
|👍
/👎
0 B
|👍
/👎