/*******************************************************************
 * 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